Section Navigation

Creating an InterBase Interactive SQL Utility With PHP

by Marcio Machado de Moura

Our objective is to list all the InterBase databases in a specific directory, and then connect and run queries against the connected databases.

Listing The Databases

The first step is to define the directory used to store the database. In our example I will define this value in a single variable called $ibdir, but it's possible create an included file to store all default parameters (directory, user name, password, etc.).

$ibdir="/home/interbase/";

This line will be present in all the example scripts

The method used to obtain the names of the databases contained in the directory, is to execute the unix command ls -1 (this shows a list of files with line by line). To obtain only the names of databases, we will use the command ls with the parameter *.gdb.

The database list will be generated inside of a combo box:

<SELECT NAME="ibname">
<OPTION selected VALUE="NOTHING">>> SELECT <<</OPTION>
<?
$ibnames="ls -1 " . $ibdir . "*.gdb";
exec($ibnames,$s);
for ($i=0;$i < count($s);$i++) {
$s[$i]=ereg_replace($ibdir,"",$s[$i]);
echo "$s[$i]";
}
?>
</select>

The ereg_replace hides the name of the directory in the list.

Below, the complete script iblist.php:

<html>
<head>
<title>InterBase ISQL</title>
</head>
<?
$ibdir="/home/interbase/";
?>
<BODY bgcolor="#ffffff" text="black"><font face="arial,verdana,helvetica" size=3>
<b>InterBase Database List</b>
<p>
<FORM ACTION="ibconnect.php" METHOD="POST">
<table border=0 cellpadding=5 cellspacing=0 bgcolor="#6699cc">
<tr>
<td><font face="arial,verdana,helvetica" size=2>Login:</td>
<td><INPUT NAME="login" TYPE=text SIZE=20 MAXSIZE=30 VALUE="SYSDBA"></td>
</tr><tr>
<td><font face="arial,verdana,helvetica" size=2>Password:</td>
<td><INPUT NAME="password" TYPE=password SIZE=20 MAXSIZE=30 VALUE="masterkey"></td>
</tr><tr>
<td><font face="arial,verdana,helvetica" size=2>Database:</td>
<td>
<SELECT NAME="ibname">
<OPTION selected VALUE="NOTHING">>> SELECT <<</OPTION>
<?
$ibnames="ls -1 " . $ibdir . "*.gdb";
exec($ibnames,$s);
for  ($i=0;$i < count($s);$i++) {
   $s[$i]=ereg_replace($ibdir,"",$s[$i]);
   echo "<OPTION VALUE='$s[$i]'>$s[$i]</OPTION>";
}
?>
</select>
</td>
</tr><tr>
<td colspan=2><INPUT TYPE=submit VALUE="Send"> <INPUT VALUE="Clear" TYPE=reset></td>
</tr>
</table>
</form>
</body>
</html>

You can copy and paste this source into an HTML editor.

Connecting To The Selected Database

The next step, after you have selected an InterBase database, is to make a connection to the database. The following script tests the connection and opens a simple form for you write your SQL query:

<html>
<head>
<title>InterBase ISQL</title>
</head>
<?
$ibdir="/home/interbase/";
?>
<BODY bgcolor="#ffffff" text="black"><font face="arial,verdana,helvetica" size=3>
<b>InterBase Connect
<p>
<?
$ibfullname="localhost:" . $ibdir . $ibname;
$conn=ibase_connect($ibfullname,$login,$password);
if (!$conn) {
   echo "Can't connect to $ibname with $login user.";
} else {
   echo "$ibname connected by $login user.";
   ibase_close($conn);
}
?>
</b>
<FORM ACTION="ibquery.php" METHOD="POST">
<table border=0 cellpadding=15 cellspacing=0 bgcolor="#6699cc">
<tr>
<td><font face='arial, helvetica' size=2>Enter your query:<br>
<TEXTAREA NAME="query" ROWS="8" COLS="50"></textarea></td>
</tr><tr>
<td>
<INPUT TYPE=submit VALUE="Send"> <INPUT VALUE="Clear" TYPE=reset>
<INPUT TYPE='HIDDEN' NAME='login'    <? echo "VALUE='$login'>"; ?>
<INPUT TYPE='HIDDEN' NAME='password' <? echo "VALUE='$password'>"; ?>
<INPUT TYPE='HIDDEN' NAME='ibname'   <? echo "VALUE='$ibname'>"; ?>
</td>
</tr>
</table>
</form>
</body>
</html>

This form tests the connection, and transfer to the ibquery.php script the query, user login, password and database name.

