Pagination Using Ajax, jQuery, PHP and MySQL Database

In this tutorial, I will teach you how to create pagination using jQuery , Ajax ,PHP</strong> and MySQL database. It’s quite the same in my last tutorial the only thing is, I added Ajax and I also make some changes in the code. Ajax is a method that can communicate to a server for exchanging data and updating parts of a web page – without refreshing the entire page.

Let’s begin:

1.Create a MySQL database and name it “peopledb“.

2.Do this following query for creating a table in the MySQL database.

CREATE TABLE `peopledb`.`tblpeople` (
 `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `NAME` VARCHAR( 30 ) NOT NULL ,
 `ADDRESS` VARCHAR( 60 ) NOT NULL ,
 `SEX` VARCHAR( 11 ) NOT NULL ,
 `CONTACT` VARCHAR( 15 ) NOT NULL
 ) ENGINE = INNODB;

3.Create a file for a configuration between MySQL database and PHP script. Name it “config.php”.

<?php
$server = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'peopledb';
$con = mysql_connect($server, $dbuser, $dbpass);
if (isset($con)) {
 # code...
 $dbSelect = mysql_select_db($dbname);
 if (!$dbSelect) {
 echo "Problem in selecting database! Please contact administraator";
 die(mysql_error());
 }
} else {
 echo "Problem in database connection! Please contact administraator";
 die(mysql_error());
}
?>

4.Create a css file for the layout of your design. Name it “style.css”.


.table {
 width: 100%;
 max-width: 100%;
 margin-bottom: 20px;
}

.table > thead > tr > th,.table > tbody > tr > td {
 padding: 8px;
 line-height: 2;
 vertical-align: top;
 border-top: 1px solid #eee;
}

.table > thead > tr > th {
 text-align: left;
 vertical-align: bottom;
 border-bottom: 1px solid #eee;
}
#loader { 
 max-width: 100%;
 text-align: center;
}
#loader > img {
 width: 20%;
 height: 30%;
}

5.Set these codes for the design in the first load of the page. Name it “index.php”.

<!DOCTYPE html>
<html>
<head>
 <!-- css extension -->
 <link href="css/style.css" rel="stylesheet" type="text/css">
 <title>Itsourcecode</title>
</head>
<body>
 <!-- container -->
 <div id="wrap">
 <!-- header -->
 <h1 class="page-header">Pagination with Jquery, PHP and MySQL
 Database</h1><!-- Load content -->
 <div id="content">
 <?php
include 'loaddata.php';
?>
 </div><!-- for paging -->
 <?php
$perPage = 3;

$sqlQuery = "SELECT * FROM `tblpeople`";
$result = mysql_query($sqlQuery);
$maxrow = mysql_num_rows($result);
$page_number = ceil($maxrow / $perPage)?>
 <ul id="pagination">
 <?php
//Load page numbers
for ($i = 1; $i <= $page_number; $i++) {
 echo '<li id="' . $i . '">' . $i . '</li>';
}
?>
 </ul>
 </div>
 <script src="jquery/jquery.min.js" type="text/javascript">
 </script> <!-- jquery extension --> 
 <script src="js/paging.js" type="text/javascript">
 </script><!-- paging extension -->
</body>
</html>

6.Create a script for the method of pagination. Name it “paging.js”.


         $.ajax({
             type: "GET",
             url: "loaddata.php",
             dataType: "text", //expect html to be returned  
             data: {
                 page: +page_number
             },
             success: function(data) {
                 $("#content").html(data);
             }
         });
     });
 });

7.Do the following code for the method in loading the data. Name it “loaddata.php”

<?php
include 'config.php';
?> 
<div id="loader"> 
 <!--  -->
 <table class="table" cellspacing="0">
   <thead>
    <tr> 
     <th>Name</th>
     <th>Address</th>
     <th>Sex</th> 
     <th>Contact#</th> 
    </tr> 
   </thead> 
   <tbody> 
 <?php
$perPage = 3;
if ($_GET) {
 $page = $_GET['page'];
} else {
 $page = 1;
}
$firstPage = ($page - 1) * $perPage;
$sqlQuery = "SELECT * FROM `tblpeople` limit {$firstPage},{$perPage}";
$result = mysql_query($sqlQuery) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
 echo '<tr>';
 echo '<td>' . $row['NAME'] . '</a></td>';
 echo '<td>' . $row['ADDRESS'] . '</td>';
 echo '<td>' . $row['SEX'] . '</td>';
 echo '<td>' . $row['CONTACT'] . '</td>';
 echo '</tr>';
}
?>  
   </tbody> 
 </table> 
</div>

For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :

Email – [email protected]
Mobile No. – 09305235027 – tnt

Download Sourcecode

Leave a Comment Cancel reply