PHP Example Retrieve DB2 Records Per Page

Retrieving large recordset contains so many rows on web page will consume a lot of resources both server and client. Responsiveness of web page decreasing.

We need to retrieve that recordset part by part and display it using web paging method. In my previous post about Limiting DB2 Record Number I had show sql example to limit large recordset on SAMPLE Database.

This php example show employee’s related project:

session_start();
$page = $_GET["page"];
$pagesize = 50;

$db = "SAMPLE";
$user = "agung";
$passwd = "abc123";
$conn = db2_connect($db,$user,$passwd);

if ($_SESSION["countrecord"] == "") {
	$sqlcount = "select count(*) as cnt from employee
	inner join empprojact on employee.empno = empprojact.empno
	inner join project on empprojact.projno = project.projno
	inner join act on empprojact.actno = act.actno
	inner join department on employee.workdept=department.deptno";

	$stmt = db2_prepare($conn, $sqlcount);
	$result = db2_execute($stmt);
	$countrecord = 0;
	if ($result) {
		$rowcount = db2_fetch_array($stmt);
		$countrecord = $rowcount[0];
	} 
	$_SESSION["countrecord"] = $countrecord;
}

if ((int) $_SESSION["countrecord"] > 0) {
	$pagecount = ceil($_SESSION["countrecord"]/$pagesize);
	if ($page=="") {
		$page = 1;
	} elseif ((int) $page > (int) $pagecount) {
		$page = $pagecount;
	}
	$recnumst = ($page-1)*$pagesize + 1;
	$recnumen = $recnumst + ($pagesize -1);

	$sql = "select * from (select firstnme, lastname, deptname, projname, actdesc,row_number() over() as rn from employee
	inner join empprojact on employee.empno = empprojact.empno
	inner join project on empprojact.projno = project.projno
	inner join act on empprojact.actno = act.actno
	inner join department on employee.workdept = department.deptno) as tables where rn between ".$recnumst." and ".$recnumen;

	$stmt = db2_prepare($conn, $sql);
	$result = db2_execute($stmt);
	if ($result) {
		while ($row = db2_fetch_array($stmt)) {
			echo "$row[0] $row[1] $row[2] $row[3] $row[4]<br>";
		}
	}
}
db2_close($conn);

Page querystring indicates page number. We set page size to 50 which means maximum record number will be showed on web page.
Calculation result of record count is stored in session. So we don’t have to re-calculate on every page. Just once on web page startup.
If page querystring has not setted then we set it to one. On the other hand when page number is larger than maximum page number it will set to maximum number.
Execute query with row number between record number start until record number end to limit recordset retrieval. Show that recordset on web page.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s