JQuery Datatable Server Side Pagination Example PHP

In this article we will Discuss jquery datatable server side pagination example php | pagination jquery php | instant search with pagination in php mysql jquery and ajax | ajax live data search using jquery php mysql with pagination

If you are a developer and Want to Create Pagination Through PHP and Jquery, Then This Tutorial is for you. Here you can Develop your own paginations for your Website or For your Articles in the Php Platform using Jquery and MySql Database.

Create Database Table With Some Dummy Data over Mysql :

First You have to create Your Database Table to Save your dummy Data. Here are some Details of Your Database Given Below:

database name => summyifi
table name => pagination
column names => id, post, postlink

1) After Database Create, You need to Run This SQL Queries to Insert Data. Code Given Below:

create database `summyifi`;
use `summyifi`;
CREATE TABLE `pagination` (
  `id` int(11) NOT NULL auto_increment,
  `post` varchar(250) NOT NULL,
  `postlink` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;

INSERT INTO `pagination` VALUES (1, 'How to Make Money on YouTube with Your Creative Work?', 'https://summyifi.com/how-to-make-money-on-youtube-with-your-creative-work.html');
INSERT INTO `pagination` VALUES (2, 'How to add watermark Text over Image in PHP', 'https://summyifi.com/how-to-add-watermark-text-over-image-in-php.html');
INSERT INTO `pagination` VALUES (3, 'How to make money through affiliate links', 'https://summyifi.com/how-to-make-money-through-affiliate-links.html');
INSERT INTO `pagination` VALUES (4, 'How to enable Scalable Vector Graphics over WordPress?', 'https://summyifi.com/how-to-enable-scalable-vector-graphics-over-wordpress.html');
INSERT INTO `pagination` VALUES (5, 'How to Install Woocommerce Plugin over WordPress ?', 'https://summyifi.com/how-to-install-woocommerce-plugin-over-wordpress.html');
INSERT INTO `pagination` VALUES (6, 'How to Start A Blog and earn Money In 2021', 'https://summyifi.com/how-to-start-a-blog-and-earn-money-in-2021.html');
INSERT INTO `pagination` VALUES (7, 'Secret Techniques To Start Freelancing And Be Successful', 'https://summyifi.com/master-the-skills-of-secret-techniques-to-start-freelancing-and-be-successful.html');
INSERT INTO `pagination` VALUES (8, 'How to Make Money on YouTube with Your Creative Work?', 'https://summyifi.com/how-to-make-money-on-youtube-with-your-creative-work.html');
INSERT INTO `pagination` VALUES (9, 'How to Make Money on YouTube with Your Creative Work?', 'https://summyifi.com/how-to-make-money-on-youtube-with-your-creative-work.html');
INSERT INTO `pagination` VALUES (10, 'How to Make Money on YouTube with Your Creative Work?', 'https://summyifi.com/how-to-make-money-on-youtube-with-your-creative-work.html');

2) Create db.php :

<?php
$connection = mysqli_connect('localhost','summyifi_usr','******','summyifi') or die(mysqli_error($connection));
?>

3) Create style.css file – It’s a CSS for your pagination numbers and next and previous Link.

<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}

div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;

text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;

color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;

font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;

color: #DDD;
}

</style>

4) JQuery Required to handle AJAX functions:

<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<script type="text/javascript">
function changePagination(pageId){
     $(".flash").show();
     $(".flash").fadeIn(400).html
                ('Loading <img src="ajax-loader.gif" />');
     var dataString = 'pageId='+ pageId;
     $.ajax({
           type: "POST",
           url: "loadData.php",
           data: dataString,
           cache: false,
           success: function(result){
           $(".flash").hide();
                 $("#pageData").html(result);
           }
      });
}
</script>

5) Create File loadData.php :

<?php
include_once('db.php');

$query="select id from pagination order by id asc";
$res    = mysqli_query($connection,$query);
$count  = mysqli_num_rows($res);
$page = (int) (!isset($_REQUEST['pageId']) ? 1 :$_REQUEST['pageId']);
$page = ($page == 0 ? 1 : $page);
$recordsPerPage = 5;
$start = ($page-1) * $recordsPerPage;
$adjacents = "2";

