Creating facebook / twitter style pagination

In this article you will learn how to create pagination like Facebook / Twitter. You should have knowledge in PHP, MySQL, CSS, jQuery, Ajax to understand this article.

Lets begin…

* First create a database named “count”.

* Create a table named “numbers” on the database we have created and add fill data to the table from one to twenty as 20 records(rows).

SQL Query is as below…

--
-- Database: `count`
--

-- --------------------------------------------------------

--
-- Table structure for table `numbers`
--

CREATE TABLE IF NOT EXISTS `numbers` (
  `number_id` int(10) NOT NULL AUTO_INCREMENT,
  `number_value` varchar(10) NOT NULL,
  PRIMARY KEY (`number_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `numbers`
--

INSERT INTO `numbers` (`number_id`, `number_value`) VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four'),
(5, 'Five'),
(6, 'Six'),
(7, 'Seven'),
(8, 'Eight'),
(9, 'Nine'),
(10, 'Ten'),
(11, 'Eleven'),
(12, 'Twelve'),
(13, 'Thirteen'),
(14, 'Fourteen'),
(15, 'Fifteen'),
(16, 'Sixteen'),
(17, 'Seventeen'),
(18, 'Eighteen'),
(19, 'Nineteen'),
(20, 'Twenty');

-- Database: `count`

-- --------------------------------------------------------

-- Table structure for table `numbers`

CREATE TABLE IF NOT EXISTS `numbers` (

`number_id` int(10) NOT NULL AUTO_INCREMENT,

`number_value` varchar(10) NOT NULL,

PRIMARY KEY (`number_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

-- Dumping data for table `numbers`

INSERT INTO `numbers` (`number_id`, `number_value`) VALUES

(1, 'One'),

(2, 'Two'),

(3, 'Three'),

(4, 'Four'),

(5, 'Five'),

(6, 'Six'),

(7, 'Seven'),

(8, 'Eight'),

(9, 'Nine'),

(10, 'Ten'),

(11, 'Eleven'),

(12, 'Twelve'),

(13, 'Thirteen'),

(14, 'Fourteen'),

(15, 'Fifteen'),

(16, 'Sixteen'),

(17, 'Seventeen'),

(18, 'Eighteen'),

(19, 'Nineteen'),

(20, 'Twenty');

We are going to show this 20 records in pagination like facebook.

* Also generate a new loading GIF icon here http://www.ajaxload.info/

* Create config.php file and place the following code

<?php
    mysql_connect("localhost","root","") or die('Can not connect to mysql');
    mysql_select_db("count") or die('Can not select database');
?>

<?php

mysql_connect("localhost","root","") or die('Can not connect to mysql');

mysql_select_db("count") or die('Can not select database');

* Create pagination.php file and place the following code

<?php
    require 'config.php';
?>
<html>
    <head>
        <title>Sample FB type Pagination</title>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        <script type="text/javascript">
            $(document).ready(function(){
                $(".more").live('click',function(){
                    var last_record = $(this).attr('id');
                    $.ajax({
                        type : "POST",
                        url  : "getmore.php",
                        data : "last_record="+last_record,
                        beforeSend : function(){
                            var exhtml = $('#more').html();
                            $('.more').html('<img src="ajax-loader.gif" alt="Loading..." />');
                        },
                        success : function(html){
                            $('.more').remove();
                            $('#records').append(html);
                        }
                    });
                });
            });
        </script>
        <style>
            ul{
                list-style:none;
                font-family:"Lucida Sans", verdana;
                font-size:12px;
                width:250px;
            }
            li{
                color:#333333;
                padding:5px;
            }
            li:hover{
                background:#EFEFEF;
            }
            .more{
                cursor:pointer;
                font-weight:bold;
                background:#EFEFEF;
                color:#3B5997;
                text-align:center;
            }
            .nomore{
                font-weight:bold;
                background:#EFEFEF;
                color:#3B5997;
                text-align:center;
            }
        </style>
    </head>
    <body>
    <?php
        // Load 5 records as default...
        $numbers = mysql_query("SELECT * FROM numbers LIMIT 5");
        if(mysql_num_rows($numbers)){
            echo '<ul id="records">';
            while($number = @mysql_fetch_array($numbers)){
                echo '<li>'.$number['number_value'].'</li>';
                $last_record = $number['number_id'];
            }
            echo '<li class="more" id="'.$last_record.'">Get More</li>';
            echo '</ul>';
        }
    ?>
    </body>
</html>

<?php

require 'config.php';

<html>

<head>

<title>Sample FB type Pagination</title>

$(document).ready(function(){

$(".more").live('click',function(){

var last_record = $(this).attr('id');

$.ajax({

type : "POST",

url : "getmore.php",

data : "last_record="+last_record,

beforeSend : function(){

var exhtml = $('#more').html();

$('.more').html('<img src="ajax-loader.gif" alt="Loading..." />');

success : function(html){

$('.more').remove();

$('#records').append(html);

}

});

</script>

<style>

ul{

list-style:none;

font-family:"Lucida Sans", verdana;

font-size:12px;

width:250px;

}

li{

color:#333333;

padding:5px;

}

li:hover{

background:#EFEFEF;

}

.more{

cursor:pointer;

font-weight:bold;

background:#EFEFEF;

color:#3B5997;

text-align:center;

}

.nomore{

font-weight:bold;

background:#EFEFEF;

color:#3B5997;

text-align:center;

}

</style>

</head>

<body>

<?php

// Load 5 records as default...

$numbers = mysql_query("SELECT * FROM numbers LIMIT 5");

if(mysql_num_rows($numbers)){

echo '<ul id="records">';

while($number = @mysql_fetch_array($numbers)){

echo '<li>'.$number['number_value'].'</li>';

$last_record = $number['number_id'];

}

echo '<li class="more" id="'.$last_record.'">Get More</li>';

echo '</ul>';

}

</body>

</html>

* Create getmore.php file and place the following code

<?php
    require 'config.php';
    if(isset($_POST['last_record']) && $_POST['last_record']!=''){
        $lastid = $_POST['last_record'];
        // Load 5 more records from the last record...
        $numbers = mysql_query("SELECT * FROM numbers where number_id > ".$lastid." LIMIT 5") or die('Error');
        if(mysql_num_rows($numbers)){
            while($number = @mysql_fetch_array($numbers)){
                echo '<li>'.$number['number_value'].'</li>';
                $last_record = $number['number_id'];
            }
            // Show More link...
            echo '<li class="more" id="'.$last_record.'">Get More</li>';
        }else{
            echo '<li class="nomore">No more records.</li>';
        }
    }else{
        echo '<li class="nomore">No more records.</li>';
    }
?>

<?php

require 'config.php';

if(isset($_POST['last_record']) && $_POST['last_record']!=''){

$lastid = $_POST['last_record'];

// Load 5 more records from the last record...

$numbers = mysql_query("SELECT * FROM numbers where number_id > ".$lastid." LIMIT 5") or die('Error');

if(mysql_num_rows($numbers)){

while($number = @mysql_fetch_array($numbers)){

echo '<li>'.$number['number_value'].'</li>';

$last_record = $number['number_id'];

}

// Show More link...

echo '<li class="more" id="'.$last_record.'">Get More</li>';

}else{

echo '<li class="nomore">No more records.</li>';

}

}else{

echo '<li class="nomore">No more records.</li>';

}

Now execute the pagination.php file and see the output. Please checkout the video tutorial for coding explanation.

Video Tutorial

http://www.youtube.com/watch?feature=player_embedded&v=2Ha00Q9F4_w