add pagination using PHP

In this article we illustrate the concept of adding and using pagination in a web page using PHP code. Here we use an example from our project templates database . This pagination logic is not on the client side but it is at the server side where we limit the SQL queries results by setting a start and end. By changing the start and end we limit the current page records. This pagination example contains the following features :

• Shows numbers for each page and highlight the current page.
• Shows Next link at the end of numbers and hide it if there is no next pages.
• Shows Prev link at the start of numbers list and hide it if there is not previous pages.
• Shows a combo box to select the number of elements to show per page (Ex: 4,8,12).

Below, you will find the PHP file responsible for listing the styles with pagination, we will focus in the explanation on the lines related to the pagination functions.
php code

<?php
           // All database details will be included here 
//include 'Controllers/Model/stylesshow.php';


  $page_name="";
$Type_ID=1;

if(isset($TYPE_NAME))
  {
 
 
	   $currentType=GetTypeIDByName($TYPE_NAME);
	   $num=mysql_num_rows($currentType);
	   if($num>0)
	   {
	   $Type_ID=mysql_result($currentType,0,"ID");
	   }
	   else
	   {
	   $Type_ID=0;
	   }
	   
  }
  else 
  {
  $TYPE_NAME="";
  }
  $folderpath="stylesdata/".$TYPE_NAME."/";
  $STYLE_CATEGORY="";
  
  if(isset($_GET['template_category_id']))
  {
    $STYLE_CATEGORY=$_GET['template_category_id'];
	if($STYLE_CATEGORY=="0")
	$STYLE_CATEGORY="";
       
  }
  ?>
  <div class="content">
  <p style="color:#4396CA;">
   Welcome to <?php echo $TYPE_NAME;?> 
   styles database.Here you will find free styles to download .
   
   You can comment on the style you like and rate it .
   
   Please share with us what you think .
  </p>
 
  <?php

@$limit=$_GET['limit']; // Read the limit value from query string. 
  
switch($limit)
{
	case 4:
$select4="selected";
$select12="";
$select8="";
break;

case 8:
$select8="selected";
$select12="";
$select4="";
break;

default:
$select12="selected";
$select8="";
$select4="";
break;

}
 //  If you use this code with a different page ( or file ) name then change this 
 $start=0;
    if(isset($_GET['start']))
$start=$_GET['start'];							// To take care global variable if OFF
 
if(!($start > 0)) {                         // This variable is set to zero for the first page
$start = 0;
}

echo "<table><tr><td>Select preview number :</td><td>

 <form name='recordsview' method='get' action=$page_name >
<table><tr><td><select name=limit>

<option value=12 $select12>12 styles</option>
<option value=8 $select8>8 styles</option>
<option value=4 $select4>4 styles</option>

</select> </td><td><img src='Template/Images/Buttons/go.png' 

onclick='javascript:document.recordsview.submit();' class='imglink'

  width='25' height='25'></td></tr></table>";

echo "</td><td>&nbsp;&nbsp;&nbsp;</td><td>Select category:</td><td>
<form name='recordscategory' 
method='get' action=$page_name >
<table><tr><td>";

 include'Categoryselector.php';
 
echo " </td><td><img src='Template/Images/Buttons/go.png' 
onclick='javascript:document.recordsview.submit();' 
class='imglink'  width='25' height='25'></td></tr></table></td></tr></table>  ";
 
$eu = ($start - 0); 

if(!$limit > 0 ){ // if limit value is not available then let us use a default value
$limit = 12;    // No of records to be shown per page by default.
}                             
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit; 

$order=" ORDER BY ";
/////////////// WE have to find out the number of records in our table. We will use this to break the pages///////

 @$sk=$_GET['sk'];
switch($sk)
{

case 'v':
$order=$order." VIEWS ";
$selection = array('style' => "",
                   'view'  => "selected",
                   'download'   => "",
			       'submit' => "",
                   'update'  => "",
                   'version'   => "");
break;

case 'd':
$order=$order." DOWNLOADS ";
$selection = array('style' => "",
                   'view'  => "",
                   'download'   => "selected",
			       'submit' => "",
                   'update'  => "",
                   'version'   => "");
break;

case 'a':
$order=$order." SUBMIT_DATE ";
$selection = array('style' => "",
                   'view'  => "",
                   'download'   => "",
			       'submit' => "selected",
                   'update'  => "",
                   'version'   => "");
break;

case 'u':
$order=$order." UPDATE_DATE ";
$selection = array('style' => "",
                   'view'  => "",
                   'download'   => "",
			       'submit' => "",
                   'update'  => "selected",
                   'version'   => "");
break;

case 'b':
$order=$order." STYLE_VR ";
$selection = array('style' => "",
                   'view'  => "",
                   'download'   => "",
			       'submit' => "",
                   'update'  => "",
                   'version'   => "selected");
break;

default:
$order=$order." STYLE_NAME ";
$selection = array('style' => "selected",
                   'view'  => "",
                   'download'   => "",
			       'submit' => "",
                   'update'  => "",
                   'version'   => "");
break;

}
@$sd=$_GET['sd']; // Read the limit value from query string. 

