PHP Multi-Page MySQL Results

If you use any search engine, they will all limit the number of results per page. Some searches can return millions of pages! Now what about your site? Do you have any pages that return more than 30 items? You can easily break that down into 10 items per page.

In this example, I will show you how to return a results page with PHP, using a MySQL database. This script will return the entire table, with 10 items per page, and it will generate the appropriate previous and next links.

The MySQL Table
This code assumes you have a table named directory with “ID”, “URL”, and “Title” fields.

The Code
[code type=php]<?
if ($page < 1) {
$page = 0;
} else {
$links[] = "<a href=’directory.php?page=".($page-1)."’>Previous</a>";
}

$global_dbh = mysql_connect("localhost","username","password");
mysql_select_db("mydatabasename", $global_dbh);

$query = "SELECT * FROM `directory` ORDER BY `ID` DESC LIMIT ".($page*10).", 11";
$result = mysql_query($query, $global_dbh);
$cnt = mysql_num_rows($result);

if ($cnt > 10) {
$cnt = 10;
$links[] = "<a href=’directory.php?page=".($page+1)."’>Next</a>";
}

echo @implode(" | ",$links);

for ($i=0; $i<$cnt; $i++) {
$row = mysql_fetch_array($result);
echo "<a href='{$row["URL"]}’>{$row["Title"]}</a><P>";
}

echo @implode(" | ",$links);
?>[/code]

The Explaination
In this code example it is assumed you have already connected to your database previously in the script, with $global_dbh

Lines 2-6: If $page is less than 1, make sure it is set to 0 to prevent negative input. Otherwise create a previous link to go back.

Lines 8-10: Here we select the database information. We start at the current page * 10, skipping over previous pages. Notice we are going to select 11 entries – if it returns 11 entries, we know to add a next page link.

Lines 12-15: Check if the returned results is greater than 10, if it is – set the counter to 10 (so we only get 10 results) and create the next page link.

Lines 17,24: Here we output the links to the browser. It looks nice having “Previous | Next”, but ugly if it was just “Previous |” or “| Next”. Using the implode function (with an @ in case there is only one link) we get the “|” only if there are two links.

Lines 19-22: Simply outputing the table rows.