Ajax pagination with php and mysql

by  
Share:
Ajax pagination with php and mysql

Pagination is a part of the parcel in every day development. We always need pagination when viewing tons of data. I received a request from one of my blog readers for providing him the pagination script. So I thought why not share it with all. In this tutorial you will learn how to implement ajax pagination with php and mysql. If you want just a normal pagination i.e. without ajax you can do that too.

View Demo

What is pagination?

Pagination is the technique to divide the content into multiple pages. Suppose you have 500 mails in your inbox, loading all at once is not only time consuming but also a foolish thing as you want to see the latest mail first and that also with a limit like last 10 mails or last 20 mails. This is where pagination comes into play.

Steps to integrate pagination?

There are various method to display pagination. I am going to show the basic one, so after learning this you can then try your hands on some more tricks to display pagination. Follow these steps to create a pagination.

Create a table in your mysql database and insert some dummy rows.

CREATE TABLE IF NOT EXISTS `tbl_pagination` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_pagination` (`id`, `name`, `age`) VALUES
(1, 'name1', 1),
(2, 'demo2', 7),
(3, 'demo3', 8),
(4, 'demo4', 9),
(5, 'demo5', 10),
(6, 'demo6', 11),
(7, 'demo7', 12),
(8, 'demo8', 13),
(9, 'demo9', 14),
(10, 'demo10', 15),
(11, 'demo11', 16);

Create a file configure.php that will have basic database connection script.
<?php

// display all error except deprecated and notice  
error_reporting( E_ALL & ~E_DEPRECATED & ~E_NOTICE );
// turn on output buffering 
ob_start();

define('DB_DRIVER', 'mysql');
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "sampletest");


// basic options for PDO 
$dboptions = array(
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

//connect with the server
try {
    $DB = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD, $dboptions);
} catch (Exception $ex) {
    echo($ex->getMessage());
    die;
}

?>

Create a file getrecords.php that will have server side code, it will be called via ajax from the index page (index.php). Initialize your page number and records limit to show per page.
<?php
// Very important to set the page number first.
if (!(isset($_GET['pagenum']))) { 
  $pagenum = 1; 
} else {
  $pagenum = intval($_GET['pagenum']); 		
}

//Number of results displayed per page 	by default its 10.
$page_limit =  ($_GET["show"] <> "" && is_numeric($_GET["show"]) ) ? intval($_GET["show"]) : 10;

?>

Calculate the total number of records in the mysql table.
<?php
// Get the total number of rows in the table
$sql = "SELECT count(*) as count FROM tbl_pagination  WHERE 1" ;
try {
    $stmt = $DB->prepare($sql);
    $stmt->execute();
    $tresults = $stmt->fetchAll();
} catch (Exception $ex) {
    echo($ex->getMessage());
}

$cnt = $tresults[0]["count"];
?>

Calculate the last page based on total number of records in table and rows per page.
<?php
//Calculate the last page based on total number of rows and rows per page. 
$last = ceil($cnt/$page_limit); 
?>

Calculate the lower limit i.e the starting point of each page based on the current page.
<?php
$lower_limit = ($pagenum - 1) * $page_limit;
?>

Finally make a database call to fetch table records based on lower limit and page limit.
<?php
$sql2 = " SELECT * FROM tbl_pagination WHERE 1 limit ". ($lower_limit)." ,  ". ($page_limit). " ";
try {
    $stmt = $DB->prepare($sql2);
    $stmt->execute();
    $results = $stmt->fetchAll();
} catch (Exception $ex) {
    echo($ex->getMessage());
}
?>

Displaying pagination links.
<?php
for($i=1; $i<=$last; $i++) {
  if ($i == $pagenum ) {
?>
  <a href="#" class="selected" ><?php echo $i ?></a>
<?php
} else {  
?>
  <a href="index.php?pagenum=<?php echo $i; ?>" class="links"  onclick="displayRecords('<?php echo $page_limit;  ?>', '<?php echo $i; ?>');" ><?php echo $i ?></a>
<?php 
  }
?>

Configuring and requesting ajax pagination

Since you are done with the server side code,  all you have to do is make an ajax call. Just send the parameters to the php page via ajax call and display the result.

Create a file index.php. It will be the landing page and the ajax pagination will be called from this page. Only a part of page will be updated. Create a div container with unique id. I have set results as id for displaying records and for displaying a loader image used loader class.

<div id="results"></div>
<div class="loader"></div>

Create a JavaScript function that will request an ajax call to php page. Also send the page number and records limit as the parameter.
function displayRecords(numRecords, pageNum ) {
	$.ajax({
		type: "GET",
		url: "getrecords.php",
		data: "show="+numRecords+"&pagenum="+pageNum,
		cache: false,
		beforeSend: function () { 
            $('.loader').html('<img src="loader.gif" alt="" width="24" height="24" >');
		},
		success: function(html) {    
            $("#results").html( html );
            $('.loader').html('');
		}
	});
}

On page load call the function displayRecords() to display first set of records . I have set the default value of page number as 1 and records limit as 10.
<script type="text/javascript">
$( document ).ready(function() {
  displayRecords(10, 1);
});  
</script>

View Demo
Download

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

sandilya on

can you plz send the ajax pagination in a winrar file to my mail vssandilya@gmail.com.

Reply
Shahrukh Khan on

Sent to you.

Reply
mohammad yaqub farooqui on

Good Day Sir,

My self Mohammad Yaqub and i need your some help. Please help me sir.
I need Login with facebook source code in core php. Can You send me ?

Thanks

Reply
stanley on

can you plz send the ajax pagination to my mail id?

Reply
Shahrukh Khan on

done, please check your email.

Reply
vishnu on

can you plz send the ajax pagination to my mail id?

Reply
Shahrukh Khan on

its a very easy task. you can go to the phpmyadmin and make a column for that.

Reply
Sacchidanand on

can you send the ajax pagination to my mail id(tiwarikunal10@gmail.com).

Reply
Shahrukh Khan on

its already available for download at the end of the tutorial.

Reply
vinod on

hi shahrukh
good pagination tutorial from you.
can you make pages shorten that are coming with increase of rows.
the pages number are more if rows are more.
plz make it short instead of displaying all the page numbers like
first,prevoius,1,2,3….7,8,9,next,last

Shahrukh Khan on

You can check Digg style pagination.

vinod on

thank u,
how to add digg style pagination to your script .
can you help in this regard.
i am getting errors while trying. it

bnm developer on

Can you please send added digg style pagination to your script .

Shahrukh Khan on

i will try for sure, but you can always checkout other site like stackoverflow.

Shahrukh Khan on

code is already available in this site

Bharathiq on

can you send the ajax pagination to my mail id(bharathimuni8@gmail.com).

Reply
Bharathiq on

Can you send me ajax pagination using ajax, codeingiter and mysql

Reply
Shahrukh Khan on

I dont work in codeignitor

Shahrukh Khan on

Please click on the social login at the end of this article it will give you a download link

Reply
Andre on

This is very good but what about if it is table records and not pages like you did it if i understand it correctly

Reply
Shahrukh Khan on

sorry, cant get you? please explain

Reply
Andre on

Like this is for pages.. and I want to do it with tabels like display 10 records from the table and then wen you click next show the next 10 etc … Sorry I suck at explain

Andre on

okay thank you so much

Hardik Somaiya on

Just awesome. This article was a lifesaver. Keep posting such good content 🙂

Also is there any advance article/code for this thing?

Reply
salih on

hi bro nice scripts

set records in descending

Reply
Ramjilal manjhi on

please send me complete php project with pagination on my gmail manjhi043@gmail.com.

Reply
Shahrukh Khan on

which project?

Reply
Yash Kumar Verma on

Kindly upload the output of the program as well sir

Reply
Shahrukh Khan on

kindly click on the view demo button to see the output.

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.