The script assumes a file called DOTDATA/EOPMAST, not MYLIB/MYEOP.
Also, sorry for all the extra blank lines. I don't see all those lines
when I format the email. They show up when posted.

Kelly

-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Kelly Cookson
Sent: Friday, December 04, 2009 4:06 PM
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>




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.