Winnipeg Web Design

Implementing paging using PHP and jQuery

In this tutorial we are going to implement paging using php and jquery. We are going to create a paging widget and we will give it a cool UI using jquery UI. I have tried to explain paging in the most simplest way and it can be extended further easily to more advance form of paging.

Tutorial Details

• Content Index
• Difficulty: Intermediate
• Estimated Completion Time: 60 mins

Content Index

1. Setting up workspace
2. What is pagging ?
3. Tutorial

Setting up workspace

Requirements

• Knowledge of jQuery
• Basic knowledge of PHP and working with My SQL
• AMP(Apache, My SQL, PHP) installed on your system.

We will need some libraries

• jQuery – visit site
• jQuery UI – visit site

We are done with our setup.

What is paging?

Paging is a term used in the field of Operating System in which block of data from the virtual memory known as page is transferred to the main memory for execution. It is used for memory optimization since only required data is there and rest can be brought up on request. In the world of web, it refers to the break up of long tables( mostly database) in page each associated with an id like a page number or an alphabetical order.

Why use paging?

Let us suppose there is a shopping website with well over 2000 items, if we do not use paging when a user opens up the page he will end up with over 2000 items on the page. It’s simply about enhancing user experience, with paging implemented, he would have easily navigated through the items.

Step 1

We will start off by creating our directory structure -

Here we will keep our javascript files in js folder, css files in css folder and php function files in lib folder.

Step 2

Now we will create a php file and save it as config.php in the lib folder. It’s always a good approach to create modules. So our config.php will consist of 2 functions.

1. Function for connecting to the database and it returns a connection link.
2. Function for outputting table by executing a sql query with a limit and an index from which it should start selecting.

Step 3

First we will enter the following code in the config.php

<?php
function connect($host, $user, $password,$database)
{
$link = @mysql_connect($host, $user, $password);
if (!$link)
die("Could not connect: ".mysql_error());
else{
$db = mysql_select_db($database);
if(!$db)
die("Could not select database: ".mysql_error());
else
return $link;
}
}
?>

This is a database function which takes parameter -

• $host – path of the database(mostly localhost)
• $user – database user
• $password – database password
• $database – The database we want to work with.

First by using the mysql_connect() function, connection is established with the host. If the connection is successful thenthe connection link is stored in the $link variable, if it failed to get a connection, we will exit the execution of the script by printing out the error message. Upon successful connection, we will select the required database and return the $link variable.

Step 4

Now we will create the function for outputting a table from the data of a sql query.

function createTable($link,$offset,$limit)
{
$result = @mysql_query("select * from chat_app LIMIT $offset,$limit",$link);
echo "<table><thead><th>User</th><th>Date</th><th>Message</th></thead><tbody>";
while($row = mysql_fetch_array($result))
{
echo    "<tr><td>".$row['user_name']."</td><td>".$row['date']."</td><td>".$row['message']."</td></tr>";
}
echo "</tbody></table><br>";
}

In this function we are taking as parameters the connection of the database, offset which is the index after which we will select the rows and limit which is the number of records we are interested in. We will execute the query using the mysql_query() function and it returns a resultset. Then using the while loop, we will iterate through the associative array returned by the mysql_fetch_array() and simply print out the record data as table rows. Thats it for out config.php file.

Step 5

So finally our config.php file looks like -

<?php
function connect($host, $user, $password,$database)
{
$link = @mysql_connect($host, $user, $password);
if (!$link)
die("Could not connect: ".mysql_error());
else
{
$db = mysql_select_db($database);
if(!$db)
die("Could not select database: ".mysql_error());
else
return $link;
}
}

function createTable($link,$offset,$limit)
{
$result = @mysql_query("select * from chat_app LIMIT $offset,$limit",$link);
echo "<table><thead><th >User</th><th>Date</th><th>Message</th></thead><tbody>";
while($row = mysql_fetch_array($result))
{
echo "<tr><td>".$row['user_name']."</td><td>".$row['date']."</td><td>".$row['message']."</td></tr>";
}
echo "</tbody></table><br>";
}
?>

Step 6

