Skip to main content

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>

Comments

Popular posts from this blog

6 guaranteed steps how to create CRON JOB FUNTION in wordpress

Create Cron Job function in Wordpress plugin Step 1: Register function on plugin activate  register_activation_hook(__FILE__, 'activate_one'); Step 2: add_filter function for interval //Filter for Adding multiple intervals add_filter( 'cron_schedules', 'intervals_schedule' ); // define interval function   function intervals_schedule($schedules) {   $schedules['everyminute'] = array(    'interval' => 60, // Every 1 minutes    'display'  => __( 'Every 1 minutes' )   );   return $schedules;  } Step 3: Activate hook function   //Schedule a first action if it's not already scheduled   function activate_one() {   if (!wp_next_scheduled('wpyog_cron_action')) {    wp_schedule_event( time(), 'everyminute', 'wpyog_cron_action');   }  } Step 4: Cron hook function   //Hook into that action that'll fire after 1 minutes   add_action('wpyog_cron_action', 'execute_

How to Create a jQuery Autocomplete in Wordpress

How to Create a jquery-ui Autocomplete in wordpress. Autocomplete provides suggestions while you type into the text field. In Wordpress we fetch dynamically matched pattern. Include javascript and css files in header. Create action inside functions.php or inside plugin code. add_action('wp_head', 'custom_register_scripts'); function custom_register_scripts(){ wp_register_style( 'techsudhir_jquery_ui_css', plugin_dir_url(__FILE__) . 'css/jquery-ui.css', false,'1.0.0' ); wp_enqueue_style( 'techsudhir_jquery_ui_css' ); wp_register_script('techsudhir_jquery_ui_js',plugin_dir_url(__FILE__) . 'js/jquery-ui.js',array('jquery'),'1.1', false); wp_enqueue_script('techsudhir_jquery_ui_js'); wp_localize_script( 'techsudhir_autocomplete', 'jqueryAutocomplete', array( 'ajaxurl' => admin_url( 'admin-ajax.php' ) ) ); wp_enqueue_script( 'techsudhir_

Facebook Login

Step 1: Go on Apps & create new Apps Step 2: Enter the app name & choose category Step 3: Enter the site domain Script code <div id="fb-root"></div> <script   src="http://connect.facebook.net/en_US/all.js#appId=<?php echo '603609636351597';?>"> </script> <script>    FB.init({              appId:'<?php echo '603609636351597';?>', cookie:true,              status:true, xfbml:true           });   FB.getLoginStatus(function(response) {   }); Login function call on click function faceLogin(){  FB.login(function(response) {    if (response.authResponse) {  FB.api('/me', function(response) { $('input[id=facebook_login_fb]').val(response['id']); $('#fb_login_form').submit();  });    } else {  console.log('User cancelled login or did