Executing The Query

We need 3 steps (for queries that do not show results) or 4 steps (for queries with select statements that return results).

First step: Connect to the database (with the hidden parameters transferred by ibconnect.php):

$ibfullname="localhost:" .$ibdir . $ibname;
$conn=ibase_connect($ibfullname,$login,$password);
if (!$conn) {
echo "Can't connect to $ibname with $login user.";
exit;
}

Second step: Filter the query to extract any slashes after (") or ('), and translate all characters (") in (').

PHP4 (and some versions of PHP3) transfer strings with splash characters. So, a query that contains, for example, the expression where name="test", will arrive as where name=/"test/". It is also necessary to change all characters (") for (') to get a correct execution of the query:

$query=stripslashes($query);
$query=ereg_replace(chr(34),chr(39),$query);

Third step: Executing and testing the query:

$result=ibase_query($conn,$query);
if (!$result) {
echo "<b><font color=#aa0000>Can't run the query:</b>
<br><br>$query</font><br><br>
<b>On the database:</b> $ibname
<b>with</b> $login <b>user.</b><br><br>";
} else {
echo "<b>Your query has been executed successfully:</b>
<br><br> $query <br><br>
<b>On the database:</b> $ibname.<br><br>";
}
$num_cols=ibase_num_fields($result);

Fourth step: If the result of the expression ibase_num_fields is "not zero", it will show the query result. But the command ibase_field_info(), available in PHP3, has not been implemented in the PHP4. So, we need to find out if the current version is PHP4, and justify the absence of the column names.

$v=phpversion();
if ($v >="4.0.0") {
echo "<font size=1>(Sorry, but <b>ibase_field_info</b>
is currently not functional in PHP4).</font>";
echo "<table cellspacing='1' cellpadding='3' border='0'>";
} else {
echo "<table cellspacing='1' cellpadding='3' border='0'><tr bgcolor=#bed2e6>";
for ($i=0; $i < $num_cols; $i++){$col_info=ibase_field_info($result,$i);
echo
"<td>$col_info->name </td>";
}
echo "</tr>";
}

The display of the query result is a default application, using two loops with the expression ibase_fetch_row() inside. See the complete script ibquery.php below:

<html>
<head>
<title>InterBase ISQL</title>
</head>
<?
$ibdir="/home/interbase/";
?>
<BODY bgcolor="#ffffff" text="black"><font face="arial,verdana,helvetica" size=3>
<b>InterBase ISQL</b>
<p>
<?
$ibfullname="localhost:" . $ibdir . $ibname;
$conn=ibase_connect($ibfullname,$login,$password);
if (!$conn) {
   echo "Can't connect to $ibname with $login user.";
   exit;
}
$query=stripslashes($query);
$query=ereg_replace(chr(34),chr(39),$query);
$result = ibase_query($conn,$query);
if (!$result) {
echo "<b><font color=#aa0000>Can't run the query:</b>
<br><br>$query</font><br><br> <b>On the database:</b>
$ibname <b>with</b> $login <b>user.</b><br><br>";
} else {
echo "<b>Your query has been executed successfully:</b>
<br><br> $query <br><br><b>On the database:</b> $ibname.<br><br>";
}
$num_cols=ibase_num_fields($result);

if ($num_cols<>0) {
$v=phpversion();
if ($v >= "4.0.0") {
echo "<font size=1>(Sorry, but <b>ibase_field_info</b>
is currently not functional in PHP 4).</font>";
echo "<table cellspacing='1' cellpadding='3' border='0'>";
      } else {
echo "<table cellspacing='1' cellpadding='3' border='0'><tr bgcolor=#bed2e6>";
for ($i = 0; $i < $num_cols; $i++){
$col_info = ibase_field_info($result, $i);
echo "<td>$col_info->name  </td>";
}
echo "</tr>";
}
$color=0;
while ($row = ibase_fetch_row($result)) {
if ($color % 2 == 0) {echo "<tr bgcolor=#99cccc>";}
else                 {echo "<tr bgcolor=#99ccff>";}
$color++;
for ($i=0; $i<$num_cols; $i++) {
echo "<td>$row[$i]</td>";
}
echo "</tr>";
}
echo "</table>";
}
ibase_close($conn);
?>
<br><br>
<a href='javascript:window.history.back()'><b>Back</b></a>
</body>
</html>

Sign In

Section Navigation

Creating an InterBase Interactive SQL Utility With PHP

Listing The Databases

Connecting To The Selected Database

Executing The Query