Sep 22, 2013

Ketan Patel

Simple Pagination using jquery,mySql and PHP

When you are displaying any list of records, then it becomes necessary to display them in different pages instead of displaying on the single page as it will become long depending on the total numbers of the records. In this post we are going to learn about Pagination using jQuery, MySQL and PHP. This is very simple tutorial. It has four files which are as below.


Pagination using jquery,mySql and PHP




(1) config.php file
    - For the database connection details.

(2) index.php file
    - Main file that display the result to the user.

(3) data.php file
    - File having code to fetch the data from the table.

(4) pagination.js file
    - Javascript file for acting as a data controller


Create Database table

CREATE TABLE IF NOT EXISTS `users`(
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `FirstName` varchar(200) NOT NULL,
 `Middlename` varchar(200) NOT NULL,
 `LastName` varchar(200) NOT NULL,
 PRIMARY KEY (`id`) )

config.php file


Change the values of hostname,username,password and database name
<?php
$mysql_hostname = "localhost"; 
$mysql_user     = "root"; 
$mysql_password = ""; 
$mysql_database = "test"; 
$con = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Opps error! occurred");
mysql_select_db($mysql_database, $con) or die("Opps error! occurred");
?>

index.php file

Note : Here we are using select count(*) from table instead of select * from table because Letting MySQL do the counting is SIGNIFICANTLY faster,as it only counts the entries rather than parsing all data,AND only returns a very small block of data :)
<?php
include('config.php');
$per_page = 3; 

//getting number of rows and calculating no of pages
$sql    = "select count(*) from users";
$result = mysql_query($sql);
$count  = mysql_fetch_row($result);
$pages  = ceil($count[0]/$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"></script>

<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 id="loading" ></div>

data.php file


<?php
include('config.php');
$per_page = 3; 
if($_GET)
{
 $page=$_GET['page'];
}

//getting table contents
$start = ($page-1)*$per_page;
$sql   = "select * from users order by id limit $start,$per_page";
$rsd   = mysql_query($sql);
?>

<table id="tbl">
   <th><b>Id</b></th>
   <th><b>FirstName</b></th>
   <th><b>MiddleName</b></th>
   <th><b>LastName</b></th>
   <?php
   while($row = mysql_fetch_array($rsd))
   {
    $id    = $row['id'];
    $fname = $row['FirstName'];
    $mname = $row['Middlename'];
    $lname = $row['LastName'];
   ?>
   <tr>
      <td><?php echo $id; ?></td>
      <td><?php echo $fname; ?></td>
      <td><?php echo $mname; ?></td>
      <td><?php echo $lname; ?></td>
  </tr>
  <?php
  } //End while
  ?>
</table>

pagination.js file



$(document).ready(function(){
  
 //Loading Image Display
 function Display_Load()
 {
     $("#loading").fadeIn(100);
     $("#loading").html("<img src='loading.gif' />");
 }
 //Hide Loading Image
 function Hide_Load()
 {
    $("#loading").fadeOut('slow');
 };
 

   //Default Starting Page Results
   
  $("#pagination li:first").css({'color' : '#FF0084','border' : 'none'});
  $("#content").load("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("data.php?page=" + pageNum, Hide_Load());
 });
});

Download Full Source

Ketan Patel

About Ketan Patel -

I have developed a wide range of websites using CorePHP, Opencart, CakePHP and CodeIgniter including sites for startup companies and small businesses. Apart from my blogging life, I like to read Novels, Listening music and Net surfing.

Subscribe to this Blog via Email :