Pagination with MySql, jQuery & Php

Ajax pagination with MySQL, PHP and jQuery

Method 1:

Javascript :
<script type="text/javascript">
$(document).ready(function(){
function loading_show(){
$('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast');
}
function loading_hide(){
$('#loading').fadeOut('fast');
}                
function loadData(page){
loading_show();                    
$.ajax
({
type: "POST",
url: "load_data.php",
data: "page="+page,
success: function(msg)
{
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});
}
});
}
loadData(1); 
$('#container .pagination li.active').on('click',function(){
var page = $(this).attr('p');
loadData(page);

});
});

</script>
load_data.php

if($_POST['page'])
{
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 3;
$previousBtn = true;
$nextBtn = true;
$firstBtn = true;
$lastBtn = true;
$start = $page * $per_page;

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tpe"; 
$conn = mysqli_connect($servername, $username, $password);
$select_db=mysqli_select_db($conn,$dbname);

$query_pag_data = "SELECT * from news LIMIT $start, $per_page";
$result_pag_data = mysqli_query($conn,$query_pag_data) or die('MySql Error' . mysqli_error());
$msg = "";
while ($row = mysqli_fetch_array($result_pag_data)) {
$htmlmsg=htmlentities($row['news_title']);
$msg .= "<li><b>" . $row['id'] . "</b> " . $htmlmsg . "</li>";
}
$msg = "<div class='data'><ul>" . $msg . "</ul></div>";


/* --------------------------------------------- */
$query_pag_num = "SELECT COUNT(*) AS count FROM news";
$result_pag_num = mysqli_query($conn,$query_pag_num);
$row = mysqli_fetch_array($result_pag_num);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);

if ($cur_page >= 7) {
    $start_loop = $cur_page - 3;
    if ($no_of_paginations > $cur_page + 3)
        $end_loop = $cur_page + 3;
    else if ($cur_page <= $no_of_paginations && $cur_page > $no_of_paginations - 6) {
        $start_loop = $no_of_paginations - 6;
        $end_loop = $no_of_paginations;
    } else {
        $end_loop = $no_of_paginations;
    }
} else {
    $start_loop = 1;
    if ($no_of_paginations > 7)
        $end_loop = 7;
    else
        $end_loop = $no_of_paginations;
}
/* ----------------------------------------------------------------------------------------------------------- */
$msg .= "<div class='pagination'><ul>";

if ($firstBtn && $cur_page > 1) {
    $msg .= "<li p='1' class='active'>First</li>";
} else if ($firstBtn) {
    $msg .= "<li p='1' class='inactive'>First</li>";
}

if ($previousBtn && $cur_page > 1) {
    $pre = $cur_page - 1;
    $msg .= "<li p='$pre' class='active'>Previous</li>";
} else if ($previousBtn) {
    $msg .= "<li class='inactive'>Previous</li>";
}
for ($i = $start_loop; $i <= $end_loop; $i++) {

    if ($cur_page == $i)
        $msg .= "<li p='$i' style='color:#fff;background-color:#006699;' class='active'>{$i}</li>";
    else
        $msg .= "<li p='$i' class='active'>{$i}</li>";
}

if ($nextBtn && $cur_page < $no_of_paginations) {
    $nex = $cur_page + 1;
    $msg .= "<li p='$nex' class='active'>Next</li>";
} else if ($nextBtn) {
    $msg .= "<li class='inactive'>Next</li>";
}

if ($lastBtn && $cur_page < $no_of_paginations) {
    $msg .= "<li p='$no_of_paginations' class='active'>Last</li>";
} else if ($lastBtn) {
    $msg .= "<li p='$no_of_paginations' class='inactive'>Last</li>";
}
$total_string = "<span class='total' a='$no_of_paginations'>Page <b>" . $cur_page . "</b> of <b>$no_of_paginations</b></span>";
$msg = $msg . "</ul>". $total_string . "</div>";  // Content for pagination
echo $msg;
}

Style.css

<style>
#container .pagination ul li.inactive,
#container .pagination ul li.inactive:hover{
background-color:#ededed;
color:#bababa;
border:1px solid #bababa;
cursor: default;
}
#container .data ul li{
list-style: none;
font-family: verdana;
margin: 5px 0 5px 0;
color: #000;
font-size: 13px;
}

