× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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>




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.