Multiple Pages With Php

Ever wondered how the search engines split your results up into those tidy pages with the "next" and "previous" links? Ever had a huge list of results you've wanted to make more organised? This article will show you how.

The Benefits

The first thing to consider when designing a website is user-friendliness. If you visit a website that's difficult to use, poorly constructed and you can't get to where you want to go fast -- what do you do? You hit that little "X" in the top-right corner, and that's exactly what visitors to your website will do if you don't take the time to make it easy to use. Splitting your results up into smaller, more managable chunks will make it easier for your visitors to browse through them.

OK, What Do I Need?

We're going to use PHP to query a MySQL database, manage the results and print a pre-defined limit of results onto each of the multiple pages. It'd be handy if you had a basic grasp of PHP and SQL -- if not visit the official PHP website and have a look at their tutorials. Experience with using PHP to interact with and handle data retrieved from a MySQL database is essential for you to understand this article. If you don't know what i'm talking about, go learn it; it's where web development is going. Oh, access to a PHP interpreter and a MySQL database would come in handy also.

Let's Do It

Database Connection

Ok firstly let's connect to your database. Obviously you would put your correct information in here. See the below code:

<?
$db_addr = 'localhost';		// address of MySQL server.
$db_user = 'user';		// Username to access server.
$db_pass = 'password';		// Password access server.
$db_name = 'MyDatabase';	// Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");
if (!($connect)) // If no connect, error and exit().
{
     echo("<p>Unable to connect to the database server.</p>");
     exit();
}
if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
     echo("<p>Unable to locate the $db_name database.</p>");
     exit();
}

If you don't understand the above maybe you should stop now and read a basic PHP tutorial. Basically, all it does is use your information to try and make a connection to the database, if a connection to the database server cannot be made, it errors and exits. If the specified database cannot be found, it errors and exits.

Define Some Variables

Now we need to define some variables, sort out how many results we want per-page and construct our database query, look at the code below, then i'll explain:

if (!($limit)){
     $limit = 10;} // Default results per-page.
if (!($page)){
     $page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM your_table WHERE name LIKE '%". $query ."%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
     echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
     exit();}

Now we have some variables to work with. If $limit is not already specified (for example in the query string) then it will have the value of 10. If $page is not already specified in the query string it will be set to 0. $limit is used to control the number of results per-page, we are going to add to this and allow the user to control it. $page is used to let the script know which page, which chunk of results to show on each given page. Now we have the limit per-page, and the number of results that need to be displayed. Bear with me this will all become clearer soon.

The Math

Now we need to calculate the number of results pages there will be. Say we have 35 results, and we want to split them into 10 per-page, there will be 4 results pages. We're also going to add some visual fancies, take a look at the code:

$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.
if ($numrows % $limit) {
$pages++;} // has remainder so add one page
$current = ($page/$limit) + 1; // Current page number.
if (($pages < 1)    ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.
else {
$total = $pages;} // Else total pages is $pages value.
$first = $page + 1; // The first result.
if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.
 
else{
$last = $numrows;} // If last results page, last result equals total number of results.

OK let's slow down and have a look at what we have done so far:

Visual Tid-Bits

Now let's write the code that'll show the user what page they're on and what set of results they're viewing, see the code:

//escape from PHP mode.
?>
<html>
<head>
<title>Search Results for <?=$query?></title>
</head>
<body>
<center><h2>Search Results for <?=$query?></h2></center>
<table width="100%" border="0">
 <tr>
  <td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
  </td>
  <td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
&nbsp;
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
  </td>
 </tr>
</table>
<?
//Go back into PHP mode.

Now we have the start of the results page that the user will actually see. Let's say again that the query returned 35 results, this bit of code would display at the top of the page:

Displaying The Results

Now we're going to show the pre-defined limit of results on the current page, see the below code:

// Now we can display results.
$results = mysql_query("SELECT * FROM your_table WHERE name LIKE '%". $query ."%' ORDER BY name ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{
?>
<p><a href="<?=$data["url"]?>" title="<?=$data["name"]?>"><?=$data["name"]?></a> - <?=$data["description"]?></p>
<?
}

Notice the MySQL query, we've modified it alittle. Instead of just selecting all the rows, we select them, order them alphabetically, limit them according to the relevant page and then display each row from the query. Bear in mind, this is an hypothetical situation. Your database table may not contain a url, name or description column, i am just improvising, i'm sure you can edit the script to meet your own needs and database. Read on....

Linking To Other Pages

Now the final part of the code, linking to each of the results pages so the user can browse through them easily, see the below code:

?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    
");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b>
");} // If current page don't give link, just text.
 else{
 echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> 
");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>");}
?>
</p>
</body>
</html>