Now we will create a new file and save it in project folder as page.php, it is the file in which will be actually implementing paging. We start of by including the config.php file and declare few variables.

<?php
include_once('lib/config.php');
$offset = $_POST['val'];
$servername='localhost';
$dbusername='root';
$dbpassword = 'root';
$dbname='list';
$link = connect($servername,$dbusername,$dbpassword,$dbname);

Here we have first included our config.php then using the $_POST array we will be getting a variable val which will actually tell which page list to show. We have variables that have string values which are the parameters of our connect() function which we had created in the config.php file. Finally we will call the connect function which returns the link in our $link variable.

Step 7

We will declare more variables that will be used in the paging.

$limit = 10;
$sql_offset = $offset * $limit;
$result = @mysql_query("select * from chat_app");
$maxrows = mysql_num_rows($result);
$nos = ceil($maxrows/$limit);

Here -

• $limit – This is the no of the records to be displayed on the page.
• $sql_offset – This is the actual offset from which database will fetch the records. It depends on the offset variable in which we will have the page index value and limit.
• Now we will have to get the total number of pages, so what we will do is get the total records using @mysql_query(“select * from chat_app”) and get the total count of the rows using mysql_num_rows(). Now we will divide the maxrows by limit and fraction it up, this will give our number of pages.
  Let me explain this with the help of an example -suppose request is of page 2 and limit 10, so sql_offset will be 2 * 10 that is 20. So our function will fetch rows from 21 to 30
  And suppose that there are 48 rows in a table, we will get that count using my_num_rows() and when divide by limit, here the limit is 10 so we will have 4.8. Then we will fraction it up using ceil() function to get 5 which are the number of pages sufficient to show all the details.

Step 8

Now we will call out createTable function and pass the required variables. We will get the list we want.

createTable($link,$sql_offset,$limit);

Step 9

Now we need to show the number of pages also and highlight the current page. So what we will do is loop till maximum no of pages and anchor all the pages except the current page.

for($i = 1;$i <= $nos;$i++ )
{
if($i==$offset+1)
echo "<strong> $i</strong>  ";
else echo "<a href='#' onclick='pager($i)'> $i </a>  ";
}
echo "<br><input type='hidden' id='offset' value='$offset'  ><input type='hidden' id='maxpage' value='$nos'  >";

Finally we will create two hidden variables that we will have current offset value and total no of pages which we will use later.

Step 10

Now we will create a html file called index.html and enter the following code. Here we have imported style.css in which we will code our css, jquery, pager.js in which we will work with our javascript code and jquery ui framework which we will use later.

<html>
<link type="text/css" rel="stylesheet" href="css/style.css" />
<link type="text/css" rel="stylesheet" href="css/start/jquery-ui-1.7.2.custom.css" />
<script type="text/javascript" src="js/jquery.js">
</script>
<script type="text/javascript" src="js/pager.js">
</script><body>
<div id="table-div" ></div>
<div id='buttons-div'><span id='prev'><a href="#" onClick="pager('prev')" >Previous
</a></span>
<span id='next'><a href="#" onClick="pager('next')" >Next</a></span>
</div>
</body>
</html>

There is a div called table-div in which we will dynamically load our table and page anchor links. There are also navigation anchors present wrapped inside span tags. Span tags are here for design purposes which we will use later. This is all for our index.html file.

pager() is a function which we will declare later in the pager.js which will handle our page request.

Step 11

Now we will create a javascript file and name it as pager.js and declare the following function.

function pager(dir)
{
var page = parseInt($("#offset").val());  //convert the value to integer
var max = parseInt($("#maxpage").val());
var no = isNaN(parseInt(dir)); // returns false if it is not a number 

}

We will create a generic function pager() that takes a dir variable which tells the function to set the page

Step 12

Now we will pass integer values in pager() function too, so we will check if a number is detected then page variable which is the variable we will send to the page.php file is set to that value.

function pager(dir)
{
var page = parseInt($("#offset").val());
var max = parseInt($("#maxpage").val());
var no = isNaN(parseInt(dir));
if(!no)
{
page = parseInt(dir);
}
if(dir=="next")
page = page + 1;
else if(dir=="first")
page =0;
else page = page - 1;
}

