Simple Ajax pagination in php MySQLi
In this tutorial we are going to create a simple AJAX Pagination in PHP MySQLi.
To perform AJAX we will use the most popular JavaScript library Jquery.
Related tutorial of Pagination
To create this Pagination follow the below steps
Step – 1
First, you go to your phpMyAdmin and create a Database called pagination.
After that select this database and go to SQL section then copy the below SQL code and paste into the SQL text-area and then click on Go button.
CREATE TABLE `posts` (
`id` int(10) UNSIGNED NOT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`body` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `posts`
ADD PRIMARY KEY (`id`);
ALTER TABLE `posts`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;This SQL code creates a table called posts into the pagination Database and it also create posts table structure.
After that please insert some records into the posts table.
Step – 2
In step 2, go to your www directory or Xampp htdocs folder and here you create a new folder called whatever you want, Here I named this folder pagination.
After that go inside the newly created folder and here you create three files index.php, db_connection.php, and posts.php.
Creating files
db_connection.php
Create this file for making database connection.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db_name = "pagination";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $db_name);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>index.php
In the index.php file we will show our posts through AJAX.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP pagination with AJAX</title>
<style>
body{
padding:0 20px;
margin: 0;
font-family: sans-serif;
}
.page_link{
display: inline-block;
color: #222;
border: 1px solid #ddd;
padding: 5px 10px;
margin: 0 5px;
text-decoration: none;
cursor: pointer;
}
.active_page{
background-color:dodgerblue;
color: #FFF;
outline: none;
border: 1px solid rgba(0,0,0,.1);
}
.container{
border: 5px solid #ccc;
padding: 10px;
}
.posts{
margin: 0;
list-style: none;
padding: 0;
}
.posts li{
padding:10px 5px;
margin: 0;
border-bottom: 1px solid #ddd;
}
h2{
margin: 0;
padding: 0;
}
p{
margin: 0;
padding:10px 10px 0 10px;
color: #444;
}
</style>
</head>
<body>
<h1 style="text-align:center;">PHP Pagination with AJAX</h1>
<div class="container">
<ul class="posts">
<!-- SHOW DATA -->
</ul>
</div>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script>
$(function(){
// function for getting posts from posts.php
function getPosts(pageNum){
var pageNum = pageNum;
$.ajax({
type:'POST',
url:'posts.php',
data:{page:pageNum},
success:function(data){
$('.posts').html(data);
},
error:function(error){
console.log(error);
}
});
}
// call the getPosts function
// here 1 is default page number
getPosts(1);
// when click pagination button
$('.posts').on('click','.page_link',function(e){
e.preventDefault();
var page_num = $(this).attr('href');
getPosts(page_num);
});
});</script>
</body>
</html>posts.php
In the posts.php we will fetch posts or data from Database (we’ll get data from here through AJAX).
<?php
// check request method
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
// if request method is not POST
http_response_code(404);
echo "<h1>404 Page Not found!</h1>";
exit;
}
// including database connection
require 'db_connection.php';
if(isset($_POST['page'])){
// if get page number through post request and check it is valid number
$page_num = filter_var($_POST['page'], FILTER_VALIDATE_INT,[
'options' => [
'default' => 1,
'min_range' => 1
]
]);
}else{
//Defautl page number
$page_num = 1;
}
// set how much show posts in a single page
$page_limit = 2;
// Set Offset
$page_offset = $page_limit * ($page_num - 1);
function showPosts($conn, $current_page_num, $page_limit, $page_offset){
// query of fetching posts
$query = mysqli_query($conn,"SELECT * FROM `posts` ORDER BY id LIMIT $page_limit OFFSET $page_offset");
// check database is not empty
if(mysqli_num_rows($query) > 0){
// fetching data
while($row = mysqli_fetch_array($query)){
echo '<li><h2>'.$row['title'].'</h2><p>'.$row['body'].'</p></li>';
}
// total number of posts
$total_posts = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM `posts`"));
// total number of pages
$total_page = ceil($total_posts / $page_limit);
// set next page number
$next_page = $current_page_num+1;
// set prev page number
$prev_page = $current_page_num-1;
echo "<li>";
//showing prev button and check current page number is greater than 1
if($current_page_num > 1){
echo '<a href="'.$prev_page.'" class="page_link">Prev</a>';
}
// show all number of pages
for($i = 1; $i <= $total_page; $i++){
//highlight the current page number
if($i == $current_page_num){
echo '<a href="'.$i.'" class="page_link active_page">'.$i.'</a>';
}else{
echo '<a href="'.$i.'" class="page_link">'.$i.'</a>';
}
}
// showing next button and check this is last page
if($total_page+1 != $next_page){
echo '<a href="'.$next_page.'" class="page_link">Next</a>';
}
echo "</li>";
}else{
echo "No Data found !";
}
}
showPosts($conn, $page_num, $page_limit, $page_offset);
?>Completed.
Excellent tutorial.I have a wordpress site. This tutorial is really helpful our website.