How to create pagination in php with ajax and mysql

By | July 13, 2017
pagination-jquery-ajax-php-mysql

Hello friends, As we all know that if there are too large data in the database to display on a single page, It will take long time to fetch all records and it will slow down your page. so we need to break specific number of  data into different part of loading then we need pagination in php.

In this tutorial, we will implement pagination in php with ajax and mysql. Please follow the steps below to create ajax pagination-

  1. Firstly create a file named as connect.php to connect with your database.
<?php

$host_name = 'localhost';  
$user_name = 'root';  
$database_password= "";  
$database_name = 'test';  
$conn = mysqli_connect($host_name, $user_name, $database_password, $database_name) or die ('Error connecting to mysql');  

?>

2) In the second step create a file named as index.php where data will be displayed with the pagination link. Let us take a posts table which has number of posts and in this example we will show 2 post per page.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" id="font-awesome-style-css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" media="all">
<!-- jQuery -->
<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<title>simaple ajax and mysql pagination in php</title>
</head>
<body>
<div><h3>Source code : PHP simaple ajax pagination</h1></div>
<div>
<div id="target-content" >loading...</div>

<?php
include('connect.php'); 
$limit = 15;
$sql = "SELECT COUNT(id) FROM posts";  
$rs_result = mysql_query($sql);  
$row = mysql_fetch_row($rs_result);  
$total_records = $row[0];  
$total_pages = ceil($total_records / $limit); 
?>
<div align="center">
<ul class='pagination text-center' id="pagination">
<?php if(!empty($total_pages)):for($i=1; $i<=$total_pages; $i++):  
      if($i == 1):?>
            <li class='active'  id="<?php echo $i;?>"><a href='pagination.php?page=<?php echo $i;?>'><?php echo $i;?></a></li> 
      <?php else:?>
      <li id="<?php echo $i;?>"><a href='pagination.php?page=<?php echo $i;?>'><?php echo $i;?></a></li>
    <?php endif;?>			
<?php endfor;endif;?>  
</div>
</div>
</body>
<script>
jQuery(document).ready(function() {
jQuery("#target-content").load("pagination.php?page=1");
    jQuery("#pagination li").live('click',function(e){
  e.preventDefault();
    jQuery("#target-content").html('loading...');
    jQuery("#pagination li").removeClass('active');
    jQuery(this).addClass('active');
        var pageNum = this.id;
        jQuery("#target-content").load("pagination.php?page=" + pageNum);
    });
    });
</script>
</html>

3) And in the third and last step, we will create a file named as pagination.php and will add some php code to fetch specific number of data from the database.

<table class="table table-bordered table-striped">  
<thead>  
<tr>  
<th>title</th>  
<th>body</th>  
</tr>  
</thead>  
<tbody>  
<?php  
include('connect.php');
$start_from = ($page-1) * $limit;  
$limit = 2; 
$sql = "SELECT * FROM articles ORDER BY title ASC LIMIT $start_from, $limit"; 
$rs_result = mysql_query ($sql); 
while ($row = mysql_fetch_assoc($rs_result)) {  
?>  
            <tr>  
            <td><? echo $row["title"]; ?></td>  
            <td><? echo $row["body"]; ?></td>  
            </tr>  
<?php  
};  
?>  
</tbody>  
</table>