How to Read and Write to InterBase Tables Using PHP

by Marcio Machado de Moura

How to Connect

$conn=ibase_connect("localhost:/home/ibdemo.gdb", "SYSDBA", "masterkey");
if (!$conn)
{ echo "Access Denied!";
exit; }

The ib_connect command needs three basic parameters:

  • Host:Database (with the complete path and file name of the database)
  • Username
  • Password

To connect locally to your own server that is running PHP, use localhost as the host name.

In our example, we are using a database located in the directory /home, named ibdemo.gdb, with the default InterBase username and password . (We use the name $conn to receive the result of the execution of the command, but any other name could be used for this variable).

Executing Queries

$query="insert into friends values ('$name','$nick','$email','$icq',0);";
$result=ibase_query($conn,$query);
if (!$result) {
echo "Error. Can't insert the record with the query: $query!";
exit;
}

The ibase_query command uses two parameters to execute a query

  • The name of the variable used to connect ($conn)
  • The Query (represented here by the $query variable)

You can execute the query directly, without using a variable:

$result=ibase_query($conn,"select * from table");

Obtaining rows and columns from a table

$row=ibase_fetch_object($result)

To obtain the content of the columns, you can use the $row variable like a vector:

  • $row[0] (The first column)
  • $row[1] (The second column)
  • ...
  • $row[N] (The last column)

Or index by the database field names:

  • $row->NAME (The field NAME)
  • $row->NICK (The field NICK)

The Loop:

while ($row = ibase_fetch_object ($result)) {
      echo "$row[0]";
      echo "$row[1]";
         ...
      echo "$row[N]";
}

Or:

while ($row = ibase_fetch_object ($result)) {
      echo "$row->FIELD_NAME_1";
      echo "$row->FIELD_NAME_2";
         ...
      echo "$row->FIELD_NAME_N";
}

Returns all the rows of the query result.

Closing a connection

ibase_close($conn);

The ibase_close(parameter) closes the connection with the InterBase Server. The only necessary parameter is the same variable as was used in the connection.

The friends table SQL Script:

CREATE TABLE friends(
name varchar(80),
nick varchar(80),
email varchar(200),
icq varchar(80),
code integer not null primary key );

CREATE GENERATOR friends_inc;
COMMIT;

SET TERM ! ;
CREATE TRIGGER create_id FOR friends
BEFORE INSERT POSITION 0 AS
BEGIN
new.code=GEN_ID(friends_inc, 1);
END !
SET TERM ; !

You will need a ISQL client to execute the SQL script.

The ibdemo.html source:

<html>
<head>
<title>A PHP/InterBase little test</title>
</head>
<BODY bgcolor="#ffffff" text="black" link="#0000a0" alink="#0000a0" vlink="#0000a0"> <font face="arial,verdana,helvetica" size=3>
<b>A PHP/InterBase little test</b>
<p>
<table border=0 cellpadding=5 cellspacing=0 bgcolor="#6699cc">
<tr>
<td
> <font face="arial,verdana,helvetica" size=2>
<b>Friends Catalog</b>
<FORM ACTION="ibsend.php" METHOD="POST">
Name:<BR>
<INPUT NAME="name" TYPE=text SIZE=50 MAXSIZE=80 VALUE="">
<P>
Nick:<BR>
<INPUT NAME="nick" TYPE=text SIZE=50 MAXSIZE=80 VALUE="">
<P>
E-mail:<BR>
<INPUT NAME="email" TYPE=text SIZE=50 MAXSIZE=80 VALUE="">
<P>
ICQ Number:<BR> <INPUT NAME="icq" TYPE=text SIZE=20 MAXSIZE=80 VALUE="">
<P>
<INPUT TYPE=submit VALUE="Send"> <INPUT VALUE="Clear" TYPE=reset>
</FORM>
</td>
</tr>
</table>
<p>
<a href="ibshow.php"><b>Show Friends</b></a>
</body> </html>

You can copy and then paste this source to a HTML editor.

The ibsend.php source:

