[adrotate group=”3,4″]
The last post detailed how we took a db2 file generated by our JobQGenie product and paged through it using a PHP script. We were thinking we had cracked the problem until one of our testers showed us just how wrong we were!
The problem was caused by the tester using the forward and backward button in the browser and not the links we had generated for the pages. If the back button was used it would throw the page links we had created out of whack! So we need to come up with another solution to allow us to track the pages better.
We provided the ability to check if the page was refreshed in the last post by simply checking if the Request URI was the same as the last one we stored, that was pretty easy! Now we needed to have a tracking technology that would know how to display the page even if it had been selected from the back button on the browser. We decided that we had to show the page being requested plus store the page we were displaying. The following code shows how we managed to achieve this.
<?php
/*
* There are 3 ways to call this file, with no parameters this will
* be the initial call, with nextid which is when the NEXT link is
* selected and finally with the recid which is called when one of the
* page numbers is selected.
*/
// start the session to allow session variables to be stored and addressed
session_start();
/*
* function to strip the array contents back to a selected page.
* takes in the address of the stored array so it can be unset and
* the id of the record to be searched for. The id is the first job
* ID which was stored for a particular page.
* returns when the id is matched to an array element
*/
function remove_to(&$pages,$id) {
$num = (count($pages)- 1);
$num_pages = $_SESSION['num_pages'];
for($i = $num; $i > 0; $i--) {
//echo("count = " .$i ."id = " .$id ."array id = " .$pages[$i][2] ."<br>");
if($pages[$i][2] == $id) {
return;
}
else {
unset($pages[$i]);
$num_pages--;
}
}
// resequence the arrray
$pages = array_values($pages);
// set the num pages again
$_SESSION['num_pages'] = $num_pages;
return;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<!-- The table rows will alternate in color -->
<style type="text/css">
tr.d0 td {
background-color: #CC9999;
}
tr.d1 td {
background-color: #9999CC;
}
</style>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type></HEAD>
<BODY>
<?php
/* include the file which holds the user info for the connection plus any configurable
* information such as the number of rows to show per page.
*/
include("../scripts/config.php");
/* check if this is a refresh of the same URL, we need to do this to determine
* if a new page element is to be added to the stored array.
*/
$p = 0;
$refresh = false;
if($_SERVER[REQUEST_URI] == $_SESSION['uri'])
$refresh = true;
else
$_SESSION['uri'] = $_SERVER[REQUEST_URI];
// if the session pages array is set copy to the local pages array
if(isset($_SESSION['pages']))
$pages = $_SESSION['pages'];
// if $_REQUEST['page'] is not set this is the intial request
if(!isset($_REQUEST['page'])) {
$next = 0;
$add_page = true;
}
else {
$next = $_REQUEST['recid'];
// if the session page number is less than the requested page this is a next page request
if($_REQUEST['page'] > $_SESSION['num_pages']) {
$add_page = true;
}
// is the page requested is less it could be a back button request or page select
else if($_REQUEST['page'] < $_SESSION['num_pages']) {
// remove the array elements so it is shown correctly
$pages = $_SESSION['pages'];
remove_to($pages,$_REQUEST['recid']);
$_SESSION['pages'] = $pages;
$add_page = false;
}
}
// connect to the i5
$options = array("i5_naming"=>DB2_I5_NAMING_ON,"i5_lib"=>"CHLIB");
$conn = db2_connect("","","",$options);
if (is_bool ( $conn ) && $conn == FALSE) {
die ( "No Connection " .db2_conn_errormsg($conn) );
}
$query = "select
jobname,
usrname,
jobid,
jobq,
jobqlib,
unstamp(ENTERTS) as TS,
unstamp(STARTTS) as STR,
unstamp(ENDEDTS) as END,
jobtype,
subtype,
endcde,
prcused,
jobstate from jq0
where JOBID > " .$next ." ORDER BY enterts"
." FETCH FIRST " .$size ." ROWS ONLY";
// connect to the database
$result = db2_exec($conn,$query);
if(!$result) {
die("db2_exec " .db2_stmt_errormsg());
}
?>
<!-- Build the table and add the records as required -->
<a href="#bottom">Goto End</a>
<table border="1">
<tr class="<?php echo("d1"); ?>">
<td><?php echo("Job ID"); ?></td> <!-- Jobid -->
<td><?php echo("Job Name");?></td> <!-- JobName -->
<td><?php echo("User Name");?></td> <!-- UserName -->
<td><?php echo("Job Queue");?></td> <!-- Job Queue -->
<td><?php echo("JobQ Library");?></td> <!-- JobQ Library -->
<td><?php echo("Entered Time");?></td> <!-- Entered TS -->
<td><?php echo("Started Time");?></td> <!-- Start TS -->
<td><?php echo("Ended Time");?></td> <!-- Ended TS -->
<td><?php echo("Job Type");?></td> <!-- Job Type -->
<td><?php echo("Sub Type");?></td> <!-- SubType -->
<td><?php echo("End Code");?></td> <!-- EndCode -->
<td><?php echo("Processor Used");?></td> <!-- Proc Used -->
<td><?php echo("Job State");?></td> <!-- JobState -->
</tr><?php
for($i = 0; $i < $size; $i++) {
$rec = db2_fetch_both($result);
// store the first record in the page array
if(!$rec)
break;
if($i == 0)
$_SESSION['first'] = $rec[2];
?>
<tr class="<?php echo("d" .($i & 1)); ?>">
<td><?php echo('<a href="display_det.php?id=' .$rec[2] .'">' .$rec[2] .'</a>'); ?></td>
<td><?php echo($rec[0]);?></td>
<td><?php echo($rec[1]);?></td>
<td><?php echo($rec[3]);?></td>
<td><?php echo($rec[4]);?></td>
<td><?php echo($rec['TS']);?></td>
<td><?php echo($rec['STR']);?></td>
<td><?php echo($rec['END']);?></td>
<td><?php echo($rec[8]);?></td>
<td><?php echo($rec[9]);?></td>
<td><?php
if($rec[13] == 0) echo("Completed Normally");
else if ($rec[10] == 10) echo("Completed Normally During Controlled Ending");
else if ($rec[10] == 20) echo("Exceeded End Severity");
else if ($rec[10] == 30) echo("Ended Abnormally");
else if ($rec[10] == 40) echo("Ended before becoming Active");
else if ($rec[10] == 50) echo("Ended while Active");
else if ($rec[10] == 60) echo("Subsystem ended while job was Active");
else if ($rec[10] == 70) echo("System ended abnormally while job was Active");
else if ($rec[10] == 80) echo("Job ended (ENDJOBABN");
else if ($rec[10] == 90) echo("Forced end after ENDJOBAN");
else if ($rec[10] == 999) echo("On Job Queue");
?></td>
<td><?php $prcused = $rec[11]/10000; echo(round((float)$prcused,2));?></td>
<td><?php
if($rec[12] == 0) echo("Ended");
else if($rec[12] == 1) echo("Failed");
else if($rec[12] == 2) echo("Job Queue");
else if($rec[12] == 3) echo("Unknown");?></td>
</tr><?php
} ?>
</table>
<a name="bottom"></a>
<!-- Only display next link if max records read -->
<?php
// store the last record read
$_SESSION['last'] = $rec[2];
// count the array size
$num_pages = count($pages);
// if add_page set add a new element to the array
if($add_page){
$pages[] = array($num_pages += 1,$_SESSION['first'],$_REQUEST['recid']);
$_SESSION['pages'] = $pages;
}
// store the number of pages
$_SESSION['num_pages'] = $num_pages;
// if there are more than 1 pages show the page selection links
if($num_pages > 1) {
// do not show last page
$count = $num_pages - 1;
//loop throught the pages stored
for($p = 0; $p < $count; $p++) {
echo('<a href="' .$_SERVER[PHP_SELF] .'?page=' .$pages[$p][0] .'&recid=' .$pages[$p][1] .'">' .$pages[$p][0] .'</a>' .' ');
}
}
// if displayed max records may be more so show next link
if($i == $size) ?>
<a href="<?php echo($_SERVER[PHP_SELF] ."?page=" .++$num_pages ."&recid=" .$rec[2]); ?>">Next</a>
<?php
// close down the connection and destroy the $result
db2_free_result($result);
db2_close($conn); ?>
</BODY>
</HTML>
The main change is how we store the page number and the last requested record ID, this allowed us to remove a number of the $query options plus manage the page array much better. You will also notice we have added a link to the top of the page to allow the user to jump to the bottom of the page where the links are, we did think about providing a set of links at the top of the page but the details required for the link are not available until the end of the script. We could have run a second SQL request and captured the information to allow the links to be generated, but felt that the overhead in doing this outweighed the benefits..
We don’t get much feedback but do see a very large number of hits to new posts so I hope you are gaining some benefit from the articles we publish?
Chris…