The above code checks to see if the current page is the first page. If it is, it bypasses the "previous" link. It then loops through each page and provides a link to them, except if the link number is that of the current page, then it displays a text number. It then checks to see if the current page is the last page, if it is, it bypasses the "next" link.

OK there we have it, a simple script that will query the database, find the number of rows affected by the query, calculate the number of pages, display to the user where they are amongst the results and provide links to the other results pages, easier than you thought, eh? Let's have a look at the full code.

The Finished Script

<?
$db_addr = 'localhost';		// address of MySQL server.
$db_user = 'user';		// Username to access server.
$db_pass = 'password';		// Password access server.
$db_name = 'MyDatabase';	// Name of database to connect to.
$connect = @mysql_connect("$db_addr", "$db_user", "$db_pass");
if (!($connect)) // If no connect, error and exit().
{
echo("<p>Unable to connect to the database server.</p>");
exit();
}
if (!(@mysql_select_db($db_name))) // If can't connect to database, error and exit().
{
echo("<p>Unable to locate the $db_name database.</p>");
exit();
}
if (!($limit)){
$limit = 10;} // Default results per-page.
if (!($page)){
$page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM your_table WHERE name LIKE '%". $query ."%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
exit();}
$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.
if ($numrows%$limit) {
$pages++;} // has remainder so add one page
$current = ($page/$limit) + 1; // Current page number.
if (($pages < 1)    ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.
else {
$total = $pages;} // Else total pages is $pages value.
$first = $page + 1; // The first result.
if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.
 
else{
$last = $numrows;} // If last results page, last result equals total number of results.
//escape from PHP mode.
?>
<html>
<head>
<title>Search Results for <?=$query?></title>
</head>
<body>
<center><h2>Search Results for <?=$query?></h2></center>
<table width="100%" border="0">
 <tr>
  <td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
  </td>
  <td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
&nbsp;
  </td>
 </tr>
 <tr>
  <td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a>   <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
  </td>
 </tr>
</table>
<?
//Go back into PHP mode.
// Now we can display results.
$results = mysql_query("SELECT * FROM your_table WHERE name LIKE '%". $query ."%' ORDER BY name ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{
?>
<p><a href="<?=$data["url"]?>" title="<?=$data["name"]?>"><?=$data["name"]?></a> - <?=$data["description"]?></p>
<?
}
?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    
");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
 $ppage = $limit*($i - 1);
 if ($ppage == $page){
 echo("<b>$i</b> 
");} // If current page don't give link, just text.
 else{
 echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> 
");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>
");}
?>
</p>
</body>
</html>

So This Is Just a Simple Search Script?

Absolutely not. This script could perform a myriad of uses. Say you had a "downloads" table in your database containing a list of programs. You want to display just the "programming" related software to your user but there are many programs in this category which would generate a huge page of links. With a slight modification to the database query you could display these programs in neat multiple pages which are user-friendly and easily navigable for your visitor. Many sites these days are built around a search-type script, give it a try for yourself.

Expandable

Why not see what ideas you can come up with? How about links on the page that allow your visitor to control how the results are ordered, by name, by date added to the database, by id? How about allowing them to reverse the listing? How about adding to the script to allow them different search actions, exact match or a vague match? The list is endless. Stick a conditional in the script, if the $query variable exists run the above script. If not display a form allowing you to enter a query, so the $query variable contains data and the script is run. This isn't a ready-run-script, it will take some modifiying. Play around!

Bibliography

If you wish to get deeper into this ever-popular partnership between PHP and MySQL, visit a couple of sites: