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. Take a look at this live demo
Pagination with jQuery, MySQL and PHP.


The tutorial contains three PHP files and two js files includes jQuery plugin.

-config.php (Database Configuration)
-pagination.php
-pagination_data.php
-jquery.js
-jquery_pagination.js

Download Script     Live Preview

Database Table

CREATE TABLE messages
(
msg_id INT PRIMARY KEY AUTO_INCREMENT,
message TEXT
);
 

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

$(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(“pagination_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(“pagination_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”);

?>

pagination.php
User interface page.

<?php

include(‘config.php’);
$per_page = 9;

//Calculating no of pages
$sql = “select * from messages”;
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$pages = ceil($count/$per_page)

?>

// <![CDATA[
javascript” src=”http://ajax.googleapis.com/ajax/
// ]]>
libs/jquery/1.3.0/jquery.min.js”>
// <![CDATA[
javascript” src=”jquery_pagination.js”>
// ]]>

<div id=”loading” ></div>
<div id=”content” ></div>
<ul id=”pagination”>
<?php
//Pagination Numbers

for($i=1; $i<=$pages; $i++)
{
echo ‘<li id=”‘.$i.‘”>’.$i.‘</li>’;
}

?>
</ul>

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

<?php

include(‘config.php’);
$per_page = 9;
if($_GET)
{
$page=$_GET[‘page’];
}

$start = ($page-1)*$per_page;
$sql = “select * from messages order by msg_id limit $start,$per_page”;
$result = mysql_query($sql);

?>

<table width=”800px”>

<?php

while($row = mysql_fetch_array($result))
{
$msg_id=$row[‘msg_id’];
$message=$row[‘message’];

?>

<tr>
<td><?php echo $msg_id; ?></td>
<td><?php echo $message; ?></td>
</tr>

<?php

}

?>

</table>

CSS Code
CSS code for page numbers.

#loading

{
width: 100%;
position: absolute;
}

li

{
list-style: none;
float: left;
margin-right: 16px;
padding:5px;
border:solid 1px #dddddd;
color:#0063DC;
}

li:hover

{
color:#FF0084;
cursor: pointer;
}

Reference: 9lessons.info

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s