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 "
}
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 "
}
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 listTable ( $row )
{
// list fields
echo "
foreach ($row as $key => $val) <
array key into $key and load the
array value into $val
{
echo "
}
echo ""; <
}
// connect to LEGATO2 database
$userName = 'someuser';
$passWord = 'somepasswd';
$databaseName = 'RDBNAME';
$dbh = db2_connect($databaseName,$userName, $passWord);
if ($dbh == 0)
{
// display connection error message on failure
echo "SQL connection failed.
";
echo db2_conn_errormsg() . "
";
die();
}
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".
$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 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['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.
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)
the array key into $key and
the array value into $val
{
echo "
}
echo "";
$firstTime = FALSE;
}
// call function to list columns using associative array
listTable($menuRow);
}
// end the table
echo "";
}
?>
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
Jeff Olen is a super-spy now but keeps his cover identity intact by working for video game studios on the East Coast. So when he’s not out killing members of ISIS or rescuing refugees, you can find him playing Wolfenstein II or testing the new Fallout 76 releases at his beach house in Costa Rica. In any case, he can’t be reached. You can email his cat at
MC Press books written by Jeff Olen available now on the MC Press Bookstore.
 |
||
 |
|
The IBM i Programmer’s Guide to PHP Get the scoop on how PHP can—and should—be deployed on IBM systems. List Price $79.95 Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online