Pagination with PHP, MYSQL and jQuery

How to make pro pagination with PHP and jQuery

When I started to learn how to make a website, Then I learn PHP and MYSQL and I built my first “Blog” Website using PHP and MYSQL, and after posting more than 35 posts I realize that something is missing in my blog because all the post on my blog are open in the home page, after searching on google I found that I have missed pagination in my blog. Then again, I search on google for “How to make pagination with PHP and MYSQL”. then many of web development websites are saying to use classes and function of PHP to build pagination but I need some simple way to make pagination for my blog because at that time I am a beginner in PHP. after some time of searching, I found jQuery and that time I decide to make Pagination with PHP and jQuery. and in this post I am sharing you easy way to make pagination in your website or any project using PHP, MYSQL and jQuery.

So let’s start:

First we need jQuery plugin for pagination you can find jQuery Pagination.js plugin from Download jQuery Pagination.js plugin  and then we need style.css Download style.css

Crete database in phpMyAdmin name pagination or your own and run these SQL query.

[php]
CREATE TABLE `post` (
`post_id` int(10) NOT NULL,
`post_name` varchar(250) NOT NULL,
`post_data` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `post`
ADD PRIMARY KEY (`post_id`);

ALTER TABLE `post`
MODIFY `post_id` int(10) NOT NULL AUTO_INCREMENT;
COMMIT;
[/php]

Then inset some test data in post table, then create db.php for database connection.

[php]
<?php
// Database Configuration
define("DB_HOST", "localhost"); // Database Host
define("DB_USER", "root"); // Database Username
define("DB_PASS", ""); // Database Password
define("DB_NAME", "pagination"); // Database Name

$connection = mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);
//Check Connection
if (mysqli_connect_errno() ){
echo "Database Connection Failed: " . mysqli_connect_error();
die;
}
?>
[/php]

Next create index.php file and and built HTML5 basic structure like given below.

[php]

<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<meta charset="utf-8">
<title>Pagination with PHP, MYSQL and jQuery</title>
</head>
<body>

</body>
</html>

[/php]

after making basic structure HTML5 Link style.css and jQuery-pagination.js and add also bootstrap and jQuery in head tag. exact code is given below.

[php]

<!DOCTYPE html>
<html lang="`en`" dir="ltr">
<head>
<meta charset="utf-8">
<title>Pagination with PHP, MYSQL and jQuery</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
<style href="assets/style.css" rel="stylesheet" ></style>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body style="text-align: center;">
<?php
include ‘db/db.php’; // Include database connection to the index.php file

$sql = "SELECT * FROM `post`";
$result = mysqli_query($connection, $sql);
while($row = mysqli_fetch_assoc($result)) {
?>
<article>
<h3><?php echo $row[‘post_name’] ?></h3>
<p class="post"><?php echo $row[‘post_data’] ?></p>
</article>
<?php } ?>
</body>
<script src="assets/jQuery-pagination.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" ></script>
</html>

[/php]

After linking css and js. you have to load post_name and post_data from SQL using php, so lets do that. below code is the final code for index.php

[php]
<!DOCTYPE html>
<?php
include ‘db/db.php’;
?>
<html lang="en" dir="ltr">
<head>
<meta charset="utf-8">
<title>Pagination with PHP, MYSQL and jQuery</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
<style href="assets/style.css" rel="stylesheet" ></style>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<style>
/* these css code for changing pagination color */
.pagination > li > a{
color: #777;
}
.pagination > .active > a, .pagination > .active > a:focus, .pagination > .active > a:hover, .pagination > .active > span, .pagination > .active > span:focus, .pagination > .active > span:hover{
background-color: #777;
border-color: #777;
}
/* these css code for atyling post articles*/
article {
margin: auto auto 10px;
max-width: 500px;
border: 2px solid grey;
padding: 10px;
box-shadow: 10px 10px 5px;
}
.post {
width: 500px;
margin: auto;
}
</style>

<body style="text-align: center;">
<?php
$limit = 5;
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * $limit;

$sql = "SELECT * FROM `post` LIMIT $start_from, $limit";
$result = mysqli_query($connection, $sql);
while($row = mysqli_fetch_assoc($result)) {
?>
<article>
<h3><?php echo $row[‘post_name’] ?></h3>
<p class="post"><?php echo $row[‘post_data’] ?></p>
</article>
<?php } ?>

<div>
<?php
$sql = "SELECT COUNT(post_id) FROM post";
$rs_result = mysqli_query($connection, $sql);
$row = mysqli_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "<nav style=’margin-top: 10px;’><ul class=’pagination’>";
for ($i=1; $i<=$total_pages; $i++) {
$pagLink .= "<li><a href=’index.php?page=".$i."’>".$i."</a></li>";
};
echo $pagLink . "</ul></nav>";
?>
</div>

<script type="text/javascript">
$(document).ready(function(){
$(‘.pagination’).pagination({
items: <?php echo $total_records;?>,
itemsOnPage: <?php echo $limit;?>,
cssStyle: ‘light-theme’,
currentPage : <?php echo $page;?>,
hrefTextPrefix : ‘index.php?page=’
});
});
</script>
</body>
<script src="assets/jquery-pagination.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" ></script>
</html>

[/php]

Now open any web browser and open your project by (localhost/pagination) or different URL that you create for your project. Hey, that’s great you have learn to create pagination using PHP,MYSQL and jQuery.

after doing upper all steps your fine output or result is showing like below.

Pagination with PHP, MYSQL and jQuery