Simple pagination using PHP-MySQL
Paging means showing your query result in multiple pages instead of just put them all in one long page. Imagine waiting for five minutes just to load a search page that shows 1000 result. By splitting the result in multiple pages you can save download time plus you don't have much scrolling to do.
To show the result of a query in several pages first you need to know how many rows you have and how many rows per page you want to show. For example if I have 95 rows and I show 10 rows per page that mean I'll have ten pages (rounded up).So the pagination needs several steps to complete.
Step 1: Create a Database connection
Establish a database cconnection to execute the select query.
<?
DEFINE ('DB_USER', 'dbusername');
DEFINE ('DB_PASSWORD', 'dbpassword');
DEFINE ('DB_HOST', 'dbroot');
DEFINE ('DB_NAME', 'dbname');
$connection = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database');
mysql_select_db(DB_NAME) or die ('Error.. Could not connecct to database');
?>
Step 2:
Find the total number of records and the current page number. If the current page number is not specified then it tooks as first page. After that we can calculate the total number of pages.This is calculated by total records / row per page. For the first time the page value will be null, so by default we will set the limit to 10 and page to 1.
<?
extract($_GET);
// number of rows in one page
$page_rows = 10;
// this will find the total number of records in the table
$data = mysql_query("SELECT count(id) as totcount FROM user") or die(mysql_error());
$row_num = mysql_fetch_object($data);
$rows = $row_num->totcount;
// this function will find how much page is neded for pagination.
$last = ceil($rows/$page_rows);
if ($pagenum < 1)
$pagenum = 1;
elseif ($pagenum > $last)
$pagenum = $last;
?>
If this variables are not blank, i.e. if the users click on the next or previous or page links then the page number and number of items to displayed will be not null and we will display data accordingly.
Step 3:Listing the datas
After that we can display the selected values.
<?
$counter = $starter+1;
while($info = mysql_fetch_array( $data_p ))
{
?>
<tr >
<td height="21"> </td>
<td><?=$count++?></td>
<td><?=$info['username'];?></td>
<td><?=$info['email']?></td>
</tr>
<?php
}
?>
Step 4
: Set up the previous,next options.
<?php
if ($pagenum != 1)
{
$previous = $pagenum-1;
?>
<a href="?pagenum=1" onclick=""> <<-First</a>
<a href="?pagenum=<?=$previous?>"> <-Previous</a>
<?php
}
?></td>
<td width="296">
<?php
if ($pagenum != $last)
{
$next = $pagenum+1;
?>
<a href="?pagenum=<?=$next?>" >Next -></a>
<a href="?pagenum=<?=$last?>" >Last ->></a>
<?php
}
?>
This area tells the user which page is currently being viewed, the total number of pages available, and contains links to go either forwards or backwards through the available pages. The hyperlinks are displayed as simple text if there are no previous or next pages. Note that these hyperlinks do not put the word FIRST, PREV, NEXT or LAST as a parameter in the URL string - they all specify an absolute page number in the format pagenum =value
Source Code
This is the complete source code
<?php
// will extract the GET value "pagenum"
extract($_GET);
// number of rows in one page
$page_rows = 10;
// data base connectivity code starts
$con = mysql_connect('localhost','root','');
if(!$con)
{
echo "could not connect";
}
mysql_select_db('test');
// data base connectivity code ends
// this will find the total number of records in the table
$data = mysql_query("SELECT count(id) as totcount FROM user") or die(mysql_error());
$row_num = mysql_fetch_object($data);
$rows = $row_num->totcount;
// this function will find how much page is neded for pagination.
$last = ceil($rows/$page_rows);
if ($pagenum < 1)
$pagenum = 1;
elseif ($pagenum > $last)
$pagenum = $last;
// this code find, the starting record of the table for SQL selection
$starter = ($pagenum - 1) * $page_rows;
$count = $starter+1;
$max = 'limit ' .$starter.',' .$page_rows;
// executing the query
$data_p = mysql_query("SELECT id,username,email FROM user $max") or die(mysql_error());
?>
<form name="frmpagination" method="post">
<table width="609" height="107" border="0" class="boarder" cellpadding="0" cellspacing="0">
<tr>
<td height="21"> </td>
<td> </td>
<td colspan="2"></td>
</tr>
<tr>
<td width="14" height="21"> </td>
<td width="55"><strong>Sl No</strong></td>
<td width="261"><strong>Name</strong></td>
<td width="279"><strong>Mail Id</strong></td>
</tr>
<tr>
<td colspan="4"><hr /></td></tr>
<?php
$counter = $starter+1;
while($info = mysql_fetch_array( $data_p ))
{
?>
<tr >
<td height="21"> </td>
<td><?=$count++?></td>
<td><?=$info['username'];?></td>
<td><?=$info['email']?></td>
</tr>
<?php
}
?>
<tr>
<td height="21"> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="21"> </td>
<td> </td>
<td colspan="2"></td>
</tr>
<tr>
<td height="21"> </td>
<td colspan="3"><table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="43"> </td>
<td width="253">
<?php
if ($pagenum != 1)
{
$previous = $pagenum-1;
?>
<a href="?pagenum=1" onclick=""> <<-First</a>
<a href="?pagenum=<?=$previous?>"> <-Previous</a>
<?php
}
?></td>
<td width="296">
<?php
if ($pagenum != $last)
{
$next = $pagenum+1;
?>
<a href="?pagenum=<?=$next?>" >Next -></a>
<a href="?pagenum=<?=$last?>" >Last ->></a>
<?php
}
?> </td>
</tr>
</table></td>
</tr>
</table>
</form>