Here in the first if condition we check that if a number is detected then set the value of page to it, if “next” string is detected, increment the page value by one, remember we called the function pager(‘next’) in our html file. If “first” string is detected set then set the page value to one. Finally if none of the string matches we are left with only one condition that is “previous” so decrement the page value by 1.

Step 13

Now we want to make sure that when page has 0 value, previous anchor does not trigger pager() function and similarly after final page next anchor should not trigger pager() function, so we provide a validation which checks lower and upper bound of the page and fades the necessary anchor. Finally we will use post function of the jquery to send page variable to our page.php file.

if(page==0)
{
$("#prev").fadeOut('fast');
}
else $("#prev").fadeIn('fast');
if(page==max-1)
{
$("#next").fadeOut('fast');
}
else
$("#next").fadeIn('fast');   $.post("page.php",{val:page},function(result){  $("#table-div").html(result);  });

Step 14

Our table needs to be populated when page is loaded first time so we will call pager(‘first’). Finally our pager.js looks like this -

function pager(dir)
{
var page = parseInt($("#offset").val());
var max = parseInt($("#maxpage").val());
var no = isNaN(parseInt(dir));
if(!no)
{
page = parseInt(dir);
}
if(dir=="next")
page = page + 1;
else if(dir=="first")
page =0;
else page = page - 1;
if(page==0)
{
$("#prev").fadeOut('fast');
}
else
$("#prev").fadeIn('fast');
if(page==max-1)
{
$("#next").fadeOut('fast');
}
else
$("#next").fadeIn('fast');   $.post("page.php",{val:page},function(result)
{
$("#table-div").html(result);
});
}

Step 15

Pagging tutorial goes this far but our widget looks really horrible so we will skin the UI. Now comes the jQuery UI part. jQuery UI offers a powerful CSS framework for all sort of elements of our page. We will start with skinning the buttons.We will add the follow classes to our anchor tags -

• ui-state-default – Sets the default interaction theme of the element
• ui-corner-all – Applies corner radius on all four corners.

and ui-widget to our outer container that is span tags,so our code in the index.html looks like.

<span id='prev'  >
<a href="#" onClick="pager('prev')" >Previous</a>
</span>
<span  id='next'>
<a href="#" onClick="pager('next')">Next</a>
</span>

Step 16

Now we will add ui-state-default class to all the anchors in the page.php. For the current page index we will add ui-state-active, the class meanings are self explanatory. So our code in the php file looks like

for($i = 1;$i <= $nos;$i++ )
{
if($i==$offset+1)
echo "<strong class='ui-state-active'> $i</strong>  ";
else
echo "<a class='ui-state-default' href='#' onclick='pager($i)' class='ui-widget ui-state-default'> $i   ";
}

Step 17

Now we finally we will skin our table, add the following classes to the elements in the config.php file

• table – ui-widget
• thead – ui-widget-header
• tbody – ui-widget-content

So our code looks like.

function createTable($link,$offset,$limit)
{
$result = @mysql_query("select * from chat_app LIMIT $offset,$limit",$link);
echo "<table class='ui-widget'><thead  class='ui-widget-header'><th >Use</th><th>Date</th><th>Message</th></thead><tbody  class='ui-widget-content'>";
while($row = mysql_fetch_array($result))
{
echo "<tr'><td>".$row['user_name']."</td><td>".$row['date']."<td><td>".$row['message']."</td></tr>";
}
echo "</tbody></table><br>";
}

Step 18

Now finally tweaking the css code, we can remove text decoration from anchor tags and finally our paging widget is ready. The best part of using the jQuery UI framework is that we can change the theme just by changing the path in the link tag. I have done this in the demo take a look at it.

Be Sociable, Share!

• 

Related posts:

1. Creating a Simple Tooltip jQuery Plugin
2. JQuery JavaScript Framework

Written by Abhin Sharma

Abhin Sharma - I am web developer from Udaipur (city of lakes) ,India who loves to work with Java, Mashups and sometimes PHP. I specialize in Swings and JEE. While not programming I love to hang out my friends.