Simple And Best Pagination With PHP, MySQL

In this tutorial we will show you how to create and implement PHP pagination in three simple steps, Pagination is one of the best way to show large amount of data very easily and effectively.

You can easily get and display thousands of results from database and show it to the visitor one by one.

Pagination technique is used to fetch small amount data stored in a database inspite of fetching all the data at once from the database by simply querring the databse again and again using some methods.

This is a very useful technique it decreases the webpage loading time by only displaying some results in front of the user.

Steps For Creating PHP Pagination With MySQL

1. Connect To Database.
2. Query The MySQL Database.
3. Get The Results.

Step 1. Connect To Database

In this step we have to create a sample database and table for our pagination and connect to database table so that we can implement pagination technique.

You can connect to any existing database from which you want to get the data. In this step we use sample database named "demo".

<?php
$host = 'localhost';
$user = 'root';
$pass = '';

mysql_connect($host, $user, $pass);

mysql_select_db('demo');
?>

Step 2. Query The MySQL Database.

<?php
$offset = 0;
$page_result = 5; 
	
if($_GET['pageno'])
{
 $page_value = $_GET['pageno'];
 if($page_value > 1)
 {	
  $offset = ($page_value - 1) * $page_result;
 }
}

$select_results = " select * from student_info limit $offset, $page_result ";
?>

In second step we use three php variables $offset, $page_result, $page_value. $offset = 0 because we want to select the data from the first that is 0th position from the table, $page_result = 5 because we want to display 5 results at a time.

$page_value = $_GET['pageno'] it is the value of the page when the user wants to change the page and click the pagination page number.

Then if the $page_value is greater than 1 we change the $offset value to the value of this code ($page_value - 1) * $page_result.

Then we query the database we select details from student_info table and limit the results returned by the query for more details of limit clause visit our MySQL clause tutorial.

You may also like load more results from database using ajax.

Step 3. Get The Results.

<?php

$result = mysql_query( $select_results );

while($row = mysql_fetch_array($result))
{
 echo $row[' student_name '];
 echo $row[' student_rollno '];
 echo $row[' student_course '];
}

$pagecount = 50; // Total number of rows
$num = $pagecount / $page_result ;

if($_GET['pageno'] > 1)
{
 echo "<a href = 'samepage.php?pageno = ".($_GET['pageno'] - 1)." '> Prev </a>";
}
for($i = 1 ; $i <= $num ; $i++)
{
 echo "<a href = "samepage.php?pageno = ". $i ." >". $i ."</a>";
}
if($num! = 1)
{
 echo "<a href = 'samepage.php?pageno = ".($_GET['pageno'] + 1)." '> Next </a>";
}

?>

In this step we get the results from student_info table, in student_info table we have three columns student_name, student_rollno, student_course.

Then, we use $pagecount = 50 because there are 50 rows in student_info table. Then, we use $num variable and set the value returned by $pagecount / $page_result.

The first if statement display the Prev page link if the pageno is greater than 1 and then we display all the pagination links using for loop upto the $num value.

The second if statement display the Next page link if the pageno is not equal to 1. Remember always do proper form validation before and after submitting the form.

That's all, this is how to create and implete the basic PHP Pagination with MySQL. You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.

And if you like this tutorials please share it with your friends via Email or Social Media.

I hope this tutorial on php pagination helps you and the steps and method mentioned above are easy to follow and implement.