This tutorial will show you how to easily load paginated data from a MySQL database with jQuery, Ajax and PHP. As an example we will load addresses from a user address table.
This is what we want to code (click the page numbers):
Excited? Let’s dive into it…
First, let’s create our folder structure. In our root directory we will have the following folders:
assets
css
js
system
The folder system will contain our PHP files.
Now let’s continue with the markup of our page by creating our index.php in our root directory.
In the head section we will include our stylesheet, in the body section just before the closing body tag the latest version of the JavaScript framework jQuery and our custom JS file. As an container for the addresses that we will later load from our database we will create a div element which gets id="results".
HTML
<!DOCTYPE html>
<html>
<head>
<title>Tutorial: Pagination with jQuery, Ajax, PHP and MySQL</title>
<link rel="stylesheet" href="assets/css/styles.css">
</head>
<body>
<table>
<thead>
<th>First name</th>
<th>Last name</th>
<th>Street</th>
<th>City</th>
</thead>
<tbody id="addresses">
<!-- addresses will later be loaded inside here -->
</tbody>
</table>
<div id="pagination">
<!-- page numbers will be loaded inside here -->
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>window.jQuery || document.write('<script src="assets/js/vendor/jquery-1.11.2.min.js"><\/script>')</script>
<script type="text/javascript" src="assets/js/custom-script.js"></script>
</body>
</html>
For a nice look of our page numbers later, we will add the following pagination styling to our stylesheet:
CSS
body {
margin: 30px auto;
width: 600px;
}
table {
width: 550px;
text-align: left;
}
tr {
line-height: 2;
}
.pagination {
width: 225px;
padding: 15px 10px;
border: 1px dotted grey;
}
.pagination li {
display: inline;
padding: 6px 10px 6px 10px;
margin: 0 -1px 0 0;
font-weight: bold;
}
.pagination li a {
text-decoration: none;
color: #333;
}
.pagination li.active {
background: #ccc;
color: #333;
}
In our system folder we will now create our database file:
db.php
<?php
// credentials
$dbhost = 'localhost';
$dbname = 'name-of-database';
$dbuser = 'name-of-database-user';
$dbpass = 'password';
// create a new database mysqli object
$db = new mysqli();
// apply methods to this objects for connecting and setting up the charset
$db->connect($dbhost, $dbuser, $dbpass, $dbname);
$db->set_charset("utf8");
// check connection
if ($db->connect_errno) {
printf("Connect failed: %s\n", $db->connect_error);
exit();
}
Time to create our address table. Log into your phpMyAdmin and execute the following SQL query which will create the addresses table with 20 entries:
CREATE TABLE addresses ( ID int NOT NULL AUTO_INCREMENT, first_name varchar(255) NOT NULL, last_name varchar(255), street varchar(255), city varchar(255), PRIMARY KEY (ID) ) INSERT INTO `addresses` (`ID`, `first_name`, `last_name`, `street`, `city`) VALUES (NULL, 'Paul', 'Weber', '3800 Southern Street', 'New York'), (NULL, 'Michael', 'Adams', '3076 Argonne Street', 'Newark'), (NULL, 'Beverly', 'Moreno', '1478 Turkey Pen Road', 'Brooklyn'), (NULL, 'Pam', 'Burns', '4376 Marion Street', 'Columbia'), (NULL, 'Jack', 'Wallin', '927 Broadway Street', 'Charleston'), (NULL, 'Carl', 'Estrada', '762 Meadow View Drive', 'Windsor'), (NULL, 'Lawrence', 'Thompson', '511 Sharon Lane', 'Garwood'), (NULL, 'John', 'Sumter', '1898 Pheasant Ridge Road', 'Bensalem'), (NULL, 'Paul', 'Hoye', '44 Hood Avenue', 'San Diego'), (NULL, 'Janet', 'Diaz', '4090 Hardman Road', 'Winooski'), (NULL, 'Stanley', 'Walters', '1669 McVaney Road', 'Asheville'), (NULL, 'Ashley', 'Voyles', '4401 Calvin Street', 'Baltimore'), (NULL, 'Amy', 'Rodriquez', '3063 Daylene Drive', 'Ann Arbor'), (NULL, 'Jackie', 'Alexander', '4271 Woodlawn Drive', 'Waukesha'), (NULL, 'Nancy', 'Luttrell', '50 Bubby Drive', 'Lockhart'), (NULL, 'Larry', 'Wellington', '3442 Benson Street', 'Wausau'), (NULL, 'Margaret', 'Kim', '4082 Mudlick Road', 'Spokane'), (NULL, 'Robert', 'Lancaster', '4808 Marshville Road', 'Pearl River'), (NULL, 'Judy', 'Lamm', '2420 Wilmar Farm Road', 'Monrovia'), (NULL, 'Richard', 'Frank', '4105 Morgan Street', 'Pensacola')
After having created the table, we can proceed with building our JS and PHP files with which we will load the addresses from the database.
In the following JS file we will create the functions for getting the results from the PHP file and the pagination functionality.
custom-script.js
$(document).ready(function () {
// general setup
var $divAddresses = $('#addresses');
var $divPagination = $('#pagination');
function getData(query) {
return $.ajax({
type: "POST",
url: "system/get-addresses.php",
data: query,
dataType: 'json',
cache: false
});
}
// when page loads, load all data
var fireAjaxLoadData = function (query) {
getData(query).done(function (html) {
$divAddresses.html(html.addresses);
$divPagination.html(html.pagination);
}).fail(function (jqxhr, textStatus, error) {
var err = textStatus + ", " + error;
$divAddresses.html("Error! Request Failed: " + err);
});
};
fireAjaxLoadData();
// when user clicks on pagination links
$divPagination.on("click", ".pagination a", function (e) {
e.preventDefault();
// get page number from link and pass it as query
var page = $(this).attr("data-page");
var query = {};
query.page = page;
fireAjaxLoadData(query);
});
});
Now we get to the PHP files that we will put into the system folder.
The function getData(), which we just set up, retrieves the addresses from the following PHP file:
get-addresses.php
<?php
if (isset($_POST)) {
require_once 'db.php';
require_once 'functions.php';
// how many items per page?
$itemsPerPage = 5;
// get values from query
if (isset($_POST["page"])) {
$pageNumber = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
if (!is_numeric($pageNumber)) { die('Invalid page number!'); }
} else {
$pageNumber = 1;
}
//get total number of records from database
$results = $db->query("SELECT COUNT(*) FROM addresses");
$getTotalRows = $results->fetch_row();
$totalPages = ceil($getTotalRows[0]/$itemsPerPage);
//fetch position of record
$pagePosition = (($pageNumber-1) * $itemsPerPage);
$query = "SELECT first_name, last_name, street, city
FROM addresses
ORDER BY first_name DESC
LIMIT $pagePosition, $itemsPerPage";
$results = $db->query($query);
// get HTML of addresses
$htmlAddressesAddresses = '';
if (mysqli_num_rows($results) > 0) {
while ($result = $results->fetch_assoc()) {
$htmlAddresses .=
'<tr>' .
'<td>' . $result['first_name'] . '</td>' .
'<td>' . $result['last_name'] . '</td>' .
'<td>' . $result['street'] . '</td>' .
'<td>' . $result['city'] . '</td>' .
'</tr>';
}
} else {
$htmlAddresses = 'There is no data available.';
}
// get HTML of pagination
$htmlPagination = getPagination($pageNumber, $totalPages);
// return HTML as a JSON object
$response = array(
'addresses' => $htmlAddresses,
'pagination' => $htmlPagination
);
echo json_encode($response);
}
For a better overview we will put the function for creating the pagination box into a separate functions file:
functions.php
<?php
function getPagination($currentPage, $totalPages) {
$pagination = '';
if ($totalPages > 0 && $totalPages != 1 && $currentPage <= $totalPages) {
$pagination .= '<ul class="pagination">';
$rightLinks = $currentPage + 3;
$previous = $currentPage - 1;
$next = $currentPage + 1;
$firstLink = true;
if ($currentPage > 1) {
$previousLink = ($previous == 0) ? 1 : $previous;
$pagination .= '<li class="first"><a href="#" data-page="1" title="First">«</a></li>';
$pagination .= '<li><a href="#" data-page="' . $previousLink . '" title="Previous"><</a></li>';
for ($i = ($currentPage - 2); $i < $currentPage; $i++) {
if ($i > 0) {
$pagination .= '<li><a href="#" data-page="' . $i . '" title="Page' . $i . '">' . $i . '</a></li>';
}
}
$firstLink = false;
}
if ($firstLink) {
$pagination .= '<li class="first active">' . $currentPage . '</li>';
} elseif ($currentPage == $totalPages) {
$pagination .= '<li class="last active">' . $currentPage . '</li>';
} else {
$pagination .= '<li class="active">' . $currentPage . '</li>';
}
for ($i = $currentPage + 1; $i < $rightLinks; $i++) {
if ($i <= $totalPages) {
$pagination .= '<li><a href="#" data-page="' . $i . '" title="Page ' . $i . '">' . $i . '</a></li>';
}
}
if ($currentPage < $totalPages) {
$nextLink = ($next > $totalPages) ? $totalPages : $next;
$pagination .= '<li><a href="#" data-page="' . $nextLink . '" title="Next">></a></li>';
$pagination .= '<li class="last"><a href="#" data-page="' . $totalPages . '" title="Last">»</a></li>';
}
$pagination .= '</ul>';
}
return $pagination;
}
Done!
I hope you liked this little tutorial on how to load data from a MySQL table with jQuery, Ajax and PHP and add pagination. Feel free to leave a comment and download the files.