[TUT] Pagination using PHP

[Gaijin]

In this tutorial, I will try to show you a way of creating Pagination script for your Content...
For this tutorial, you need an ready table to read from. However if you don't have a table around here is a SQL query command string so you can create a Table...

SQL (Click to View)

Code:

CREATE TABLE IF NOT EXISTS `rainbow` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `hash` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

ANd so you have something inside of this table....

Code:

INSERT INTO `rainbow` (`id`, `value`, `hash`) VALUES
(1, 'Test', '0cbc6611f5540bd0809a388dc95a615b'),
(2, 'SupportForums', '01449a3069d0f790106af1c9ecd0b57c'),
(3, 'Omniscient', 'a3aecb685fd19d080881ff49b06373f2'),
(4, 'sf', '60d31eb37595dd44584be5ef363283e3'),
(5, 'Tutorial', '368fe771261fcb18f7988833c9294a20'),
(6, 'md5', '1bc29b36f623ba82aaf6724fd3b16718'),
(7, 'pagination', 'fe7cd4d1bf3fea9a0d921e224b3fa24c'),
(8, 'php', 'e1bfd762321e409cee4ac0b6e841963c'),
(9, 'python', '23eeeb4347bdd26bfc6b7ee9a3b755dd');

Here is the complete code, it loads data from the table "rainbow" and splits the information into pages...

PHP Code:

<?php

$db = array(
    "host" => "localhost", # host
    "user" => "root", # login username
    "pass" => "", # login password
    "base" => "tut" # database name
);

if(!$connect = @mysql_connect($db['host'], $db['user'], $db['pass'])) {
    # echo mysql_error(), "<br />"; # mysql error
    die("Not Connected!");
}elseif(!$database = @mysql_select_db($db['base'])) {
    # echo mysql_error(), "<br />"; # mysql error
    die("Database {$db['base']} can not be found!");
}

$rows_per_page = 2;

$sql = "SELECT `id` FROM `rainbow`";
if(!$query = @mysql_query($sql)) {
    die(mysql_error());
}

$rows = mysql_num_rows($query);
$pages = ceil(($rows / $rows_per_page));

mysql_free_result($query);

if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] > 1) {
    $page = $_GET['page'];
    $offset = ($rows_per_page * $page) - $rows_per_page;
    echo $offset;
}else{
    $page = 1;
    $offset = 0;
}

$sql = "SELECT * FROM `rainbow` LIMIT {$offset}, {$rows_per_page};";
if(!$query = @mysql_query($sql)) {
    die(mysql_error());
}

echo "Pages: ", $pages, " Rows: ", $rows, "<br />";

while($row = mysql_fetch_array($query)) {
    echo "<div><span>", $row['value'], "</span> => <span>", $row['hash'], "</span></div>";
}

$prev = $page - 1;
$next = $page + 1;

if($page > 1) {
    echo "<a href=\"page.php?page={$prev}\">Previous</a>";
}

echo " | Page: {$page} | ";

if($page < $pages) {
    echo "<a href=\"page.php?page={$next}\">Next</a>";
}

mysql_free_result($query);

mysql_close($connect)

?>

Not let me explain you the code...
The first lines;

PHP Code:

$db = array(
    "host" => "localhost", # host
    "user" => "root", # login username
    "pass" => "", # login password
    "base" => "tut" # database name
);

if(!$connect = @mysql_connect($db['host'], $db['user'], $db['pass'])) {
    # echo mysql_error(), "<br />"; # mysql error
    die("Not Connected!");
}elseif(!$database = @mysql_select_db($db['base'])) {
    # echo mysql_error(), "<br />"; # mysql error
    die("Database {$db['base']} can not be found!");
} 


Are used to establish a connection to your MySql database and select the give Database, in this case that would be the Database with the name "tut".
The $db array holds the login information to your database, with the function mysql_connect we try to connect to the database, otherwise we kill the script
using the function die.
Next we try to select the database, using the function mysql_select_db... On error we kill the script using the function die again!

So far so good, let us continue with defining how much entries we want to show per page;

