Nov 23rd

Comments Pagination using jQuery, AJAX & PHP/MySQL

this quick tutorial teaches you how to make a basic example of comments pagination which can also be easily modified to paginate any large list.

pagTo follow on from my last tutorial, I’m going to demonstrate the basic principles of paginating your query results on a page using jQuery and AJAX. Sometimes your SQL select query will be returning a huge list of results. Pagination is used to separate these lists into more manageable blocks. You can find a working example here.

To do this tutorial I’m going to assume you know how to use XHTML, PHP, MySQL and have a passing knowledge of JavaScript and jQuery and you know how to attach JavaScript files to a web page. I’m also assuming you know how to create a database and a new table and insert and select data from it.

1. Setting Up

Before we start you’re going to need to download the latest version of jQuery from here. Also download the source code for this example here (Includes jQuery version 1.3.2).

If you did my last tutorial you’ve probably already created the “tests” database. If not create a new database called “tests” and a table inside that called “comments” with columns “id” (primary key), “message”(varchar 255) and “date_time”(datetime). Also, add a couple of dummy entries into the table so we can display them from the start. Here’s the SQL code for that:

CREATE TABLE `comments` (
    `id` mediumint(9) NOT NULL auto_increment,
    `message` varchar(255) NOT NULL,
    `date_time` datetime NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

2. The HTML

Lets create our html page first. Create a new page called index.php, attach the jQuery library to it and copy the code below into it:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Testing AJAX</title>
<script type="text/javascript" src="jquery-1.3.2.js"></script>
<style type="text/css" href="style.css"></style>
</head>

<body>

<div>
    <form id="comment_form" method="post">
        <fieldset>
            <legend>Add Comment</legend>
            <p>Message:<br /><textarea name="message" id="message" rows="8" cols="40"></textarea></p>
            <p><input type="submit" value="Add Comment"></p>
        </fieldset>
    </form>
</div>
<div class="comments_list">
</div>
</body>
</html>

This has a simple form to submit our comment and an empty div underneath called comments_list where we’re going to display our comments.

3. Loading The Comments

Copy the comments.php file into the same folder as the index.php. Then paste the following into the bottom of your index page right above where the </body> tag is.

<script type="text/javascript">
$(document).ready(function(){
    function loadCommentsList(p){
        var page_no=p;
        $.ajax({
            url: "comments.php",
            type: "POST",
            cache: false,
            data: "&page_no="+ page_no,
            success : function(html){
                $(".comments_list").html(html);
            }
        });
    }
    loadCommentsList(1);

    $(".page_no").live("click", function(){
        //This is for passing the page number to the pagination script
        var id = $(this).attr("class");
        loadCommentsList(id.substr(13));
    });
});
</script>

The first part is a function called loadCommentsList is and is used for loading our comments. The second part is a jQuery event handler. This handler is waiting for links with the class name page_no to be clicked. Depending on how many pages there will be a different amount of links with this class. When one is clicked it passes the number of the page in the list you want to see. It then calls the loadCommentsList function to reload the list.

4. Saving To The Database

Copy the save.php page into the same folder as the index page. Then copy the following function into the document.ready function in your index page.

$("form#comment_form").submit(function() {
    // we want to store the values from the form input box, then send via ajax below
    var message = $('#message').attr('value');
    $.ajax({
        type: "POST",
        url: "save.php",
        data: "message="+ message,
        success: function(){
            loadCommentsList(1);
        }
    });
    return false;
});

This function is called when the user submits the form. It passes the message to the save.php file which inserts it into the database. On success it then calls the loadCommentsList and reloads the list on the first page.

5. Explanation Of How The Comments File Works

After following the above steps you should have a working page that takes user comments and displays them in a paginated format. Now I’m going to explain line by line just how the pagination works. Open the comments.php file in your text editor. You can see the code is split into 3 main parts: The calculation, the header and the comment loop. Lets start with the calculation.

Calculation
//Determins the amount of results per page
$display=10;

//sets the start point for the SQL query limit
if(isset($_POST['page_no'])){
    $start=(($_POST['page_no']-1) * $display);
}else{
    $start=0;
}

The first few lines are easy. It sets a variable called display which is where you put how many comments you want to display on each page. The next part is an if statement which gets the current page and sets the start point for the SQL query.

//counts the results from that category
$sql="SELECT count(*) AS count FROM comments";
$query=mysql_query($sql);
$result=mysql_result($query, "text");

//makes sure the right number is displayed
if($start+$display>$result){
    $leftover=$start+$display-$result;
    $leftover=$start+$display-$leftover;
}else{
    $leftover=$start+$display;
}

This gets the number of comments in the database. It then sets a variable called leftover. This variable is for displaying the second number in the header eg: 1-10 or 11-15. What its doing is getting the start point, which is the number of the first comment you want to display. It then checks to see if the start plus the display number is bigger than the count. If it is it gets the difference between them.

//determines the amount of pages needed
if($result>$display){
    $total_pages=ceil($result/$display);
}else{
    $total_pages=1;
}

This calculates the number of pages. The ceil function rounds up the number.

//finds the current page
if(!isset($_POST['page_no']) || $_POST['page_no']<1){
    $current_page=1;
}else{
    $current_page=$_POST['page_no'];
}

This sets the current page. If there is no page_no request sent it sets the page to 1.

Header Display
Displaying <?php
if($start!=0 || $result>$display){
    echo $start+1 .'-';
}
echo $leftover .' of ' .$result .' results';
?>

The first thing it does is print out the number of the comments being displayed and how many comments there are in total.

if($current_page>1){
    //this determines wether or not to show the previous button
    echo '<a class="page_no page-'; echo $current_page-1 .'" href="javascript:void(0);">&laquo; previous</a>  ';
}

Next it prints out the page links. If you look at the class for this link you can see its one of the links the jQuery event handler is waiting for. First thing it does is checks to see if page being shown is not the first. If so it prints a previous link.

//prints out the number of pages
for($i=1; $i<=$total_pages; $i++){
    if($i != $current_page){
        echo '<a class="page_no page-' .$i .'" href="javascript:void(0);">' .$i .'</a> ';
    }else{
        echo $i .' ';
    }
}

Next it prints out a link for each page of comments. When it gets to the number of the current page it just prints the number without a link.

// this determines if its the last page and shows the next button
if($total_pages >= $current_page+1){
    echo ' <a class="page_no page-'; echo $current_page+1 .'" href="javascript:void(0);">next &raquo;</a>';
}

Finally, it does a check similar to the previous link except it checks to see if there are pages after the current page. If so it prints a next link.

Comments Loop

The final thing (and easiest!) this script does is a while loop for printing the actual comments. You can see its limit is set to the start and display variables set in the calculation section.

And thats it! You can see a working version here. That was a bit tricky to explain so if you have any questions please feel free to email me or leave a comment below.

Tags: ////
Posted in Blog by Abban |

3 Responses to “Comments Pagination using jQuery, AJAX & PHP/MySQL”

  1. May 27th
    Nathan Mol says:

    Thank you man, this tutorial was very useful !

  2. Jun 24th
    pulsa says:

    terimakasi bos

  3. Jul 14th
    Daniel says:

    Thanks, but…. alot of numbers can’t just be easy as just put like:

    [1] 2 3 4 5 … 82

    PLEASE!!!!

Leave a Reply

Now you've seen what we can do, see what we can do for you