$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($count/$recordsPerPage);
$lpm1 = $lastpage - 1;   
$pagination = "";
if($lastpage > 1)
    {   
        $pagination .= "<div class='pagination'>";
        if ($page > 1)
            $pagination.= "<a href=\"#Page=".($prev)."\" onClick='changePagination(".($prev).");'>&laquo; Previous&nbsp;&nbsp;</a>";
        else
            $pagination.= "<span class='disabled'>&laquo; Previous&nbsp;&nbsp;</span>";   
        if ($lastpage < 7 + ($adjacents * 2))
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class='current'>$counter</span>";
                else
                    $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     

            }
        }   

        elseif($lastpage > 5 + ($adjacents * 2))
        {
            if($page < 1 + ($adjacents * 2))
            {
                for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if($counter == $page)
                        $pagination.= "<span class='current'>$counter</span>";
                    else
                        $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
                }
                $pagination.= "...";
                $pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
                $pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";   

           }
           elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
           {
               $pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
               $pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
               $pagination.= "...";
               for($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
               {
                   if($counter == $page)
                       $pagination.= "<span class='current'>$counter</span>";
                   else
                       $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
               }
               $pagination.= "..";
               $pagination.= "<a href=\"#Page=".($lpm1)."\" onClick='changePagination(".($lpm1).");'>$lpm1</a>";
               $pagination.= "<a href=\"#Page=".($lastpage)."\" onClick='changePagination(".($lastpage).");'>$lastpage</a>";   
           }
           else
           {
               $pagination.= "<a href=\"#Page=\"1\"\" onClick='changePagination(1);'>1</a>";
               $pagination.= "<a href=\"#Page=\"2\"\" onClick='changePagination(2);'>2</a>";
               $pagination.= "..";
               for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
               {
                   if($counter == $page)
                        $pagination.= "<span class='current'>$counter</span>";
                   else
                        $pagination.= "<a href=\"#Page=".($counter)."\" onClick='changePagination(".($counter).");'>$counter</a>";     
               }
           }
        }
        if($page < $counter - 1)
            $pagination.= "<a href=\"#Page=".($next)."\" onClick='changePagination(".($next).");'>Next &raquo;</a>";
        else
            $pagination.= "<span class='disabled'>Next &raquo;</span>";

        $pagination.= "</div>";       
    }

if(isset($_POST['pageId']) && !empty($_POST['pageId']))
{
    $id=$_POST['pageId'];
}
else
{
    $id='0';
}
$query="select post,postlink from pagination order by id asc
limit ".mysqli_real_escape_string($connection,$start).",$recordsPerPage";
//echo $query;
$res    =   mysqli_query($connection,$query);
$count  =   mysqli_num_rows($res);
$HTML='';
if($count > 0)
{
    while($row=mysqli_fetch_array($res))
    {
        $post=$row['post'];
        $link=$row['postlink'];
        $HTML.='<div>';
        $HTML.='<a href="'.$link.'" target="_blank">'.$post.'</a>';
        $HTML.='</div><br/>';
    }
}
else
{
    $HTML='No Data Found';
}
echo $HTML;
echo $pagination;
?>

AJAX function will send requests to the PHP file and returned your paginated data.

This line of code returns all numbers in the table and shows count.

$query="select id from pagination order by id asc";
$res=mysql_query($query);
$count=mysql_num_rows($res);

This will Display 5 Records on each page. You can also Increase records using $start = ($page-1) * 5; to any number you want.

Index file complete code of CSS and jquery together:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Jquery datatable server side pagination example php | summyifi.com</title>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
    <script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<style>
div.pagination {
padding: 3px;
margin: 3px;
text-align:center;
}

div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;

text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.digg a:active {
border: 1px solid #000099;

color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;

font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;

color: #DDD;
}

</style>

<script type="text/javascript">
$(document).ready(function(){
changePagination('0');	
});
function changePagination(pageId){
     $(".flash").show();
     $(".flash").fadeIn(400).html
                ('Loading <img src="ajax-loader.gif" />');
     var dataString = 'pageId='+ pageId;
     $.ajax({
           type: "POST",
           url: "loadData.php",
           data: dataString,
           cache: false,
           success: function(result){
           $(".flash").hide();
                 $("#pageData").html(result);
           }
      });
}
</script>
  </head>
  <body>
    <h2>How to create pagination in PHP and MySQL with AJAX example.&nbsp;&nbsp;&nbsp;=> <a href="https://summyifi.com/">Home</a> | <a href="https://summyifi.com/">More Demos</a></h2>

<div id="pageData"></div>
<span class="flash"></span>

</body>
</html>

If you have some queries then you can Comment Me. I will surely Reply with solutions. If you need more informative Articles then You can Visit My Website at https://summyifi.com

Thank you 🙂

Tags : jquery datatable server side pagination example php | pagination jquery php | instant search with pagination in php mysql jquery and ajax | ajax live data search using jquery php mysql with pagination

Leave a Reply

Your email address will not be published. Required fields are marked *