PHP paging tutorial

Written by Codes Tips on April 4, 2009 – 2:27 am -

In this tutorial I want to make a paging tutorial in php.

A friend of mine tell me to help him with a script to do paging in php from a mysql database and I would like to share with all.
If you wanna take data from a xls,csv or other files, from a database like mysql,mssql and you wanna list the results on the page, you may have many results for your result.
I want to present an example so you can understand how a paging can be made in php. I made an example about how to search results from a mysql database and make pagination on the results.

You should first have a form in html from where the user can input the search field:

<form action="Search.php" method="POST">
<table align="center">
<tr><td>Name:</td><td><input type="text" name="search" width="40"></td><td><input name=submit type=submit value=Search><input type=hidden name=hiddensearch value=yes></td></tr>
</table>
</form>

You should now put the code before the html form for verifying if the user submits the form through a POST, if not show the html form. So we will have 2 cases, when when we first post the form we will show the first page and links to all other pages. The links to other pages will look something like search.php?search=Name&page=10 -> this will fetch the results from database for the page number 10 with the search query ‘Name’. Each page will have a number of results say 20, which we will call it pagesize.

First we should connect to the mysql server and make a query on the database. We discuss previously how we can connect to a database and how to make a query, go here for learning how to connect and fetch results from mysql. After we make a query we get each row of the query result and verify if it is on the current page( if it is a post then we will get the first $pagesize records). To select records for a page we should use verify a condition for every row:

if ($nr>=($pagesize*($page-1)+1)&&($nr<=($pagesize*$page)))
{
 //write the result
}

Where $nr - is the current record
$pagesize - is the size of a page
$page - is the page number

In the end we should write all the links to other pages( the navigation through result pages part ):

<?
	echo '<tr>';
	echo '<td>';
	for ($i=1;$i<=ceil($nr/$pagesize);$i++)
	if ($i!=$page)
	  echo '<a href="Search.php?search='.urlencode($name).'&page='.$i.'">'.$i.'</a> | ';
	else
	  echo $i.' | ';  
	echo '</td>';
	echo '<tr>';
?>

Here is the complete code:

<?
 
$server="";
$user="";
$pass="";
$database="";
 
include("header.php");
 
 if($_POST['hiddensearch'] == 'yes'|| isset($_GET['search']))
  {
 
    $connect=mysql_connect($server, $user, $pass)
      or die ("Database Connection Error");
 
$pagesize=20;//how many results will be on a page	   
$page=$_GET['page'];//curent page
if ($page=="")
   $page=1;
 
//we 
if ( $_POST['hiddensearch'] == 'yes')  
$name=$_POST['search'];
else
$name=$_GET['search'];
 
//we write the query
$result = mysql_db_query($database, "SELECT * FROM Table Where Name='".$name."'");
 
 
if(mysql_num_rows($result) > 0)
{
 
    ?>
<br>
<center>Results  <?=mysql_num_rows($result);?> with <b><?echo $name;?> parameter</b></center>
 
 
 
 <TABLE align="center" cellspacing="0" cellpadding="0">
<?
$nr=0;
//fetch every row and verify if it is in the current page
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
 $nr++;
 if ($nr>=($pagesize*($page-1)+1)&&($nr<=($pagesize*$page)))
 {
 
   echo '<tr>'; 
 
	echo '<td>'.$row['Name'].'</td>';
   echo '</tr>';
 }
}
 
//build the navigation links to other pages
  if ($pagesize<$nr)
   {
 
 
	echo '<tr>';
	echo '<td>';
	for ($i=1;$i<=ceil($nr/$pagesize);$i++)
	if ($i!=$page)
	  echo '<a href="Search.php?search='.urlencode($name).'&page='.$i.'">'.$i.'</a> | ';
	else
	  echo $i.' | ';  
	echo '</td>';
	echo '<tr>';
   }
echo '</table>';
 
}
else
  {
   echo 'No results found.';
 
  }
mysql_close($connect);
}
else
{
?>
<center><b>Search </b></center><br><br>
<form action="Search.php" method="POST">
<table align="center">
<tr><td>Name:</td><td><input type="text" name="search" width="40"></td><td><input name=submit 
 
type=submit value=Search><input type=hidden name=hiddensearch value=yes></td></tr>
</table>
</form>
 
<?
}
include("footer.php");
?>