[adrotate group=”3,4″]
The last post PHP for ‘i’ #4 showed how you could use the i5_ functions to display the details of a record in an additional page. We have also posted about how to use the db2_ functions to get a faster response as we found the i5_ functions were very slow (the i5_pconnect() functioned cleared this up but we are still looking at how to correctly code the use of the function). The documentation for any of the IBM ‘i’ related PHP functionality is ver poor at best, so we could not find a method to extract the RRN from a file read which would allow us to read from the file again using the rrn associated with each record.
We asked about the db2_ functions which are controlled by IBM so we expect changes will be hard to get implemented. Our thoughts were to have the RRN returned within the array for every access. Then we posted to the Zend Forums to find out if anyone else had coded around the issue and if so how did they do it. We had one response which talked about using the rrn() function within SQL? We searched for documentation for the function but only found an article in the ITJungle. It was looking at incrementing a number which didn’t seem to fit the bill, but testing proved that the function exists and it does return the RRN as expected.
We have changed the file names to allow us to run two page threads, one using the i5_ functionality from the i5ToolKit the other using the IBM db2_ functions. Here is the initial page (db2test.php) which will display a list of users in a table, we have published part of this code before showing how to publish the list but added the code to allow a link to the details page for each line.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type></HEAD>
<BODY>
<?php
//include("i5toolkit/Toolkit_classes.php");
// include the file which holds the user info
include("../scripts/config.php");
// connect to the i5
$options = array("i5_lib"=>"cprojdta","cursor"=>DB2_SCROLLABLE);
$conn = db2_connect("","","",$options);
if (is_bool ( $conn ) && $conn == FALSE) {
die ( i5_errormsg () );
}
$result = db2_exec($conn,"select rrn(a), a.* from users a"); ?>
<table border="1">
<tr><td width="100"> <td width="150">First Name</td><td width="150">Last Name</td></tr><?php
while ($row = db2_fetch_both($result)) {
echo("<tr><td><a href=dspdetsdb2.php?id=" .$row[0] .">Details</a></td><td>".$row['FIRSTNAME'] ."</td><td>" .$row['LASTNAME'] ."</td></tr><br />");
}
db2_free_result($result);
db2_close($conn);
?>
</table>
</BODY>
</HTML>
The code is quite simple to understand, the only change to the previous code is the addition of the link to the dspdetsdb2.php page which is added to every line.
The dspdetsdb2.php page is called with the rrn for each entry, this is used to get the record back from the database. We have read that the DB2 engine has two methods of reading the files, one will read through all of the entries even after finding the correct entry, the other will stop once it has found the correct entry. It says the engine called cannot be determined ahead of time so we are unsure just how much impact this will have on a large file using this technique? Using a unique index may be a better solution, but that is for a later phase.. Here is the code we created.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
</HEAD>
<BODY>
<?php
//include("i5toolkit/Toolkit_classes.php");
// include the file which holds the user info
include("../scripts/config.php");
// connect to the i5
$options = array("i5_lib"=>"cprojdta","cursor"=>DB2_SCROLLABLE);
$conn = db2_connect("","","",$options);
if (is_bool ( $conn ) && $conn == FALSE) {
die ( i5_errormsg () );
}
$query = "select * from users where rrn(users)=" .$_REQUEST['id'];
$result = db2_exec($conn,$query);
if(!$result) {
db2_stmt_errormsg($result);
exit();
}
$rec = db2_fetch_both($result);
?>
<table>
<tr>
<td><a href="db2test.php">Return to list</a></td>
</tr>
<tr>
<td><label>First Name</label></td>
<td><?php echo($rec["FIRSTNAME"]); ?></td>
</tr>
<tr>
<td><label>Last Name</label></td>
<td><?php echo($rec["LASTNAME"]); ?></td>
</tr>
<tr>
<td><label>Address</label></td>
<td><?php echo($rec["ADDR1"]); ?></td>
</tr>
<tr>
<td><label>Address</label></td>
<td><?php echo($rec["ADDR2"]); ?></td>
</tr>
<tr>
<td><label>City</label></td>
<td><?php echo($rec["CITY"]); ?></td>
</tr>
<tr>
<td><label>State</label></td>
<td><?php echo($rec["STATE"]); ?></td>
</tr>
<tr>
<td><label>Zip Code</label></td>
<td><?php echo($rec["ZIP"]); ?></td>
</tr>
<tr>
<td><label>Country</label></td>
<td><?php echo($rec["COUNTRY"]); ?></td>
</tr>
<tr>
<td><label>Telephone</label></td>
<td><?php echo($rec["TELNUM"]); ?></td>
</tr>
<tr>
<td><label>Email</label></td>
<td><?php echo($rec["EMAIL"]); ?></td>
</tr>
</table>
<?php
// close the file and free resource
db2_free_result($result);
db2_close($conn);
?>
</BODY>
</HTML>
The pages load very quickly but we are only looking at a few records, once the record count grows I am not sure how this technique will scale. The benefit we see is we can implement a page size limit which will help with scaling, the use of the rrn() function will allow us the control this very easily or by using the cursor within the file.
Thats it for this post, let us know if you have any questions.
Chris…