PHP Code:

$rows_per_page = 2; 


After that, we can run a query to get the totlat number of entries in the Table.

PHP Code:

$sql = "SELECT `id` FROM `rainbow`";
if(!$query = @mysql_query($sql)) {
    die(mysql_error());
}

$rows = mysql_num_rows($query);
$pages = ceil(($rows / $rows_per_page));

mysql_free_result($query); 


We execute the query command $sql using the function mysql_query, if the query fails we kill the script using the same function as the one above, die.
Then we declare the variable $rows holding a value of total entries, the function mysq_num_rows will return the number of entries inside of the table "rainbow".
To get the pages we use simple math, $rows divided by $rows_per_page, for the case we get an floating point, we caluclate the pages number using the ceil function..
This function will round up the returned number of our Calculation above...
And laslty we free up the used memory with the function mysql_free_result.

Now that we have the number of Pages, we can continue with checking on which page the user is, that is done over the GET method.

PHP Code:

if(isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] > 1) {
    $page = $_GET['page'];
    $offset = ($rows_per_page * $page) - $rows_per_page;
}else{
    $page = 1;
    $offset = 0;
} 


First we check if the user has requested a page, if the request is a number and if it's over 1.
If all of the above is set, we declare the variable $page, then caluclate the offset for our next query...
To calculate the offset we first multiply $rows_per_page with the requested $page, and then subtract it with the $rows_per_page again so that our offset is set to the right number.

Otherwise we declare $page to 1 and $offset to 0.

We have our pages and the offset, we can now run the query.

PHP Code:

$sql = "SELECT * FROM `rainbow` LIMIT {$offset}, {$rows_per_page};";
if(!$query = @mysql_query($sql)) {
    die(mysql_error());
}

echo "Pages: ", $pages, " Rows: ", $rows, "<br />"; 


It's the same command as in our first query, only that this time we use an additional command named, LIMIT.
This will tell our mysql_query function to start loading from $offset and just load the number of $rows_per_page.
If the query fails, we again kill the script.

We can now display our loaded entries, using the function mysql_fetch_array, which runs within a while loop.

PHP Code:

while($row = mysql_fetch_array($query)) {
    echo "<div><span>", $row['value'], "</span> => <span>", $row['hash'], "</span></div>";
} 


To give our user the ability to browse through the pages, we declare the number of the previous and the next page.

PHP Code:

$prev = $page - 1;
$next = $page + 1; 


Easy huh...

At this point the script is working, we now show the links to the user...

PHP Code:

if($page > 1) {
    echo "<a href=\"page.php?page={$prev}\">Previous</a>";
}

echo " | Page: {$page} | ";

if($page < $pages) {
    echo "<a href=\"page.php?page={$next}\">Next</a>";
} 


We first check if the $page is over 1, if it is we output a Link to the previous page.
Then we show the user on which page s/he is, and finally we check if the $page is under the amount of $pages.
If it is, we show the link to the next page.

Now we just clean some things;

PHP Code:

mysql_free_result($query);

mysql_close($connect) 


We free up the memory of the last $query and close the connection to mysql using the function mysql_close.

Thank you for reading...

[tartou2]

Very interesting and i like it. Good job

[DAMINK™]

Great tutorial mate. Rep given to reflect as much.

[Gaijin]

Very interesting and i like it. Good job

Thank you! ;)

Great tutorial mate. Rep given to reflect as much.

lol Thank you very much DAMNIK, glads you like it!

[zee000]

Hello,

Actually your pagination tut is excellent its very simple to understand, Even the new guys like me also can understand easily it helped me a lot but still i need some more as i would like to have the pagination in as the below format


| First |Previous | 1 | 2 | 3 | 4 | 5 |....| 10 | |Next | Last |

i have seen in this format in this forum it looks better because we can view data easily even it is more. any suggestion to do in this way please

[Proof]

Interesting way of doing it, I use a different method but this one works just as good. Although this has way more code to it.

[LMCampbell]

Nice tut. Very interesting and Helpful

[Arеs]

Nice tutorial, very helpful. Thanks!