Top

Follow me and receive all the latest free scripts:

By Email:

Categories
Most Popular Posts

Pagination with jQuery, Ajax, PHP and MySQL

Pagination with jQuery, Ajax, PHP and MySQL

Published March 07, 2015 by , category jQuery

jQueryPaginationAjaxPHPMySQL

Easy pagination solution with jQuery and Ajax

Introduction

In this tutorial I will show you a simple way to paginate your content using Ajax with jQuery. jQuery makes it very easy to send Ajax requests.

For the example my pagination jQuery script is tied to a MySQL database table and I use PDO for the connection (database access). If you are not sure about how to connect a database using PDO please check this tutorial. You may choose another way to connect your MySQL database, just adapt!

Live demo

Here is a demo. I chose to display 4 stories per page. I show the story id, story title and story content from the database table.

Files architecture

index.php
_connexion.php

ajax

tuto-pagination.php

jquery

jquery-1.10.1.min.js

js

tuto-pagination.js

design

Database structure sample

A simple table named tc_tuto_pagination with three fields: id, a standard auto increment field, title, a varchar field and description, a text field.

In addition to the database structure I also provide data, for the demo.

CREATE TABLE IF NOT EXISTS `tc_tuto_pagination` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

INSERT INTO `tc_tuto_pagination` (`id`, `title`, `description`) VALUES
(1, 'Story 1', 'Text story 1'),
(2, 'Story 2', 'Text story 2'),
(3, 'Story 3', 'Text story 3'),
(4, 'Story 4', 'Text story 4'),
(5, 'Story 5', 'Text story 5'),
(6, 'Story 6', 'Text story 6'),
(7, 'Story 7', 'Text story 7'),
(8, 'Story 8', 'Text story 8'),
(9, 'Story 9', 'Text story 9'),
(10, 'Story 10', 'Text story 10'),
(11, 'Story 11', 'Text story 11'),
(12, 'Story 12', 'Text story 12'),
(13, 'Story 13', 'Text story 13'),
(14, 'Story 14', 'Text story 14'),
(15, 'Story 15', 'Text story 15'),
(16, 'Story 16', 'Text story 16'),
(17, 'Story 17', 'Text story 17'),
(18, 'Story 18', 'Text story 18'),
(19, 'Story 19', 'Text story 19'),
(20, 'Story 20', 'Text story 20'),
(21, 'Story 21', 'Text story 21'),
(22, 'Story 22', 'Text story 22'),
(23, 'Story 23', 'Text story 23'),
(24, 'Story 24', 'Text story 24'),
(25, 'Story 25', 'Text story 25'),
(26, 'Story 26', 'Text story 26'),
(27, 'Story 27', 'Text story 27'),
(28, 'Story 28', 'Text story 28'),
(29, 'Story 29', 'Text story 29'),
(30, 'Story 30', 'Text story 30'),
(31, 'Story 31', 'Text story 31'),
(32, 'Story 32', 'Text story 32'),
(33, 'Story 33', 'Text story 33'),
(34, 'Story 34', 'Text story 34'),
(35, 'Story 35', 'Text story 35'),
(36, 'Story 36', 'Text story 36'),
(37, 'Story 37', 'Text story 37'),
(38, 'Story 38', 'Text story 38'),
(39, 'Story 39', 'Text story 39'),
(40, 'Story 40', 'Text story 40');

Database connection

Page _connexion.php. I use PDO for the connection. Please update with your hostname, dbname, username and password.

<?PHP
// Server connection
$dns = 'mysql:host=localhost;dbname=test_js';
$user = 'root';
$password = '';
$bdd = new PDO($dns, $user, $password);

HTML code

index.php page, including few lines of CSS for the pagination buttons style.

Lines are commented.

