![]() |
IBPhoenix Research |
![]() ![]() |
Creating an InterBase Interactive SQL Utility With PHP |
By Marcio Machado de MouraOur objective is to list all the InterBase databases in a specific directory, and then connect and run queries against the connected databases. Listing The DatabasesThe 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 DatabaseThe 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 QueryWe need 3 steps (for queries that do not show results) or 4 steps (for queries with select statements that return results). First step: $ibfullname="localhost:" .$ibdir . $ibname; $conn=ibase_connect($ibfullname,$login,$password); if (!$conn) { echo "Can't connect to $ibname with $login user."; exit; } Second step: 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: $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: $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> The IBISQL Click HERE to see this example running. All databases found are available for testing. You can execute CREATE TABLE, INSERT VALUES, RUN SELECTS, UPDATES, etc. |