Good example Kelly
One pointer might be to include an ORDER BY clause in the SQL otherwise you
are at the fickle mercy of the SQL engine as to the order of your results.
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Kelly Cookson
Sent: 04 December 2009 22:06
To: Web Enabling the AS400 / iSeries
Subject: [WEB400] Pagination with PHP and DB2 (srollable cursor, no OLAP)
The example I created using ROWNUMBER() OVER() had some limitations.
Here is another simple example of pagination that uses a scrollable
cursor rather than the OLAP functions.
This is certainly not the only way to paginate with DB2. I just thought
it might be useful to have a working example in the WEB400 archive.
The script assumes a DB2 file called MYLIB/MYEOP. The MYEOP file
contains four fields: EOPYEAR, EOPPRD, EOPBEGDT, EOPCLSDT.
EOPYEAR = year
EOPPRD = period
EOPBEGDT = begin date
EOPCLSDT = close date
The file holds the begin and close dates for each business period of
every year. I want to scroll back and forth through the file, seeing the
information for one year at a time. (We happen to have 13 business
periods in each year. So I want to see 13 records at a time.)
The script uses a an ordinary SELECT statement to rows records from the
file. However, when executing the statement, the script requests a
scrollable cursor.
$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM
DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' =>
DB2_SCROLLABLE));
The script then uses a for loop to get individual rows from the result
set. The variable $rn is the row on which we want to start the loop. The
variable $limit is the row on which we want to end the loop.
$i=$rn;
for ($i=$rn; $i<=$limit; $i++)
{
$row = db2_fetch_both($result, $i);
echo "<tr><td>".$row["EOPYEAR"]."</td>";
echo "<td>".$row["EOPPRD"]."</td>";
echo "<td>".$row["EOPBGSDT"]."</td>";
echo "<td>".$row["EOPCLSDT"]."</td></tr>";
}
The script also contains two forms. The form buttons let the user post
requests to scroll forward to the "next" records or backward to the
"previous" records. The session variable $_SESSION["rn"] stores the row
number across scrolling requests.
The only limitation I know about with this approach is it wouldn't work
with the db2_prepare function. (If you can figure out how to get it to
work with db2_prepare, please share!)
The complete script is provided below. Hope this helps.
Thanks,
Kelly
<?php
session_start();
switch ($_POST["scroll"])
{
case "next":
$_SESSION["rn"] = $_SESSION["rn"] + 13;
unset($_POST["scroll"]);
break;
case "previous":
$_SESSION["rn"] = $_SESSION["rn"] - 13;
if ($_SESSION["rn"] < 1)
{
$_SESSION["rn"] = 1;
}
unset($_POST["scroll"]);
break;
default:
$_SESSION["rn"] = 1;
break;
}
$rn = $_SESSION["rn"];
$limit = ($rn + 12);
?>
<html>
<head>
<title>Pagination Example</title>
<!--Optional formatting for the table and the buttons. -->
<style type="text/css">
body {
text-align: center;
}
table {
margin: 10px;
border: 1px solid #000000;
}
th, td {
border: 1px solid #dedede;
padding: 2px 4px 2px 4px;
}
form {
display: inline;
text-align: center;
}
input {
width: 100px;
}
</style>
</head>
<body>
<h2>Years and Periods</h2>
<table>
<tr>
<th>Year</th>
<th>Period</th>
<th>Begin</th>
<th>End</th>
</tr>
<?php
$conn = db2_connect("", "", "");
$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM
DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' =>
DB2_SCROLLABLE));
$i=$rn;
for ($i=$rn; $i<=$limit; $i++)
{
$row = db2_fetch_both($result, $i);
echo "<tr><td>".$row["EOPYEAR"]."</td>";
echo "<td>".$row["EOPPRD"]."</td>";
echo "<td>".$row["EOPBGSDT"]."</td>";
echo "<td>".$row["EOPCLSDT"]."</td></tr>";
}
db2_close($conn);
?>
</table>
<form method="post" action="pagination2.php">
<input type="hidden" name="scroll" value="previous" />
<input type="submit" value="Previous" />
</form>
<form method="post" action="pagination2.php">
<input type="hidden" name="scroll" value="next" />
<input type="submit" value="Next" />
</form>
</body>
</html>
midrange.com