<?PHP
include('_connexion.php');
?>
<!DOCTYPE>
<html>
<head>
	<title>Pagination tutorial</title>
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<link rel="stylesheet" href="bootstrap/css/bootstrap.min.css" type="text/css">
	
	<style>
	#pagination div { display: inline-block; margin-right: 5px; margin-top: 5px }
	#pagination .cell a { border-radius: 3px; font-size: 11px; color: #333; padding: 8px; text-decoration:none; border: 1px solid #d3d3d3; background-color: #f8f8f8; }
	#pagination .cell a:hover { border: 1px solid #c6c6c6; background-color: #f0f0f0;  }
	#pagination .cell_active span { border-radius: 3px; font-size: 11px; color: #333; padding: 8px; border: 1px solid #c6c6c6; background-color: #e9e9e9; }
	#pagination .cell_disabled span { border-radius: 3px; font-size: 11px; color: #777777; padding: 8px; border: 1px solid #dddddd; background-color: #ffffff; }
	</style>
</head>

<body>
<div id="articleArea"></div> <!-- Where articles appear -->

<!-- Where pagination appears -->
<div id="pagination">
	<!-- Just tell the system we start with page 1 (id=1) -->
	<!-- See the .js file, we trigger a click when page is loaded -->
	<div><a href="#" id="1"></a></div>
</div>

<script type="text/javascript" src="jquery/jquery-1.10.1.min.js"></script>
<script type="text/javascript" src="js/tuto-pagination.js"></script>

</body>
</html>

PHP code

Page ajax/tuto-pagination.php called by jQuery Ajax function. Lines are commented.

<?php
include('../_connexion.php'); // Connection to database

if($_POST) {
	$page = $_POST['page']; // Current page number
	$per_page = $_POST['per_page']; // Articles per page
	if ($page != 1) $start = ($page-1) * $per_page;
	else $start=0;
	
	$select = $bdd->query('SELECT id, title, description FROM tc_tuto_pagination LIMIT '.$start.', '.$per_page.''); // Select article list from $start
	$select->setFetchMode(PDO::FETCH_OBJ);
	$numArticles = $bdd->query('SELECT count(id) FROM tc_tuto_pagination')->fetch(PDO::FETCH_NUM); // Total number of articles in the database
	
	$numPage = ceil($numArticles[0] / $per_page); // Total number of page
	
	// We build the article list
	$articleList = '';
	while( $result = $select->fetch() ) {
		$articleList .= '<div class="well well-sm">' . $result->id . '. <b>' . $result->title . '</b><p>' . $result->description . '</p></div>';
	}
	
	// We send back the total number of page and the article list
	$dataBack = array('numPage' => $numPage, 'articleList' => $articleList);
	$dataBack = json_encode($dataBack);
	
	echo $dataBack;
}
?>

JS code

Page js/tuto-pagination.js, lines are commented.

$('document').ready(function() {
	$("#pagination a").trigger('click'); // When page is loaded we trigger a click
});

$('#pagination').on('click', 'a', function(e) { // When click on a 'a' element of the pagination div
	var page = this.id; // Page number is the id of the 'a' element
	var pagination = ''; // Init pagination
	
	$('#articleArea').html('<img src="design/loader-small.gif" alt="" />'); // Display a processing icon
	var data = {page: page, per_page: 4}; // Create JSON which will be sent via Ajax
	// We set up the per_page var at 4. You may change to any number you need.
	
	$.ajax({ // jQuery Ajax
		type: 'POST',
		url: 'ajax/tuto-pagination.php', // URL to the PHP file which will insert new value in the database
		data: data, // We send the data string
		dataType: 'json', // Json format
		timeout: 3000,
		success: function(data) {
			$('#articleArea').html(data.articleList); // We update the articleArea DIV with the article list
			
			// Pagination system
			if (page == 1) pagination += '<div class="cell_disabled"><span>First</span></div><div class="cell_disabled"><span>Previous</span></div>';
			else pagination += '<div class="cell"><a href="#" id="1">First</a></div><div class="cell"><a href="#" id="' + (page - 1) + '">Previous</span></a></div>';

			for (var i=parseInt(page)-3; i<=parseInt(page)+3; i++) {
				if (i >= 1 && i <= data.numPage) {
					pagination += '<div';
					if (i == page) pagination += ' class="cell_active"><span>' + i + '</span>';
					else pagination += ' class="cell"><a href="#" id="' + i + '">' + i + '</a>';
					pagination += '</div>';
				}
			}

			if (page == data.numPage) pagination += '<div class="cell_disabled"><span>Next</span></div><div class="cell_disabled"><span>Last</span></div>';
			else pagination += '<div class="cell"><a href="#" id="' + (parseInt(page) + 1) + '">Next</a></div><div class="cell"><a href="#" id="' + data.numPage + '">Last</span></a></div>';
			
			$('#pagination').html(pagination); // We update the pagination DIV
		},
		error: function() {
		}
	});
	return false;
});

