Pagination Using Ajax, jQuery, PHP and MySQL Database

by

pagignWithajaxIn 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.

<div class="highlight">
<pre><code class="language-php">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;</code></pre>
</div>

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”.

<div class="highlight">
<pre><code class="language-css">
.table {
 width: 100%;
 max-width: 100%;
 margin-bottom: 20px;
}

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

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

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

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

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

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

<div class="highlight">
<pre><code class="language-javascript">
         $.ajax({
             type: "GET",
             url: "loaddata.php",
             dataType: "text", //expect html to be returned  
             data: {
                 page: +page_number
             },
             success: function(data) {
                 $("#content").html(data);
             }
         });
     });
 });</code></pre>
</div>

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

<div class="highlight">
<pre>&lt;?php
include 'config.php';
?&gt; 
&lt;div id="loader"&gt; 
 &lt;!--  --&gt;
 &lt;table class="table" cellspacing="0"&gt;
   &lt;thead&gt;
    &lt;tr&gt; 
     &lt;th&gt;Name&lt;/th&gt;
     &lt;th&gt;Address&lt;/th&gt;
     &lt;th&gt;Sex&lt;/th&gt; 
     &lt;th&gt;Contact#&lt;/th&gt; 
    &lt;/tr&gt; 
   &lt;/thead&gt; 
   &lt;tbody&gt; 
 &lt;?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 '&lt;tr&gt;';
 echo '&lt;td&gt;' . $row['NAME'] . '&lt;/a&gt;&lt;/td&gt;';
 echo '&lt;td&gt;' . $row['ADDRESS'] . '&lt;/td&gt;';
 echo '&lt;td&gt;' . $row['SEX'] . '&lt;/td&gt;';
 echo '&lt;td&gt;' . $row['CONTACT'] . '&lt;/td&gt;';
 echo '&lt;/tr&gt;';
}
?&gt;  
   &lt;/tbody&gt; 
 &lt;/table&gt; 
&lt;/div&gt;</pre>
</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