Ever use a Search Engine? I'm sure you have, lots of time. When Search Engines found thousands of results for a keyword do they spit out all the result in one page? Nope, they use paging to show the result little by little.

Paging means showing your query result in multiple pages instead of just put them all in one long page. Imagine waiting for five minutes just to load a search page that shows 1000 result. By splitting the result in multiple pages you can save download time plus you don't have much scrolling to do.

To show the result of a query in several pages first you need to know how many rows you have and how many rows per page you want to show. For example if I have 295 rows and I show 30 rows per page that mean I'll have ten pages (rounded up).

 
 
For the example I created a table named randoms that store 295 random numbers. Each page shows 20 numbers.

Example: paging.php
Source code :paging.phps

<?php
include 'library/config.php';
include 'library/opendb.php';

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = " SELECT val FROM randoms " .
         " LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($row = mysql_fetch_array($result))
{
   echo $row['val'] . '<br>';
}

// ... more code here
?>

Paging is implemented in MySQL using LIMIT that take two arguments. The first argument specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the first row is 0 ( not 1 ).

When paging.php is called for the first time the value of $_GET['page'] is not set. This caused $pageNum value to remain 1 and the query is :

SELECT val FROM randoms LIMIT 0, 20

which returns the first 20 values from the table. But when paging.php is called like this http://www.php-mysql-tutorial....php?page=4
the value of $pageNum becomes 4 and the query will be :

SELECT val FROM randoms LIMIT 60, 20

this query returns rows 60 to 79.

After showing the values we need to print the links to show any pages we like. But first we have to count the number of pages. This is achieved by dividing the number of total rows by the number of rows to show per page :

$maxPage = ceil($numrows/$rowsPerPage);
 
<?php
// ... the previous code

// how many rows we have in database
$query   = "SELECT COUNT(val) AS numrows FROM randoms";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

// ... still more code coming
?>

The mathematical function ceil() is used to round up the value of $numrows/$rowsPerPage.

In this case the value of total rows $numrows is 295 and $rowsPerPage is 20 so the result of the division is 14.75 and by using ceil() we get $maxPage = 15

Now that we know how many pages we have we make a loop to print the link. Each link will look something like this:

<a href="paging.php?page=5">5</a>

You see that we use $_SERVER['PHP_SELF'] instead of paging.php when creating the link to point to the paging file. This is done to avoid the trouble of modifying the code in case we want to change the filename.

  
We are almost complete. Just need to add a little code to create a 'Previous' and 'Next' link. With these links we can navigate to the previous and next page easily. And while we at it let's also create a 'First page' and 'Last page' link so we can jump straight to the first and last page when we want to.

 

<?php
// ... the previous code

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// and close the database connection
include '../library/closedb.php';

// ... and we're done!
?>

Making these navigation link is actually easier than you may think. When we're on the fifth page we just make the 'Previous' link point to the fourth. The same principle also apply for the 'Next' link, we just need to add one to the page number.

One thing to remember is that we don't need to print the 'Previous' and 'First Page' link when we're already on the first page. Same thing for the 'Next' and 'Last' link. If we do print them that would only confuse the one who click on it. Because we'll be giving them the exact same page.

 
 
We got a problem here...
Take a look at this slightly modified version of paging.php. Instead of showing 20 numbers in a page, I decided to show just three.

See the problem already?

Those page numbers are running across the screen! Yuck!

This call for a little modification to the code. Instead of printing the link to each and every page we will just saying something like "Viewing page 4 of 99 pages".

Than means we havel remove these code :

<?php
// ... the previous code

$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

// ... the rest here
?>

And then modify this one

<?php
// ...

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// ...
?>

 

Into this

<?php
// ...

// print the navigation link
echo $first . $prev .
" Showing page $pageNum of $maxPage pages " . $next . $last;

// ...
?>
 

Source: http://www.php-mysql-tutorial....paging.php

When there's more than one column involved in paging there isn't much that we need to modify. We only need to decide how to count the total number of rows we have in the table. Consider the student table. This table have five columns as shown in the SQL below.

Source : examples/source/student.sql

CREATE TABLE student(
   id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(30) NOT NULL,
   address VARCHAR(50) NOT NULL,
   age TINYINT UNSIGNED NOT NULL,
   register_date DATE NOT NULL,

   PRIMARY KEY (id)
);

  

In the select query we just select all the columns. You can also use SELECT * instead of mentioning all the column names ( SELECT id, name, address, age, register_date ). But personally i prefer writing the column names in the query so that by reading the code i know what the column names in a table without having to check the database.

Example: paging4.php
Source code :paging4.phps

<?php
include 'library/config.php';
include 'library/opendb.php';

// how many rows to show per page
$rowsPerPage = 3;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT id, name, address, age, register_date
          FROM student
          LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

// print the student info in table
echo '<table border="1"><tr><td>Student Id</td><td>Name</td><td>Address</td><td>Age</td><td>Register Date</td></tr>';
while(list($id, $name, $address, $age, $regdate) = mysql_fetch_array($result))
{
echo "<tr><td>$id</td><td>$name</td><td>$address</td>
<td>$age</td><td>$regdate</td></tr>";
}
echo '</table>';
echo '<br>';

// ... more code here
?>


In this example we print the result in table. Before looping through the array we just echo the starting table code and the header which displays the column names. Then in the loop we just print the values in a HTML table row.

The next thing is finding out the total number of rows. There are several ways to do it. The first one is shown below. It's the same method used in previous examples. We just use the COUNT() function

Example: paging4.php
Source code :paging4.phps

<?php
// ... previous code here

// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM student";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// ... just the same code that prints the prev & next link
?>


You can also count any other columns since they all yield the same result. So your query can be rewritten into this :

<?php
// ...
$query = "SELECT COUNT(name) AS numrows FROM student";
// ...
?>


Or this :

<?php
// ...
$query = "SELECT COUNT(age) AS numrows FROM student";
// ...
?>


There is another way to count the total rows. Instead of using COUNT() function in the query you use a simple SELECT <column> and use myql_num_rows() to see how many rows returned.

Take a look at the code below. We now separate the query into two parts. One is the normal SELECT query and the second is the SQL that performs the paging. After finish printing the result you can reuse the first part of the query to find the total number of rows.

Example: paging5.php
Source code :paging5.phps

<?php
// ... same old code to get the page number and counting the offset

$query = "SELECT id, name, address, age, register_date
          FROM student ";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');

// ... the code that prints the result in a table

// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed');
$numrows = mysql_num_rows($result);

// ... and here is the code that print the prev & next links
?>


  

There is another advantage in separating the original query with the paging query. In case you only wish to list all student whose age is older than 15. You just need to modify the original query and you don't have to worry about changing the query to find the total number of rows. The example is shown below :

<?php
// ... same old code to get the page number and counting the offset

$query = "SELECT id, name, address, age, register_date
          FROM student
          WHERE age > 15";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');

// ... the code that prints the result in a table

// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed');
$numrows = mysql_num_rows($result);

// ... and here is the code that print the prev & next links
?>


The disadvantage of this method is that the second execution of mysql_query() will retrieve all columns from the database. This is very useless since we're not going to use them. We only interested in finding the total rows returned by that query. In the end it's really up to you to decide which method you prefer.

Source: http://www.php-mysql-tutorial....ging-2.php