Conclusion

Congratulation, you have just learnt how to make a pagination system for your content.

You have questions? Want to share ideas? Please comment below.

Happy coding!

About Simon Laroche
Simon Laroche on Google+
Simon Laroche on Twitter
Simon Laroche on Facebook
Simon Laroche on Pinterest
Simon Laroche on LinkedIn
: I am a Coder, Designer, Webmaster and Expert SEO Consulting, I'm also a wise traveller and an avid amateur photographer. I created the website TipoCode and many others such as Landolia: a World of Photos...

If you need help about this script, please leave a comment below. I reply as much as I can depending of my time, you may also get help from others.
I also offer a paid support, if you are in the need to adapt or create a script...

Leave a comment

Comments (18 comments)

Mukesh Posted on January 21, 2017
I want to get value from querystring but isn't work with your code because i want to display data from different table. my query string is like this "http://localhost/Example/paging/sale_.php?id=1" when id=1 table "bf" use to display data from database & when id=2 table "jmd" use to display data from database. Thanks
Jav Posted on December 01, 2016
Works like a charm. Thanks.
Hiren Posted on August 17, 2016
Thanks... this article very helpful...
chintan raninga Posted on May 28, 2016
nice and simple logic thank you for guide
kanoune
kanoune Posted on May 14, 2016
Hello Simon,

now i tried to change the structure of the code found in tut-pagination.php file and wrap it in a function.
The problem is that, how can i make it work ? In other words what changes should i make on the tut-pagination.js file ?

thx in advance.
Kanoune
Simon Laroche
Simon Laroche Posted on May 11, 2016
@kanoune: great! Glad it works.
kanoune
kanoune Posted on May 11, 2016
Hi Simon,
the file jquery has been the problem. I added it, and voila it works like a charm.

Thank you very much.

best wishes from germany
Simon Laroche
Simon Laroche Posted on May 11, 2016
@kanoune: As I said in index.php file, at the end you have the link to jquery.
You need to dl jquery framework
kanoune
kanoune Posted on May 11, 2016
Hi Simon,

thank you very much for your replies. I tried again to copy and past every thing inclusive the database and table, but still i get only a blank page on my browser!
Should i include some Ajax packages or something else ?
I have no knowledge of Javascript, jquery or ajax, and it is strange that could not run this pagination project.

have a nice day
Kanoune
Simon Laroche
Simon Laroche Posted on May 11, 2016
@kanoune : no, the index does not contain only comments.
But

<!-- Where pagination appears -->
<div id="pagination">
<div><a href="#" id="1"></a></div>
</div>

Which is where the pagination will show (see the js file)
kanoune
kanoune Posted on May 11, 2016
Hi Simon,
i tried but it has not work, because the index.php file contains no data, just comments.
Could you help please :)

thx
Kanoune
Simon Laroche
Simon Laroche Posted on May 11, 2016
@kanoune : No, not available in download, but if you copy paste it should be the same...
kanoune
kanoune Posted on May 11, 2016
Hello,

is the script for pagination not available for downloading?
i have some problems running this script via copy past

thx.
Kanoune
Simon Laroche
Simon Laroche Posted on March 12, 2016
@mahdifox: well I can't really say! You need to make step by step tests, check the DB connection, the jQuery call etc...
mahdifox
mahdifox Posted on March 12, 2016
hey there
i copied your "index.php" codes into my "index.php" and other files as well .
but whenever i open the browser and open files from localhost (your project files) it seems like a blank page just as white as honest. :-( will you please help ?
many thanks in advance.
saw Posted on February 15, 2016
Nice and simple implementation
Thanks
Eugene Posted on September 26, 2015
Nice guide =)
Fedde Anker Posted on July 24, 2015
Thank you very much.