Categories
PHP

Firebird: Connect, Insert and Fetch Data

Learn how to access data in Firebird from PHP.

<?php
 $conn = ibase_connect('localhost:/path/db.gdb', 'user','pass');
 if (! $conn) {
  exit ( 'Connection Failed' );
 }
 
 echo 'Connected to the database.';
 ibase_close($conn);

This section also uses .gdb files that are compatible with both Firebird and Interbase; the new Firebird format has the extension .fdb. After this file is created, ibase_connect() connects to the file or database. For the host, you have to provide a string in the format 'localhost:/path/to/file.gdb' when using TCP/IP, or the local filename; you also need a username and a password.

Firebird Query and Prepared Statement

The function ibase_query() can be used to send an SQL string to the database. To be safe from SQL injection, a prepared statement must be used. Here, the function ibase_prepare() comes into play: It parses an SQL statement (with question marks as placeholders) and returns a statement object. Then, ibase_execute() executes this statement and retrieves the values for the placeholders as additional parameters.

Using Auto-increment Function in Firebird

<?php
 $conn = ibase_connect('localhost:/path/db.gdb', 'user','pass');
 $sql = 'INSERT INTO user_table (id, name, email)
         VALUES (GEN_ID(user_table, 1), ?, ?)';
 $stmt = ibase_prepare($conn, $sql);
 ibase_execute($stmt, $_POST['name'], $_POST['email']);
 echo 'Record saved.';

The preceding code contains a specialty of Firebird. The identity column is driven by a generator in the database; the call to GEN_ID(user_table, 1) enters the next available value in this column when inserting a new field.

Retrieving Results of a Query to Firebird

No matter if you are using ibase_query or ibase_execute(), at the end, you have a handle for the resultset, which you can iterate with ibase_fetch_assoc() which returns an associative array or ibase_fetch_object() which returns an object. This code uses the latter method.

Note: Firebird returns column names in uppercase, so the object properties (and the keys in the associative arrays) are uppercase, too.

<?php
 $conn = ibase_connect('localhost:/path/db.gdb', 'user','pass');
 $result = ibase_query($conn, 'SELECT * FROM user_table LIMIT 5');
 while ($row = ibase_fetch_object($result)) {
  echo $row->ID .', '.
       $row->NAME .', '.
       $row->EMAIL .', ';
 }
 ibase_close($db);

Working with Databases: