Add Pagination To MySQL Database Result In PHP


Pagination makes the results tidy and simple. When there are many results, pagination helps sorting them out and adding each results to specific pages. In PHP, it’s real simple. You can use this tutorial to add a pagination to your database results in PHP.

We will be using PDO for database connection and queries. The Database table named “users” is like this :

id First_Name Last_Name
1 Subin Siby
2 Peter Charles
3 Thomas Chacko

It contains more and more data. It’s just a sample. Let’s move on.

Variables

We will set the result limit in a page using the $limit variable :

$limit = 10;

The $p variable have the page number got from $_GET :

$p=$_GET['p']=="" ? 1:$_GET['p'];

Note that the above variable will have the value 1 even if the $_GET[‘p’] is empty or not defined.

The $start variable calculates the starting number we’re going to add in the SQL queries LIMIT clause :

$start=($p-1)*$limit;

The page number is subtracted by 1 and is multiplied by the $limit number which will return the start number used in the LIMIT caluse.

Get Data

We will first get the data from database into the $data variable :

$sql=$dbh->prepare("SELECT * FROM users ORDER BY id LIMIT :start, :limit");
$sql->bindValue(':limit', $limit, PDO::PARAM_INT);
$sql->bindValue(':start', $start, PDO::PARAM_INT);
$sql->execute();
$data=$sql->fetch();

You can output the data you got from SQL query as you like :

if($sql->rowCount()!=0){
 while($r=$data){
  echo $r['First_Name']." ".$r['Last_Name'];
 }
}else{
 echo "No Users Found";
}

We add the row checking, because if the page number mentioned is not correct SQL query will return nothing.

Pagination

We will display the pages now and CSS styling to it. The following PHP code will get the actual Total row numbers as $count variable :

$count=$db->prepare("SELECT COUNT(id) FROM users ORDER BY id");
$count->execute();
$count=$count->fetchColumn();

Now, we will generate the pages list in a horizontal scrollable element :

$countP=(ceil($count/$limit)) + 1;
$tW=($countP*50) + $countP;
echo"<center style='overflow-x:auto;margin-top:10px;padding-bottom:10px;'>";
 echo"<div style='width:".$tW."px'>";
 for($i=1;$i<$countP;$i++){
  $isC=$i==$_GET['p'] ? "b-green":"";
  echo "<a href='?p=$i'><button class='pgbutton $isC'>$i</button></a>";
 }
 echo"</div>";
echo"</center>";

The styling of the page buttons is :

<style>
.pgbutton{
 width:45px;
 margin:0px 5px;
}
</style>

If you want to display the no of results, you can use :

echo"$count Results Found.";

You can make changes to the code as you like. When you change the SQL queries, be sure that you have changed the SQL queries of both occurences, one with LIMIT clause and one without.