Creating A Simple Data Browser With PHP and MySQL

Creating A Simple Data Browser With PHP and MySQL - Part 2

In my last post, I’ve shown how to build a simple PHP / MySQL data browser. Today we’re going to take this PHP tutorial one step further by adding a page navigator.

First, create an array that will contain all parameters sent through GET and a variable through which you will set the number of records to show per page. Insert this at the beginning of the script, before $dbname = ‘your db name’:

$params = array();
$recperpage = 5;

Then, add the following code at the very end of the script, after </html>. This is the function that will validate the values sent through GET:

<?php

function GetFromGet()
{
global $params, $nbpages;

$params[’pageno’] = $_GET[’pageno’];

if($params[’pageno’] < 1 || $params[’pageno’] > $nbpages)
$params[’pageno’] = 1;
}

?>

Note that this function will do more than that eventually.

Now we need to find out how many pages there are going to be. To do this, we will query the database and find out how many records there are in our “artist” table. Then we’re going to call the GetFromGet() function so that the page number can be validated according to the maximum number of pages available. When the page number has been validated, we can set the first to appear within this page. Insert this code before $sql = “SELECT…”:

// FIND OUT THE NUMBER OF RECORDS IN OUR DATABASE
$sql = “SELECT count(artist_name) as nbrec FROM artist;”;
$result = mysql_query($sql);

// SET THE NUMBER OF PAGES AVAILABLE
$nbpages = ceil(mysql_result($result, 0, ‘nbrec’) / $recperpage);
GetFromGet();

// SET THE FIRST RECORD TO BE DISPLAYED
$firstrec = ($params[’pageno’] * $recperpage) - $recperpage;

You also need to add a function that will generate the HTML code for the page navigator right after the GetFromGet() function:

function BuildNavigator()
{
global $params, $nbpages;

$navigator = ”;

for($i=1;$i<=$nbpages;$i++)
{
  if($i != $params[’pageno’])
   $navigator .= ‘<a href=”index2.php?pageno=’ . $i . ‘”>’ . $i . ‘</a>’;
  else
   $navigator .= “<strong>$i</strong>”;

  if($i < $nbpages)
   $navigator .= ” | “;
}

echo $navigator;
}

Then we’re going to add another row to the bottom of the table to hold the page navigator and call the BuildNavigator() function. Insert the following code just before the </table> tag.

<tr id=”navigator”>
<td colspan=”2″><?php BuildNavigator(); ?></td>
</tr>

We’re almost done, now we need to alter the SQL query to limit the record selection according to the page number the visitor is browsing and the number of records per page. Change this line:

$sql = “SELECT artist.artist_name, genre.genre_desc ” .
”FROM artist INNER JOIN genre ON artist.genre_id = genre.genre_id ” .
”ORDER BY artist.artist_name;”;

by this:

$sql = “SELECT artist.artist_name, genre.genre_desc ” .
   ”FROM artist INNER JOIN genre ON artist.genre_id = genre.genre_id ” .
   ”ORDER BY artist.artist_name ” .
   ”LIMIT $firstrec, $recperpage;”;

Of course you could make a nicer page navigator by adding first, previous, next, last links by modifying the BuildNavigator() function.

Stay tuned for my next post in which I will explain how to add sorting capabilities to our data browser.

See this PHP script in action here.

Download the source code for this PHP tutorial here.

Share This Post