Simple And Best Pagination With PHP, MySQL

Pagination is one of the best ways to show a large amount of data very easily and effectively. You can easily get and display thousands of results from the database and show it to the visitor one by one.

Pagination technique is used to fetch small amount data stored in a database in spite of fetching all the data at once from the database by simply querying the database, 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. In this tutorial, we will show you how to create and implement PHP pagination in three simple steps.

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 a 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 the 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 the 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 a student_info table, in the 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 displays the Prev page link if the page is greater than 1 and then we display all the pagination links using for loop up to the $num value.

The second if statement displays the Next page link if the page 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 complete 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 tutorial please share it with your friends via Email or Social Media.



Leave a Reply