TechTip: Start Using PHP on the iSeries...NOW! Part II

Web Languages
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

In Part I of this series, I covered how to install and configure PHP on your iSeries. Also, I touched a bit on how to access the existing DB2 databases. Now, I'll get to the so-called "meat and potatoes" of developing your PHP application. The goal is to give you a few practical (and hopefully working!) examples of PHP scripts and functions. A rather lofty goal perhaps, but I'll give it a shot.

Somewhere, someone's going to be unhappy because I'm not starting with the ubiquitous "Hello World" script. Frankly, I don't care. Hopefully, you don't either. The script shown below provides all the pieces you need to examine...and all in one compact package.



// function to output database rows to a table
function listTable ( $row ) 
{

// list fields
echo " ";

foreach ($row as $key => $val)   
{
echo " ".$val."";
}

echo "";
}

// main PHP script

// connect to DB2 database
$userName      = 'someuser';
$passWord      = 'somepasswd';
$systemName = 'SYSTEMNAME';
$dbh = db2_connect($systemName,$userName, $passWord);
if ($dbh == 0)
{
// display connection error message on failure
echo "SQL connection failed";
echo db2_conn_errormsg() . "
";

die();
}

// create SQL to retrieve data

$s = "select Working_Telephone_Number, Last_Name".
    " from jeffo.leadmstr";
    
// execute the SQL 
$q = db2_exec($dbh, $s);

// handle SQL fail condition
if ( $q == 0)
{

// display error message on failure
echo "SQL statement failed
";

echo db2_stmt_errormsg()."
";


}
// if SQL was successful
else 
{

// start the table
echo ' ';
$firstTime = TRUE;

// loop thru all rows in the result set
while ($menuRow = db2_fetch_assoc($q))
{
// if first time thru then output headings
if ($firstTime)
{
echo " ";
foreach ($menuRow as $key => $val)
{
echo "".$key."";
}
echo "";
$firstTime = FALSE;
}

// call function to list columns using associative array
listTable($menuRow);

}

// end the table
echo "";
}
?>

At first glance, this may look rather daunting, but it's really fairly simple. I'll break down each piece and explain what it's doing and why. I'm not going to spend any time on PHP syntax. As experienced developers, you can decipher syntax without my help.

The first 12 lines comprise a function that outputs each row of data into an HTML table. If you're unfamiliar with associative arrays, you may want to read the php.net documentation on them. It helped me to start thinking of the array index as an array key instead. PHP requires that your functions are placed at the top of the script; that's why the function precedes the main script code. Not much else here worth mentioning except that the FOREACH function is really handy, and you should thoroughly familiarize yourself with it.

// function to output database rows to a table
function listTable ( $row )     <----------------- Function definition with parameter(s) 
{

// list fields
echo " ";   <-------------------------- HTML to start a table row

foreach ($row as $key => $val)  <--- for each array element load the
      
array key into $key and load the
      
array value into $val
             
{
echo " ".$val.""; <-- HTML to output table column/field values
}
echo "";   <-------------------------- HTML to end a table row

}
The next portion of the code creates the connection to the iSeries database. If you have used ODBC in the past, the syntax here should be somewhat familiar. The $databaseName should be the same as the Database Directory Entry you created in Part I of this article. The values for $userName and $passWord are not case-sensitive. The db2_Connect() function returns a resource object if it is successful and a zero if it fails. In the event of a failure, the db2_conn_errormsg() function returns the failure reason information, and then the die() function terminates the script immediately. Once the connection is successfully established, the $dbh object is used with the other DB2 functions to access the databases.
// main PHP script

// connect to LEGATO2 database
$userName      = 'someuser';
$passWord      = 'somepasswd';
$databaseName = 'RDBNAME';
$dbh = db2_connect($databaseName,$userName, $passWord);  
if ($dbh == 0) ----------------------- Check for failed connection attempt
{
// display connection error message on failure
echo "SQL connection failed.
";  
---------- Display message telling what happened
echo db2_conn_errormsg() . "
";

die();  ---------------------- End the script immediately
}
Now you're ready to retrieve data from your database. The first thing to do is construct the SQL statement. The syntax is very similar to SQL on the iSeries. The only exception seems to be that you cannot use the slash (/) to separate the library from the file name. You must use a period (.) instead.

The IF statement used here is formatted to be clear for us RPG developers. It could have been written "if (!$q)" and would work just fine. The exclamation point (or "bang") is not familiar to RPG developers. It can be thought of as a "not." For instance, "!($someNumber == $otherNumber)" is exactly the same as "$someNumber != $otherNumber".

// create SQL to retrieve data

$s = "select Working_Telephone_Number, Last_Name".
    " from jeffo.leadmstr";  ---------------------- Create the SQL statement to execute
    
// execute the SQL 
$q = db2_exec($dbh, $s);  ---------------------- Execute the SQL statement

// handle SQL fail condition
if ( $q == 0)  -----------------------  Check for a failure 
{
// display error message on failure
echo "SQL statement failed
";

echo db2_stmt_errormsg()."
"; 
--- Display SQL error message
}

If the SQL statement executed correctly, you should now have a valid result set object (referenced in the code as $q).

Now, you need a little better understanding of the way the DB2_fetch_assoc function works. In a nutshell, this function retrieves a row/record from the result set (the next row by default), uses the name of each column/field as the array key, and loads the array with the values of each column/field. In our example, this might look like the following:

$menuRow['WORKING_TELEPHONE_NUMBER']  = 7142223333
$menuRow['LAST_NAME'] = 'Olen'

Notice that the index keys are in all caps? This is not an accident. The SQL engine always sends the column/field names in all caps. Knowing this will save you a lot of time trying to debug a script that has no bugs except that you are using mixed case when accessing your associative array(s).

Knowing the structure of the associative array should also shed some light on the code below, which outputs the headings for our table.
// if SQL was successful
else 
{

// start the table
echo ' ';  
$firstTime = TRUE;

// loop thru all rows in the result set
while ($menuRow = db2_fetch_assoc($q))  --- retrieve the next row and load the array
{
// if first time thru then output headings
if ($firstTime)  ----------- only output headings the first time thru the loop
{
echo " ";
foreach ($menuRow as $key => $val) ---- For each array element load 
      the array key into $key and 
      the array value into $val
{
echo "".$key.""; --- output the column heading
}
echo "";
$firstTime = FALSE; --- reset the flag so that this will not execute again
}

// call function to list columns using associative array
listTable($menuRow); -- pass the array to the function to output the field values

}

// end the table
echo "";
}
?>  ---- end the script


As the saying goes, "That's all folks!" You can easily modify this script to output a table of records from any database. All you need to do is change the SQL statement to retrieve different file data. Or better yet, change the whole thing into a function and simply pass the library and file name as parameters.

On a side note, I am partial to using the DB2_fetch_assoc function, but you may want to explore a couple of other options: the DB2_fetch_array, DB2_fetch_both, and DB2_fetch_row.

Next time, I'll dig into the Zend Code APIs that allow you to call CL and RPG programs from your PHP code. Until then, experiment with accessing your iSeries data from PHP.

Jeff Olen is the owner of Olen Business Consulting, Inc., an iSeries development services company based in Orange, California. When he's not busy writing code or writing articles, you can find him jumping out of perfectly good airplanes (a.k.a. skydiving). To find out more about Jeff or Olen Business Consulting, Inc., go to www.olen-inc.com or email Jeff at This email address is being protected from spambots. You need JavaScript enabled to view it..



BLOG COMMENTS POWERED BY DISQUS