PHP mysql jquery ajax pagination. Through this tutorial, you will learn how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.
Sometimes, your MySQL database contains thousands of records; And you are showing a list of them from MySQL database in the PHP app and you want to see only a few records and data on one page. So for this, you can show the data by using Pagination in Php Mysql with Jquery Ajax.
Pagination in PHP and MySQL with Ajax and jQuery
Follow the below steps and implement advanced jQuery ajax pagination + bootstrap with PHP and MySQL;
- Step 1 – Create Database And Table
- Step 2 – Create a Database Connection File
- Step 3 – Download simple-bootstrap-paginator Plugin
- Step 4 – Create List File
- Step 5 – Create Get Data PHP File
Step 1 – Create Database And Table
First of all, navigate to your phpmyadmin panel and create database and table using the following sql queries:
CREATE DATABASE my_db;
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`skills` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Then insert few records into users table to display records with pagination; so use the following SQL query for that:
INSERT INTO `users` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES
(1, 'John', 'C', 'Newyork', 'Male', 'Software Engineer', 25),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 20),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30),
(4, 'Meera', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 38),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28),
(7, 'Kelvin', 'MySQL', 'Paris', 'Male', 'Web Developer', 23),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 20),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 35),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 32);
Step 2 – Create a Database Connection File
In this step, you will create a file name db.php and add the following code into your file.
The below code is used to create a MySQL database connection in PHP. When you insert form data into MySQL database, there you will include this file:
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Step 3 – Download simple-bootstrap-paginator Plugin
In this step, download the jQuery simple bootstrap pagination plugin and place it into your PHP project directory. Then include Bootstrap, jQuery and pagination plugin files into your list.php file.
Step 4 – Create List File
In this step, you need to create a file which names list.php and add the following code into it:
<!DOCTYPE html>
<html>
<head>
<title>php mysql jquery ajax pagination - Tutsmake</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="plugin/simple-bootstrap-paginator.js"></script>
<script src="js/pagination.js"></script>
</head>
<body>
<div class="container">
<?php
include_once("db.php");
$perPage = 5;
$sqlQuery = "SELECT * FROM users";
$result = mysqli_query($conn, $sqlQuery);
$totalRecords = mysqli_num_rows($result);
$totalPages = ceil($totalRecords/$perPage);
?>
<div class="row">
<table class="table table-hover table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Age</th>
<th>Address</th>
<th>Skills</th>
<th>Designation</th>
</tr>
</thead>
<tbody id="content">
</tbody>
</table>
<div id="pagination"></div>
<input type="hidden" id="totalPages" value="<?php echo $totalPages; ?>">
</div>
</div>
<script>
$(document).ready(function(){
var totalPage = parseInt($('#totalPages').val());
var pag = $('#pagination').simplePaginator({
totalPages: totalPage,
maxButtonsVisible: 5,
currentPage: 1,
nextLabel: 'Next',
prevLabel: 'Prev',
firstLabel: 'First',
lastLabel: 'Last',
clickCurrentPage: true,
pageChange: function(page) {
$("#content").html('<tr><td colspan="6"><strong>loading...</strong></td></tr>');
$.ajax({
url:"get_data.php",
method:"POST",
dataType: "json",
data:{page: page},
success:function(responseData){
$('#content').html(responseData.html);
}
});
}
});
});
</script>
</body>
</html>
Step 5 – Create Get Data PHP File
In this step, you need to create a new file name get_data.php and add the below code into your get_data.php file.
<?php
include_once("db.php");
$perPage = 5;
if (isset($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
$startFrom = ($page-1) * $perPage;
$sqlQuery = "SELECT id, name, age, address, skills, designation
FROM users ORDER BY id ASC LIMIT $startFrom, $perPage";
$result = mysqli_query($conn, $sqlQuery);
$paginationHtml = '';
while ($row = mysqli_fetch_assoc($result)) {
$paginationHtml.='<tr>';
$paginationHtml.='<td>'.$row["id"].'</td>';
$paginationHtml.='<td>'.$row["name"].'</td>';
$paginationHtml.='<td>'.$row["age"].'</td>';
$paginationHtml.='<td>'.$row["address"].'</td>';
$paginationHtml.='<td>'.$row["skills"].'</td>';
$paginationHtml.='<td>'.$row["designation"].'</td>';
$paginationHtml.='</tr>';
}
$jsonData = array(
"html" => $paginationHtml,
);
echo json_encode($jsonData);
?>
The above below code is used to fetch data from MySQL database using PHP server. And return data to HTML page using jQuery ajax pagination + bootstrap library.
Conclusion
PHP MySQL jquery ajax pagination. Through this tutorial, you have learned how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.
Recommended PHP Tutorials
If you have any questions or thoughts to share, use the comment form below to reach us.
