Pagination with JQuery, MySql and PHP (easy)

[rajat.vaghani37]

My first tutorial here.. So not quite sure how it will be, and if it turns out to even be of any help to anyone out here, but still, worth a try! :smile:

This tutorial, like the title says, is for simple pagination.. Which is very important for people who have text flowing and flowing in a page. Visitors obviously like organized websites.
The code is very simple to understand and implement as well. Let me know if you have any questions.

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

First obviously we need a database table -

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

jquery_pagination.js -

$(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 ( remember to change hostname-localhost here, username, password, database)

<?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 - the 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)
?>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/
libs/jquery/1.3.0/jquery.min.js"></script>
<script type="text/javascript" src="jquery_pagination.js"></script>

<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 script to display the data from the 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>

Obviously, you can use css to style it too..
Let me know if it helped or you need more help and your stuck somewhere or anything.. the code is pretty self explanatory I feel.. though tell me if you need explanations anywhere and i'd gladly do it!