Paging With PHP [Part1]

In this post I’ll give you some tutorial how to paging with PHP. In this case I used SQLite for my database. First I make customers table with SQLite admin.

SQlite Admin

Then I fill customers table with 10 customers data. This is PHP script to show all data without paging.

^?View Code PHP

<?
	define("DBNAME","paging.sdb");
	define("MODE",0666);
echo "<table border='1'>
<tr>
<td>ID</td>
<td>Name</td>
<td>Address</td>
<td>City</td>
<td>Phone</td>
</tr>";
try 
{
	$dbhandle = new SQLiteDatabase(DBNAME,MODE,$sqlerr);
	$sql = "SELECT * FROM customers";
	$query = $dbhandle->query($sql);
	$result = $query->fetchAll(SQLITE_NUM);
	if(!$result) throw new Exception("Something wrong in query, number: ".$dbhandle->lastError());
	foreach ($result as $row) 
	{
		$id=$row[0];
		$name=$row[1];
		$address=$row[2];
		$city=$row[3];
		$phone=$row[4];
		echo "<tr>
				<td>".$id."</td>
				<td>".$name."</td>
				<td>".$address."</td>
				<td>".$city."</td>
				<td>".$phone."</td>
			</tr>";
	}
	$dbhandle=null;
}
catch (Exception $e)
{
	die($e->getMessage());
}
echo "</table>";
?>

And the result from Browser:
Without Paging
First you must know about paging concept. You can see on the picture below.

Ilustration Position and Limit

Form the illustration first data position on database started by 0 and for the example the limit is . There are SQLite syntax with LIMIT are:

^?View Code PHP

1	SELECT * FROM customers LIMIT $position,$limit

Example, if you want to show 5 data on page 1 (1-5), SQLite syntax is:

^?View Code PHP

1	SELECT * FROM customers LIMIT 0,5

Then, if you want to show 5 next data on page 2 (6-10), SQLite syntax is:

^?View Code PHP

1	SELECT * FROM customers LIMIT 5,5

Now, I’ll give you 3 step, how to paging.

Give limit and then check number of page and position when the script running.
Matching SQLite syntax with limit and position.
Count data total and page total with link for navigation page.

This is PHP script for paging:

^?View Code PHP

<?
	define("DBNAME","paging.sdb");
	define("MODE",0666);
 
$limit=5; //number of limit per page data shown
$pages=$_GET['pages'];
if(empty($pages)){
	$position	= 0; // declare position
	$pages		= 1; // declare pages
}
else{
	$position	=	($pages-1) * $limit; //if any page position = page-1*limit
}
 
 
echo "<table border='1'>
<tr>
<td>ID</td>
<td>Name</td>
<td>Address</td>
<td>City</td>
<td>Phone</td>
</tr>";
try 
{
	$dbhandle = new SQLiteDatabase(DBNAME,MODE,$sqlerr);
	$sql = "SELECT * FROM customers LIMIT $position,$limit"; //show data with limit
	$query = $dbhandle->query($sql);
	$result = $query->fetchAll(SQLITE_NUM);
	if(!$result) throw new Exception("Something wrong in query, number: ".$dbhandle->lastError());
	foreach ($result as $row) 
	{
		$id=$row[0];
		$name=$row[1];
		$address=$row[2];
		$city=$row[3];
		$phone=$row[4];
		echo "<tr>
				<td>".$id."</td>
				<td>".$name."</td>
				<td>".$address."</td>
				<td>".$city."</td>
				<td>".$phone."</td>
			</tr>";
 
	}
	$dbhandle=null;
}
catch (Exception $e)
{
	die($e->getMessage());
}
 
//select all to count how many data
try
{
		$dbhandle = new SQLiteDatabase(DBNAME,MODE,$sqlerr);
		$sql = "SELECT * FROM customers";
		$query = $dbhandle->query($sql);
		$result = $query->fetchAll(SQLITE_NUM);
		if(!$result) $datacount=0;
		foreach ($result as $row) 
		{   
			$datacount++; //number of data save as $datacount
		}
}
catch (Exception $e)
{
	die($e->getMessage());
}
 
$pagecount=ceil($datacount/$limit); //count pages
echo "</table><br />pages: ";
for($i=1;$i<=$pagecount;$i++) //make link of pages
if ($i != $pages){
	echo "<a href=$_SERVER[PHP_SELF]?pages=$i>$i</a> | ";
}
else{
	echo "<strong>$i</strong> |";
}
echo "<p>Total user: <strong>$datacount</strong></p>";
 
?>

Whenever that script running, first will checking “Any $pages or not?”. If noting, then $page=1 and $position=0. If any $pages, then $position=($pages-1)*$limit. Example: On page 3, then $position=(3-1)*5=10.

Command ceil($datacount/$limit); on script. Ceil is used to eliminated commas. Example: $datacount=36 divide by $limit=5 and the result 7.2. With ceil(36/5) function the result is 8 pages.

And the result from browser are:

5 data on page one

5 data on page 2

5 data on page 2

Hmm.. Congratulation, let’s try^^

To be continue…

Einchi