[adrotate group=”3,7,8,9″]
Previously we created a simple file output page which we said could be used to update a DB2 file in much the same way as the PHPMyAdmin interface allows where a single record is displayed which allows the user to move around the records and update fields where necessary. That example did not allow the update or moving around of the records so we thought we would add some simple code to allow that to happen.
First of all we needed to change the function which displays the data to allow that data to be sent off to another script to be updated. We also needed to add a couple of buttons which allows the user to move back and forth around the records. The buttons to move around the file were simple as they just call the same page with updated relative record numbers, updating the file required us to wrap the content in a form and have a submit button to call the target script.
Here is the updated function, the index.php and initial page did not require updtaing.
/*
* function Dsp_Pfm()
* display the contents of a file
* @parms
* File
* File Library
* returns the number of errors.
*/
function Dsp_Pfm_2($conn,$file,$flib,$id) {
// query to get the data frm the file
$query = "SELECT rrn(a) as RRN, a.* FROM " .rtrim($flib) ."/" .rtrim($file) ." a WHERE RRN(a) > '" .$id ."' FETCH FIRST ROW ONLY";
$result = i5_query($query,$conn);
if(!$result){
echo("Failed to get the data<br>" .$query);
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
}
$rec = i5_fetch_assoc($result);
echo("<table><form name=updent method=post action=scripts/update_pfm.php?file=" .$file ."&flib=" .$flib ."&id=" .$rec['RRN'] ." >");
// the assoc array contains the field names so we can use those as the headers
$i = 0;
foreach($rec as $key => $value) {
echo("<tr><td>" .$key ."</td><td><input type=text name=" .$key ." id=" .$key ." value='" .$value ."');
if(&key == "RRN")
echo(" readonly=readonly");
echo(" /></td></tr>");
$i++;
}
echo("<tr><td colspan=2><input type=submit value=Update /></td</tr></form></table>");
// button to get the next record
echo("<table><tr><td>");
if($id > 0) {
$id--;
echo("<input type=button value=PREV OnClick=location='dsp_pfm.php?file=" .$file ."&flib=" .$flib ."&id=" .$id ."' /></td><td>");
$id++;
}
$id++;
echo("<input type=button value=NEXT OnClick=location='dsp_pfm.php?file=" .$file ."&flib=" .$flib ."&id=" .$id ."' /></td></tr></table>");
echo("<input type=button value='Log Out' OnClick=location='scripts/logout.php' ?>");
// break the reference to the last element as per the manual
unset($value);
i5_free_query($result);
return;
As the update is carried out using the RRN as the key to the file we call the target script with the “id” parameter set to the current RRN. Once the target script is called it will convert the form data to a SQL script that can be run against the database. Here is the update script. Just to complete things we added a button that would call another script to log out the user and clean up the sessions variables. You will notice that we omit the RRN field from the update as it does not exist in the actual DB, we have also marked the field in the display page to be read only as it would be pretty messy if the users were allowed to change it.
<?php
/*
Copyright © 2010, Shield Advanced Solutions Ltd
All rights reserved.
http://www.shieldadvanced.ca/
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
- Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
- Neither the name of the Shield Advanced Solutions, nor the names of its
contributors may be used to endorse or promote products
derived from this software without specific prior written
permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
*/
session_start();
include('functions.php');
// if failed to connect set the $_SESSION variables to empty
if(connect($conn) == -1) {
$_SESSION['Pwd_Err'] = 1;
$_SESSION['usr'] = "";
$_SESSION['pwd'] = "";
$_SESSION['valid_usr'] = NULL;
header('Location: /index.php');
exit(0);
}
// The post variables have the culumn headings and values
$query = "UPDATE " .$_REQUEST['flib'] ."." .$_REQUEST['file'] ." as a SET ";
foreach($_POST as $key => $value) {
// skip the RRN field
if($key != "RRN")
$query .= $key ." = '" .$value ."',";
}
// trim off the last ,'
$query = rtrim($query,",");
$query .= " WHERE RRN(a)='" .$_REQUEST['id'] ."'";
// break the reference to the last element as per the manual
unset($value);
// update the file
$result = i5_query($query,$conn);
if(!$result){
echo("Failed to get the data<br>" .$query);
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
$prev = $_REQUEST['id'];
$prev--;
header("Location: ../dsp_pfm.php?file=" .$_REQUEST['file'] ."&flib=" .$_REQUEST['flib'] ."&id=" .$prev);
}
header("Location: ../dsp_pfm.php?file=" .$_REQUEST['file'] ."&flib=" .$_REQUEST['flib'] ."&id=" .$_REQUEST['id']);
exit(0);
?>
}
The above code will format the SQL string and submit it for action using the i5_query() function. If the request is successful it will call the dsp_pfm page again with the current RRN as the id value, if it fails it will call the dsp_pfm page with a number less than the current RRN so the same RRN is displayed. you can change this to meet what ever requirements you have such as returning to the same record on the page with the update completed.
As you can see creating simple yet effective tools for the IBMi database are very easy, we have not added much in the way of error checking and corrective actions but you can see the possibilities are endless. We are using the Aura iAMP server and Zend server on a PC for testing the scripts and running a fully licensed Easycom server on the IBMi which allows us to access the i5_toolkit() functions from either system without any changes to the PHP code. The iAMP/Linux/Windows HTTP server requires no Easycom license to call the i5_toolkit() functions as all of the licensing is managed from the IBMi Easycom Server installation. If you are running the Zend Core/Server Easycom toolkit you will not be able to run the i5_toolkit() from a remote HTTP server.
If you are interested in doing more with PHP and your IBMi talk with us! We can guide you through the setting up of a very effective PHP based environment for both testing and production purposes. Licensing of Easycom allows you greater freedom and flexibility plus it brings many more features to your PHP solution at a cost which is a fraction of the other solutions out there. If you need to speak with some of the clients we have already helped move to a fully functioning Easycom environment let us know, I am sure they will be more than happy to discuss their experiences when dealing with us.
Happy PHP’ing.
Chris…