<html>
<head>
<title>A PHP/InterBase little test</title>
</head>
<BODY bgcolor="#ffffff" text="black" link="#0000a0" alink="#0000a0" vlink="#0000a0"> <font face="arial,verdana,helvetica" size=3>
<b>A PHP/InterBase little test</b>
<p>
<? $back="<br><br><a href='javascript:window.history.back()'><b>Back</b></a>";
if (empty($name)) {
echo "Name is empty! $back";
exit;
}
$conn=ibase_connect("localhost:/home/ibdemo.gdb", "SYSDBA", "masterkey");
if (!$conn) {
echo "Accesso Denied! $back";
exit;
}
$result=ibase_query($conn,"select name from friends where name='$name'");
$num_rows=ibase_fetch_row($result);
if (!empty($num_rows[0])) {
echo "Name already exist: $num_rows[0] $back";
exit;
}
$query="insert into friends values ('$name','$nick','$email','$icq',0);"; $result=ibase_query($conn,$query);
if (!$result) {
echo "Error. Can't insert the record with the query: $query! $back";
exit;
}
echo "Record saved: $name $back";
ibase_close($conn)
; ?>
</body>
</html>

You can copy and paste this source to a HTML editor.

The ibshow.php source:

<html>
<head>
<title>A PHP/InterBase little test</title>
</head>
<BODY bgcolor="#ffffff" text="black" link="#0000a0" alink="#0000a0" vlink="#0000a0"> <font face="arial,verdana,helvetica" size=3>
<b>A PHP/InterBase little test</b>
<p>
<? $lt="<font face='arial,verdana,helvetica' size=2><b>";
$conn=ibase_connect ("localhost:/home/ibdemo.gdb", "SYSDBA", "masterkey");
if (!$conn) {
echo "Acess Denied!<br>";
exit;
}
$result=ibase_query($conn, "select count(code) from friends");
if (!$result)
{echo "<br>Error executing query select count!";
exit;
}
$num_rows=ibase_fetch_row($result);
echo "<b>Found $num_rows[0] Records</b>";

echo "<table border=1 cellpadding=5 cellspacing=0 bgcolor=#99ccff bordercolor=#000070> <tr bgcolor=#ffffc0><td colspan=5>$lt Order By Name</td></tr>
<tr bgcolor=#bfccd2>
<td>$lt Name</td>
<td>$lt Nick</td>
<td>$lt E-mail</td>
<td>$lt ICQ Number</td>
<td>$lt Code</td>
";
$result=ibase_query($conn, "select * from friends order by name");
if (!$result) {
echo "<tr><td colspan=5>Erro showing table order by name!</td></tr>
</table>";
} else {
while ($row=ibase_fetch_object ($result)) {
echo "<tr>
<td>$lt $row->NAME</td>
<td>$lt $row->NICK</td>
<td>$lt $row->EMAIL</td>
<td>$lt $row->ICQ</td>
<td>$lt $row->CODE</td>
</tr>";
} echo "</table>";
}

echo "<p>
<table border=1 cellpadding=5 cellspacing=0 bgcolor=#99ccff bordercolor=#000070> <tr bgcolor=#ffffc0><td colspan=5>$lt Order By Code</td></tr>
<tr bgcolor=#bfccd2>
<td>$lt Name</td>
<td>$lt Nick</td>
<td>$lt E-mail</td>
<td>$lt ICQ Number</td>
<td>$lt Code</td> ";
$result=ibase_query($conn, "select * from friends order by code");
if (!$result) {
echo "<tr><td colspan=5>Erro showing table order by code!</td></tr>
</table>";
} else {
while ($row=ibase_fetch_row ($result)) {
echo "<tr>
<td>$lt $row[0]</td>
<td>$lt $row[1]</td>
<td>$lt $row[2]</td>
<td>$lt $row[3]</td>
<td>$lt $row[4]</td>
</tr>";
}
echo "</table>";
}
ibase_close($conn);
?>
<br><br>
<a href='javascript:window.history.back()'><b>Back</b></a>
</body>
</html>

You can copy and paste this source to a HTML editor.