Mysql Php Pagination Script

Most of the times it happens that when we query mysql we get hundreds of results. In such situations we want those results to be displayed page wise i.e. a certain number of results per page. This is known as Pagination. In this article I am sharing a very simple and easy to implement php pagination script. This script is divided in three parts one part is directly inserted in your main file in which you are querying the database and the second part is inserted in a separate file which needs to be included in each file you want to use this pagination script, and the third part is inserted in the bottom of the main file where you want to display number of pages, page links and next page link.

You need to save the following script in a separate file and name it as “pagination.php”.

< ?php
// MAKE QUERY STRING
$sortby=isset($_REQUEST['sortby']) ? $_REQUEST['sortby'] : "";
$orderby=isset($_REQUEST['orderby']) ? " ORDER BY '{$_REQUEST['orderby']}' " : "";

$qsarr = array();
if($_SERVER['QUERY_STRING'] != "")
{
 $a = explode('&', $_SERVER['QUERY_STRING']);
  
 $i = 0;
 while ($i < count($a))
 {
  $b = split('=', $a[$i]);
  $qskey = htmlspecialchars(urldecode($b[0]));
  $qsval = htmlspecialchars(urldecode($b[1]));
  $qsarr[$qskey] = $qsval;
  $i++;
 }
 if(array_key_exists("pageno",$qsarr))
 {
  unset($qsarr['pageno']);
 }
 if(count($qsarr) > 0)
 {
   $PageLink = basename($_SERVER['PHP_SELF'])."?".http_build_query($qsarr)."&";
    }
 else
 {
   $PageLink = basename($_SERVER['PHP_SELF'])."?".http_build_query($qsarr);
   
 }
}
else
{
 $PageLink = basename($_SERVER['PHP_SELF'])."?";
}
if(array_key_exists("sortby",$qsarr))
{
 $qsarr['sortby'] = ($qsarr['sortby'] == "DESC") ? "ASC" : "DESC";
}
else
{
 $qsarr['sortby'] = "ASC";
}
?>

I am using an example for showing how the main pagination script is to be added in your code.

< ?php include_once("pagination.php"); ?>
< ?php
$sql="SELECT * FROM table1 "; // example query

 //////////////////PAGINATION STARTS HERE////////////

 $sqlCount = $sql; 

 $rsCount = mysql_query($sqlCount);

 $totalrows = mysql_num_rows($rsCount);

 $limit=5;

 if(isset($_REQUEST['page'])){$page = $_REQUEST['page'];}

 else{

  $page = 1;

 }

 $limitvalue = ($page - 1) * $limit;   

 ob_start();

 if($page > 1){

   $pageprev = $page-1;
  if ($pageprev==1)
   echo("< a href=\"http://www.funmarkaz.net/funny_sms.php\"><< /a> ");
  else
   echo("< a href=\"".$PageLink."page=$pageprev\"><< /a> ");

 }                                                               

 $numofpages = ceil($totalrows / $limit);

 for($i = 1; $i <= $numofpages; $i++)

 {

  if($i > $page-10 and $i < $page+10)

  {

   if($page == $i)

    echo($i." ");

   else
   {
    if ($i==1)
     echo("< a href=\"http://www.funmarkaz.net/funny_sms.php\">$i< /a> ");
    else
     echo("< a href=\"".$PageLink."page=$i\">$i< /a> ");
   }
  }

 }   

 if($page < $numofpages){

  $pagenext = ($page + 1);
  
  echo ("< a href=\"".$PageLink."page=$pagenext\">>< /a>");

 }  

 $pagination = ob_get_contents();

 ob_end_clean();



 $sql.= " LIMIT $limitvalue, $limit";

 //echo $sql;

 $res = mysql_query($sql);

 $total_displaying = mysql_num_rows($res);

 $starting = ($total_displaying > 0) ? $limitvalue+1 : 0;

 $ending = $limitvalue+$total_displaying;

 //////////////// PAGINATION ENDS HERE////////////

 $result=mysql_query($sql); //example query
?>

Now add the below mentioned code where you want to display page numbers, page links, and next page link.

< table width="100%" border="0" cellpadding="1" cellspacing="3">
          < tr>
            < td width="40%" class="style2">< ?php echo $starting; ?> - < ?php echo $ending; ?> out of < ?php echo $totalrows; ?>< /td>
            < td width="60%" align="center" class="style2">< ?php echo $pagination; ?>< /td>
          < /tr>
        < /table>

If you find any difficulties in implementing this script or if you have any queries, feel free to comment on this post.