switch($sd)
{
case 'd':
$order=$order." DESC ";
$selectionsd = array('asc' => "",
                   'desc'  => "selected");
break;


default:
$order=$order." ASC ";
$selectionsd = array('asc' => "selected",
                   'desc'  => "");
break;

}

 if($STYLE_CATEGORY!="")
 {
$result2=GetStylesPagesbyOrderCat($order,$Type_ID,$STYLE_CATEGORY);
}
else 
{
$result2=GetStylesPagesbyOrder($order,$Type_ID);
}

echo mysql_error();
$nume=mysql_num_rows($result2);
/////// The variable nume above will store the total number of records in the table////

/////////// Now let us print the table headers ////////////////
$bgcolor="#f1f1f1";

////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////
  if($STYLE_CATEGORY!="")
  {
$result= GetStylesPagesbyLimitCat($order,$eu, $limit,$Type_ID,$STYLE_CATEGORY);
}
 else
 {
 $result= GetStylesPagesbyLimit($order,$eu, $limit,$Type_ID);
 }
 
display($back,$page_name,$limit ,$nume ,$this1,$eu,$next,$selection,$selectionsd,$sk,$sd,$STYLE_CATEGORY);
//////////////// Now we will display the returned records in side the rows of the table/////////
$i=0;
include 'Controllers/ratingcontroller.php';  
echo"<br/><table border=0 cols=%100>";

while($noticia = mysql_fetch_array($result))
{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}

$image=	$folderpath."images/".$noticia['STYLE_ID'].".PNG";
$largeimage= $folderpath."/images/".$noticia['STYLE_ID']."_2.PNG";
$rate =  getRatingImg($noticia['STYLE_ID'],$rating);
$file= $folderpath."uploads/".$noticia['STYLE_NAME']."_".$noticia['STYLE_ID'].".zip";
if(($i%4)==0)
	echo"<tr>";
echo "<td>";		
 
echo " <div class='styleroundcont'>
<div class='styleroundtop'>
	 <img src='Template/Images/c1.gif' 
	 width='15' height='15' class='corner' 
	 style='display: none' /></div> <center>";

echo "<a style='cursor: hand;' 
href='styleview/".$TYPE_NAME."/".$noticia['STYLE_NAME']."/".$noticia['STYLE_ID']."'>
<strong>$noticia[STYLE_NAME]</strong><br />";
echo "<div class='imageback'><img name='$image' alt='' src='$image' 
        
         
         id = 'tpl_$noticia[STYLE_ID]'/></a>
		 <br />";
		 ?>
<script  type="text/javascript">
       image = new Image;
	   image.src = 'http://localhost/templatesdragon/<?php echo $largeimage;?>';	   
		new TemplatePreview('tpl_<?php echo $noticia['STYLE_ID'];?>', {
        title:  'Template <?php echo $noticia['STYLE_ID'];?>',
        src:    image.src,
        width:  image.width,
        height:  image.height   });

    </script>
	<?php
echo "Viewed: $noticia[VIEWS]<br />Downloads: $noticia[DOWNLOADS]<br />";
echo "<img name='$image'  src='$rate'  /></br>
     <center>$noticia[CATEGORY]<br/>------------------<br/>$TYPE_NAME.$noticia[TYPE_VR]</center></div><br />  ";
echo " </center><div class='styleroundbottom'>
	 <img src='Template/Images/c4.gif' 
	 width='15' height='15' class='corner' 
	 style='display: none' /> </div></div>";
echo "</td>";

$i++;
if(($i%4)==0)
	echo"</tr>";

}
echo"</tr>";
echo"</table>";
echo"<br />";


////////////////////////////// End of displaying the table with records ////////////////////////

///// Variables set for advance paging///////////

//////////// End of variables for advance paging ///////////////
/////////////// Start the buttom links with Prev and next link with page numbers /////////////////

display($back,$page_name,$limit ,$nume ,$this1,$eu,$next,$selection,$selectionsd,$sk,$sd,$STYLE_CATEGORY);



?>

