Skip to content

Pagination with jQuery, MySQL and PHP

  • by
Pagination with jQuery, MySQL and PHP

I received lot of requests from my readers that asked to me how to implement Pagination with jQuery, PHP and MySQL. so I had developed a simple tutorial. It’s looks big but very simple script.

Pagination with jQuery, MySQL and PHP

Jquery Duplicate Fields Form Submit with PHP
Creating ZIP File with PHP

Pagination with jQuery, MySQL and PHP

The tutorial contains three PHP files, one CSS file and two js files includes jQuery plugin.

-config.php (Database Configuration)
-index.php
-page_data.php
-style.css
-jquery.js
-pagination_js.js


Database Table

CREATE TABLE `Pagination` (
`id` INT NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR( 80 ) NOT NULL ,
`last_name` VARCHAR( 80 ) NOT NULL ,
PRIMARY KEY ( `id` )
)

pagination_js.js
Contains javascript this script works like a data controller.

// JavaScript Document
$(document).ready(function(){
    
  //Display Loading Image
  function Display_Load()
  {
      $("#loading").fadeIn(900,0);
    $("#loading").html("<img src='bigLoader.gif' />");
  }
  //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("page_data.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("page_data.php?page=" + pageNum, Hide_Load());
  });
  
  
});

config.php
You have to change hostname, username, password and databasename.

<?php
$mysql_hostname = "localhost";
$mysql_user = "username";
$mysql_password = "password";
$mysql_database = "database";
$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");
?>

index.php
User interface page.

<?php
include('config.php');
$per_page = 6; 
//getting number of rows and calculating no of pages
$sql = "select * from Pagination";
$rsd = mysql_query($sql);
$count = mysql_num_rows($rsd);
$pages = ceil($count/$per_page)
?>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>
<script type="text/javascript" src="pagination_js.js"></script>
<link href="style.css" rel="stylesheet">

<div align="center">
<div id="loading" ></div>
<div id="content" ></div>
<table width="800px">
<tr>
<td><ul id="pagination">
<?php
//Show page links
for($i=1; $i<=$pages; $i++)
{
  echo '<li id="'.$i.'">'.$i.'</li>';
}
?>
</ul></td>
</tr>
</table>
</div>

page_data.php
Simple php script display data from the messages table.

<?php
include('config.php');
$per_page = 6; 
if($_GET)
{
  $page=$_GET['page'];
}
//get table contents
$start = ($page-1)*$per_page;
$sql = "select * from Pagination order by id limit $start,$per_page";
$rsd = mysql_query($sql);
?>

<table class="tableStrip">
<tr><th>#</th><th>First Name</th><th>Last Name</th></tr>
<?php
//Print the contents
while($row = mysql_fetch_array($rsd))
{
  $id=$row['id'];
  $fname=$row['first_name'];
  $lname=$row['last_name'];
  ?>
  <tr><td style="color:#B2b2b2; padding-left:4px" width="30px"><?php echo $id; ?></td><td><?php echo $fname; ?></td><td><?php echo $lname; ?></td></tr>
  <?php
} //while
  ?>
</table>

style.css
CSS code for page numbers.

@charset "utf-8";
/* CSS Document */
body {
  margin: 0;
  padding: 0;
  font-family:Verdana;
  font-size:15px
}
a {
  text-decoration:none;
  color:#B2b2b2;
}
a:hover {
  color:#DF3D82;
  text-decoration:underline;
}
#loading {
  width: 100%;
  position: absolute;
}
#pagination {
  text-align:center;
  margin-left:120px;
}
li {
  list-style: none;
  float: left;
  margin-right: 16px;
  padding:5px;
  border:solid 1px #dddddd;
  color:#0063DC;
}
li:hover {
  color:#FF0084;
  cursor: pointer;
}

table {border-collapse:collapse;}
.tableStrip th {background:#00ffff; padding:5px;border:1px solid #dddddd; font-weight:normal; text-align:left;}
.tableStrip {margin-bottom:25px;width:40%;}
.tableStrip td {padding:5px;border:1px solid #dddddd;}
.tableStrip tr:nth-child(even) {background:#f9f9f9;}

Leave a Reply

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