In this tutorial “Pagination with jQuery Ajax PHP and MySQL”, we are going to learn how to implement pagination in any project with PHP and MySQL. In any web project, pagination functionality is an essential part where huge numbers of records are listed from the database. And now it also becomes a common feature in the web project to display a large number of data on a single page. For that Ajax, pagination is the preferable way to data because it improves the user interface of the web page. its allows displaying records on the same page without reloading page. We’ve developed the simple and powerful script to create pagination with jQuery, Ajax, PHP, and MySQL.
Table of Contents
Creating Our Database
Firstly, we have to create a database to store the data, for creating database follow these steps:
- Open phpMyAdmin
- Select database icon and Create a new database with the name after that
- Create the table with the table name
- And then insert the details Or
- After creating a database name, click the SQL and paste the following code.
Create Table
CREATE TABLE `pagination` ( `id` int(11) NOT NULL, `unique_id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `phone_no` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Connection.php
This is a connection page, mysqli_connect() function opens connection to the MySQL server .
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pagination";
$limit = 10;
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>
Main Screen
This page will appear whenever a user opens the site, this page contains simple web interface that displays the data records with pagination, where the user has to just click to the pagination number to get another page. This page contains all record of data which is available in MySQL database.
Create a PHP file named “index.php” and paste the following code inside of it.
Index.php
<?php
include('connection.php');
$countSql = "SELECT COUNT(id) FROM pagination";
$result = mysqli_query($conn, $countSql);
$row = mysqli_fetch_row($result);
$total_records = $row[0];
$tot_pages = ceil($total_records / $limit);
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $limit;
$p = "SELECT * FROM pagination ORDER BY id ASC LIMIT $start_from, $limit";
$rs_result = mysqli_query($conn, $p);
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css">
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="dist/simplePagination.css" />
<script src="javascript/jquery.simplePagination.js"></script>
<title>Techjunkgigs</title>
<script type="text/javascript">
$(document).ready(function(){
$('.pagination').pagination({
items: <?php echo $total_records;?>,
itemsOnPage: <?php echo $limit;?>,
cssStyle: 'dark-theme',
currentPage : 1,
onPageClick : function(pageNumber) {
jQuery("#target-content").html('loading......');
jQuery("#target-content").load("logic.php?page=" + pageNumber);
}
});
});
</script>
</head>
<body>
<div class="container" style="padding-top:20px;">
<div class="container" style="background:#e6e7e8">
<h1><b>Techjunkgigs</b></h1>
<h3>Pagination with jQuery, Ajax, PHP and MySQL</h3></br>
</div>
<br>
<div class="col-sm-12">
<table class="table table-bordered table-striped" >
<thead style="background:#262626; color:white;">
<tr>
<th>ID</th>
<th>Unique Id</th>
<th>Name</th>
<th>Phone No</th>
</tr>
</thead>
<tbody id="target-content">
<?php
while ($row = mysqli_fetch_assoc($rs_result)) {
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["unique_id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["phone_no"]; ?></td>
</tr>
<?php
};
?>
</tbody>
</table>
</div>
<nav><ul class="pagination">
<?php if(!empty($tot_pages)):for($i=1; $i<=$tot_pages; $i++):
if($i == 1):?>
<li class='active' id="<?php echo $i;?>"><a href='logic.php?page=<?php echo $i;?>'><?php echo $i;?></a></li>
<?php else:?>
<li id="<?php echo $i;?>"><a href='logic.php?page=<?php echo $i;?>'><?php echo $i;?></a></li>
<?php endif;?>
<?php endfor;endif;?>
</ul></nav>
</div>
</body>
</html>
We just need to include jquery.simplePagination.js and simplePagination.css on our page.
Click here to download jquery.simplePagination.js and simplePagination.css files
<link rel="stylesheet" href="javascript/simplePagination.css" /> <script src="javascript/jquery.simplePagination.js"></script>
Then we also need to include the bootstrap links
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css"> <script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.js"> </script> <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"> </script>
This is the call function on our pagination placeholder
$(document).ready(function(){
$('.pagination').pagination({
items: <?php echo $total_records;?>,
itemsOnPage: <?php echo $limit;?>,
cssStyle: 'dark-theme',
currentPage : 1,
onPageClick : function(pageNumber) {
jQuery("#target-content").html('loading......');
jQuery("#target-content").load("logic.php?page=" + pageNumber);
}
});
});
When any user clicks on the pagination button then the index page will redirect the page to the logic.php page. And retrieve the result from the table and displayed it to the user without refreshing the page.
Logic Page
And this page contains the source code of “logic.php” file which displays the data from the MySQL database.
<?php
include('connection.php');
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $limit;
$sql = "SELECT * FROM pagination ORDER BY id ASC LIMIT $start_from, $limit";
$rs_result = mysqli_query($conn, $sql);
?>
<?php
while ($row = mysqli_fetch_assoc($rs_result)) {
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["unique_id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["phone_no"]; ?></td>
</tr>
<?php
};
?>
Demo Image
Video Demo
You can also check
Ajax Live Data Search using PHP and MySQL
Insert Data into MySQL database with PHP and AJAX without refreshing page
I hope this article helped you to know “Pagination with jQuery Ajax PHP and MySQL”. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. And If you have any query then please let us know by using the comment form.
Amazing tutorial on pagination on MySQL it will help me a lot.
Thanks for Sharing. Keep Posting.
its pleasure for me Raj that you like my post.
Very helpful guide. Helped me a lot.