PHP Pagination

Pagination means to show the information into multiple pages instead of one and when u say PHP pagination, it means that you want to show the records in multiple pages instead of showing all records in one page.

So, what type of output you are looking for.

Next and Previous PHP Pagination

Next - Previous PHP Pagination
Next and Previous Pagination

Google-style PHP Pagination

Google Style PHP Pagination
Google-style

JSON

[
	{
		
                employee: "Sunil",
		salary: "10000"
	},
	{
		employee: "Anil",
		salary: "10000"
	}
	
]

The logic of getting data is the same in all the above format. let’s take an example you have a table with 100 of records and you want to display 10 records per page. The three things you must find

  1. The total number of records in the table.
  2. How many records you want to fetch per page
  3. And the page number.
# get total number of rows
 
$sql = "SELECT COUNT(*) FROM employee";   
$result = mysqli_query($con, $sql);   
$row = mysqli_fetch_row($result);   
$total_records = $row[0];   

# set the limit
$limit = 10;

# get the page number
$page = isset($_GET['page']) ? $_GET['page'] : 1;
 

Next, find the number of pages and the offset.

$num_of_pages = ceil($total_records/$limit)
$offset = ($page - 1) * $limit;

Now, you can run the query to fetch the records using the MySQL LIMIT clause by giving two arguments.

$sql = "select * from employee limit $offset, $limit";

Next – Previous Pagination Example

$sql = "SELECT COUNT(*) FROM employee";   
$result = mysqli_query($con, $sql);   
$row = mysqli_fetch_row($result);   
$total_records = $row[0];   

# set the limit
$limit = 10;

# get the page number
$page = isset($_GET['page']) ? $_GET['page'] : 1;

$num_of_pages = ceil($total_records/$limit)
$offset = ($page - 1) * $limit;

$sql = "select * from employee limit $offset, $limit";
$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))
{
     // show your data here....
}

$first = "<li><a href='index.php'>First</li>";
# next page
if($page < $num_of_pages)
{
  $next="<li><a href='index.php?next=".
  $next.=$page + 1;
  $next.=".htm'>Next</a></li>";
}
else
{
 $next="<li class='disabled'><a href='' onclick=':alert(\"You are on last  page\");return false'>Next</a></li>";
}

# previous page
if($page > 1)
{
  $prev="<li><a href='index.php?page=";
  $prev.= $page - 1;
  $prev.=".htm'>Previous</a></li>";
}
else
{
 $prev= "<li class='disabled'><a href='' onclick='alert(\"You are on first page\");return false'>Previous</a></li>";
}
		
$last="<li><a href='index.php?page=$num_of_pages'>Last</a></li>";

$pagination = $first.$next.$last.$prev;

Google-style Pagination Example

$sql = "SELECT COUNT(*) FROM employee";   
$result = mysqli_query($con, $sql);   
$row = mysqli_fetch_row($result);   
$total_records = $row[0];   

# set the limit
$limit = 10;

# get the page number
$page = isset($_GET['page']) ? $_GET['page'] : 1;

$num_of_pages = ceil($total_records/$limit)
$offset = ($page - 1) * $limit;

$sql = "select * from employee limit $offset, $limit";
$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))
{
     // show your data here....
}

$pagelength = 3; // number of pages link				
if($num_of_pages <= $pagelength)
{
  $start = 1; 
  $end = $num_of_pages;
}
else if ($num_ofpages > $pagelength)
{
   $start = $page - $pagelength;
   if($start <= 0)
	$start = 2;
   $end = $page+$pagelength;
   if($end >=$num_ofpages)
       $end = $num_of_pages;
}

$link="";		
while($start <= $end)
{
  if($start == $page)
     $link .="<li><a href=''>$start</a></li>";
  else
  {
     $link.="<li><a href='index.php?page=";
     $link.=$start ;
     $link.="'>$start</a></li>";
  }
  $start = $start+1;
}

$next="";
$first="<li><a href='index.php'>First</a></li>"; 
$last="<li><a href='index.php?page=$num_ofpages'>Last</a></li>"; 	
if($page < $num_ofpages)
{
   $next = $page+1;
   $next ="<li><a href='index.php?url=$next'>Next</a></li>";
}
if($num_of_pages != 1)
  $link = "<li'>".
  "<ul>$first $link $last $next</ul>".
  "</li>";
else {
	$link="";
  }

In Google-style pagination, you can see that I have used the variable “pagelength” which tell the number of next and previous pages link will visible. let say if the user clicks on page number 6, it will show you page number 3, 4, 5 and 7, 8, 9 because the “pagelength” is set to 3.

And for JSON output, you can use the json_decode function.

Leave a Comment

Your email address will not be published. Required fields are marked *