<?php
function display($back,$page_name,$limit ,$nume ,$this1,$eu,$next,$selection,$selectionsd,$sk,$sd,$STYLE_CATEGORY){
while (list ($key, $val) = each ($selection)) {
			if($key == 'style')		
				$selectionn =  $val;
			else	
			if($key == 'version')
				$selectionb =  $val;		
			else
			if($key == 'download')
				$selectiond =  $val;
			else
			if($key == 'view')
				$selectionv =  $val;
			else
			if($key == 'submit')
				$selectiona  =  $val;
			else
				$selectionu =  $val;

			
		
} 
while (list ($key, $val) = each ($selectionsd)) {
			if($key == 'asc')		
				$selectionas =  $val;
			else				
				$selectionds =  $val;
		
} 

			
			echo "  <br/>
		 
					 	
			<form name='formsort' method='get' style='margin:0px 0 0 0;' action='$page_name'>
<label>Sort by : </label>
			<table>
			<tr>
			<td> <select name='sk' id='sk'><option value='n' $selectionn>Style</option>
<option value='v' $selectionv>Viewed</option><option value='d' $selectiond>Downloads</option>
<option value='a' $selectiona>Submitted</option><option value='u' $selectionu>Last updated</option>
<option value='b' $selectionb>Style Version</option></select></label> <label><select name='sd' id='sd'>
<option value='a' $selectionas>Ascending</option><option value='d' $selectionds>Descending</option>
</select> 
			
		 </td><td>
	 <img src='Template/Images/Buttons/go.png' 
	 onclick='javascript:document.recordsview.submit();' 
	 class='imglink'  width='25' height='25'> 
			</td></tr></table>
			</form><br />
  <div class='contentsmaller'>Total number of styles avalaible is $nume<p>";		
//// if our variable $back is equal to 0 or more then 
//only we will display the link to move back ////////
echo "Pages : ";
if($back >=0) { 
print "<a class='pagenumber' href='$page_name?start=$back
&limit=$limit&sk=$sk&sd=$sd&template_category_id=$STYLE_CATEGORY'>
<font face='Verdana' size='2'>PREV</font></a>"; 
} 
//////////////// Let us display the page links at  center.
 //We will not display the current page as a link ///////////
//echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a class='pagenumber' 

href='$page_name?start=$i&limit=$limit&sk=$sk&sd=$sd&
template_category_id=$STYLE_CATEGORY'>
$l</a>|";
}
else { echo "<font class='currentpagenumber' >$l</font>";}        /// Current page is not displayed as link and given font color red
$l=$l+1;
}


//echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this1 < $nume) { 
print "<a class='pagenumber' href='$page_name?start=$next&limit=$limit&sk=$sk&sd=$sd&
template_category_id=$STYLE_CATEGORY'>
<font face='Verdana' size='2'>NEXT</font></a> ";} 
echo "  
			 <br/></p></div>";
}

?>
 </div>

There are two main parameters in this code which are the start and end of the queries named as $eu, $limit actually $eu is the start index and $limit the number of records to get starting from index $eu. Below, we get the $eu value :

php code

$eu = ($start - 0);

$start should be sent, if not we get a default value at :
php code

//  If you use this code with a different page 
 //( or file ) name then change this 
 $start=0;
    if(isset($_GET['start']))
$start=$_GET['start'];// To take care global variable if OFF
 
if(!($start > 0)) {// This variable is set to zero for the first page
$start = 0;
}

The results return from the database queries are affected by these two variables at :
php code

if($STYLE_CATEGORY!="")
  {
$result= GetStylesPagesbyLimitCat($order,$eu, $limit,$Type_ID,$STYLE_CATEGORY);
}
 else
 {
 $result= GetStylesPagesbyLimit($order,$eu, $limit,$Type_ID);
 }

These queries functions are defined in the following PHP file:
php code