#container .pagination{
width: 800px;
height: 25px;
}
#container .pagination ul li{
list-style: none;
float: left;
border: 1px solid #006699;
padding: 2px 6px 2px 6px;
margin: 0 3px 0 3px;
font-family: arial;
font-size: 14px;
color: #006699;
font-weight: bold;
background-color: #f2f2f2;
}
#container .pagination ul li:hover{
color: #fff;
background-color: #006699;
cursor: pointer;
}
.total
{
display: inline-block;
padding: 4px 0 0;
vertical-align: top;
}
</style>
Method 2:

Step 1: Database structure

Step: 2 jQuery Script

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>
<script>
$(document).ready(function()
{
//Display Loading Image
function Display_Load()
{
$("#loading").fadeIn(900,0);
$("#loading").html("Loading");
}
//Hide Loading Image
function Hide_Load()
{
$("#loading").fadeOut('slow');
};

//Default Starting Page Results
$("#pagination li:first")
.css({'color' : '#FF0084'}).css({'border' : 'none'});
Display_Load();
$("#content").load("paginate.php?page=1", Hide_Load());

//Pagination Click
$("#pagination li").click(function(){
Display_Load();
//CSS Styles
$("#pagination li")
.css({'border' : 'solid #dddddd 1px'})
.css({'color' : '#0063DC'});

$(this)
.css({'color' : '#FF0084'})
.css({'border' : 'none'});

//Loading Data
var pageNum = this.id;
$("#content").load("paginate.php?page=" + pageNum, Hide_Load());
});

});
</script>

Step 3: Body part

<body>
<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "practice";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd)
or die("Opps some thing went wrong");
?>
<div id="loading" ></div>
<div id="content" >

</div>
<ul id="pagination">
<?php
$per_page = 2;

$sql = "select * from emp";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$pages = ceil($count/$per_page);
for($i=1; $i<=$pages; $i++)
{
echo '<li id="'.$i.'">'.$i.'</li>';
}
?>
</ul>
</body>

Step 4: Paginate.php

<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "practice";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd)
or die("Opps some thing went wrong");
$per_page = 2;
if($_GET)
{
$page=$_GET['page'];
}

$start = ($page-1)*$per_page;
$sql = "select * from emp order by empno limit $start,$per_page";
$result = mysql_query($sql);
?>
<table width="800px">
<?php
while($row =mysql_fetch_array($result))
{
$msg_id=$row['empno'];
$message=$row['ename'];
?>
<tr>
<td><?php echo $msg_id; ?></td>
<td><?php echo $message; ?></td>
</tr>
<?php
}
?>
</table>

Method:3

Pagination on self page

$rec_limit = 3;
if(isset($_GET["page"])){
$page  = $_GET["page"];
}else {
$page=1;
}
$start_from = ($page-1) * $rec_limit;  
$limit = $start_from.','.$rec_limit;
$order ='id desc';
$where_cond = "is_deleted='1'";
$db_obj->select('news','*', $where_cond,null,$order,$limit);
$news = $db_obj->getResult();

$where_cond = "is_deleted='1'";
$db_obj->select('news','*', $where_cond);
$count = $db_obj->numRows();
$total_pages = ceil($count / $rec_limit);
<style>
ul.pagination {
display: inline-block;
padding: 0;
margin: 0 0 0 10px;
}
ul.pagination li {display: inline;}
ul.pagination li a {
color: black;
float: left;
padding: 3px 9px;
text-decoration: none;
transition: background-color .3s;
border: 1px solid #ddd;
margin: 0 4px;
}
ul.pagination li a.active {
background-color: #006699;
color: white;
border: 1px solid #006699;
}
ul.pagination li a:hover:not(.active) {background-color: #ddd;}
</style>
<ul class="pagination">
<?php  
echo "<li><a href='$_PHP_SELF?page=1'>".'First'."</a></li>";
for ($i=1; $i<=$total_pages; $i++) {
$class= ($i==$page)?'active':'inactive';


echo "<li><a class='".$class."' href='$_PHP_SELF?page=".$i."'>".$i."</a></li>";
};
echo "<li><a href='$_PHP_SELF?page=$total_pages'>".'Last'."</a></li>";
?>
</ul>