<?php 
  
  
  
   function GetStyles()
   {
   $query=" SELECT * FROM styles  ";
   $result=mysql_query($query);
   echo mysql_error();
   
   $styles = array();
	while ($row = mysql_fetch_objects($result)) 
	{
		$styles[] = $row;
	}
    
	
	return $styles;
   }
   function GetStylesPages($eu, $limit)
   {
     $query=" SELECT * FROM styles limit $eu, $limit ";
	 $result=mysql_query($query);
	 echo mysql_error();
     return $result;
   
   }
      function GetStylesPagesbyLimit($order,$eu, $limit,$Type_ID)
   {
     $query=" SELECT * FROM styles where  ID='$Type_ID' ".$order." limit $eu, $limit  ";
	 $result=mysql_query($query);
	 echo mysql_error();
     return $result;
   
   }
       function GetStylesPagesbyLimitCat($order,$eu, $limit,$Type_ID,$STYLE_CATEGORY)
   {
     $query=" SELECT * FROM styles where  ID='$Type_ID' and category='$STYLE_CATEGORY' ".$order." limit $eu, $limit  ";
	 $result=mysql_query($query);
	 echo mysql_error();
     return $result;
   
   }
     function GetStylesPagesbyOrder($order,$Type_ID)
   {
     $query=" SELECT * FROM styles  where  ID='$Type_ID'  ".$order."";
	 $result=mysql_query($query);
	 echo mysql_error();
     return $result;
   }
      function GetStylesPagesbyOrderCat($order,$Type_ID,$STYLE_CATEGORY)
   {
     $query=" SELECT * FROM styles  where  ID='$Type_ID' and category='$STYLE_CATEGORY' ".$order."";
	 $result=mysql_query($query);
	 echo mysql_error();
     return $result;
   }
   function GetNumberOfStyles()
   {
      $query=" SELECT * FROM phpbb3_style  ";
      $result=mysql_query($query);
      echo mysql_error();
      $num=mysql_num_rows($result);
	  return $num;
   }
   
   function GetStyleById( $STYLE_ID)
   {
         $query="Select * from styles where  STYLE_ID='$STYLE_ID';";
		 $style=mysql_query($query) or die("<br/><br/> Please enter correct ID ");   
		 echo mysql_error();
		 return $style;
	
	}
	function GetStyleByName($STYLE_NAME)
	{
	      $query="Select STYLE_ID from styles where  STYLE_NAME='$STYLE_NAME';";
		   $style=mysql_query($query) or die("<br/><br/> Please enter correct style name ");  
		    echo mysql_error(); 
				 return $style;
	}
	function GetAllStyles()
	{
	   $query="Select * from styles order by STYLE_NAME desc ";
		 $allstyles=mysql_query($query) or die("<br/><br/> error in geting data ");   
		 echo mysql_error();
		 return $allstyles;
	}
		function GetAllStylesForType($ID)
	{
	   $query="Select * from styles where ID='$ID' order by STYLE_NAME desc ";
		 $allstyles=mysql_query($query) or die("<br/><br/> error in geting data ");   
		 echo mysql_error();
		 return $allstyles;
	}
	function GetStyleByNameAndTypeID($STYLE_NAME,$TYPEID)
	{
	      $query="Select STYLE_ID from styles where  STYLE_NAME='$STYLE_NAME' and ID='$TYPEID';";
		   $style=mysql_query($query) or die("<br/><br/> Please enter correct style name ");  
		    echo mysql_error(); 
				 return $style;
	}
	function GetTypeIDByName($TypeName)
	{
	   $query="Select  ID  from type where  TYPE_NAME='$TypeName'";
		 $allstyles=mysql_query($query) or die("<br/><br/> error in geting data ");   
		 echo mysql_error();
		 return $allstyles;
	}
	function GetAllTypes()
	{
	    $query="Select *  from type order by TYPE_NAME  desc ";
		$alltypes=mysql_query($query) or die("<br/><br/> error in geting  types list ");   
		 echo mysql_error();
		 return $alltypes;
	}
	
   
?>

We get the $limit value in this part :
php code

@$limit=$_GET['limit']; // Read the limit value from query string. 
  
switch($limit)
{
	case 4:
$select4="selected";
$select12="";
$select8="";
break;

case 8:
$select8="selected";
$select12="";
$select4="";
break;

default:
$select12="selected";
$select8="";
$select4="";
break;

}

If it is not sent , $select12="selected" , $select12 is related to the number of elements to show per page and we set the value of $limit if is not sent at :
php code

if(!$limit > 0 ){ // if limit value is not available then let us use a default value
$limit = 12;    // No of records to be shown per page by default.
}

Note that the selection box for the number of elements to show in page is printed at :
php code

echo "<table><tr><td>Select preview number :</td>
<td> <form name='recordsview' method='get' action=$page_name >

<table><tr><td><select name=limit>
<option value=12 $select12>12 styles</option>

<option value=8 $select8>8 styles</option>
<option value=4 $select4>4 styles</option>

</select> </td><td><img src='Template/Images/Buttons/go.png' 

onclick='javascript:document.recordsview.submit();' 

class='imglink'  width='25' height='25'></td></tr></table>";

The display function in this pagination example is called twice, one at the beginning of printing the list and one at the end. You can make many changes to this code and update it. There is a part in this article example related to sorting the elements based on attributes such as views, date, and version
php code

$order=$order." DOWNLOADS ";
$selection = array('style' => "",
                   'view'  => "",
                   'download'   => "selected",
                   'submit' => "",
                   'update'  => "",
                   'version'   => "");

The code above is not related to the pagination concept, you can just remove these parts from the example. There also some code not mentioned here such as header , footer and connection to database because it is out of topic focus. You can download the full project and run the code with all files from templates database .