Creating A Simple Data Browser With PHP and MySQL

Creating A Simple Data Browser With PHP and MySQL – Part 3

Now that you have a PHP / MySQL data browser with a page navigator, we’re going to add sorting capabilities. We want to allow the visitors to click on a column header in order to sort it as they want.

For the last part of this tutorial, I’ve split the main script (from part 2) into three files:

the main PHP script which will be accessed by the visitor (index3.php)
a CSS style sheet called through index3.php
functions.php which will contain all of our functions

I’ve also created a function to connect to the database. This makes for a much clearer script.

Adding Sorting Capability

To be able to sort our data, we will need two additional parameters: the sorting field and sorting order. We will store these values into $params['sortby'] and $params['order']. To validate the possible values of these parameters, we will modify the GetFromGet() function:

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

$params['pageno'] = $_GET['pageno'];
$params['sortby'] = $_GET['sortby'];
$params['order'] = $_GET['order'];

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

if(!in_array($params['sortby'], $fields) || $params['sortby'] == ”)
  $params['sortby'] = $fields[0];

if($params['order'] != ‘asc’ && $params['order'] != ‘desc’)
  $params['order'] = ‘asc’;
}

Remember that it’s important to always validate user inputs. Never trust what parameters your visitors sends to your scripts. You could end up having huge security issues.

Now we’re going to remove the static column headers so that they will be generated dynamically. Remove this part from the main script:

<tr>
<th>Artist</th>
<th>Genre</th>
</tr>

and replace it by:

<? BuildColumnHeaders(); ?>

At this point, this is what your data browser table code should be like (index3.php):

<table width=”400″ cellspacing=”0″ cellpadding=”3″ border=”0″>
<?php

// SET THE NUMBER OF PAGES AVAILABLE
$nbpages = ceil(GetNbRec() / $recperpage);

// READ PARAMETER VALUES PASSED THROUGH ‘GET’
GetFromGet();

// BUILDING COLUMN HEADERS AND SORTERS
BuildColumnHeaders();

// SET THE FIRST AND LAST RECORD TO BE DISPLAYED
$firstrec = ($params['pageno'] * $recperpage) – $recperpage;

// Build the data browser
BuildDataBrowser($firstrec, $recperpage);
?>
<tr id=”navigator”>
<td colspan=”2″><?php BuildNavigator(); ?></td>
</tr>
</table>

Now add this function to the functions.php file:

function BuildColumnHeaders()
{
global $params, $fields, $header_titles;

$headers = “<tr>”;

$i=0;

foreach($fields as $colheader)
{
  if($params['sortby'] == $colheader)
  {
   if($params['order'] == ‘asc’)
    $order = ‘desc’;
   else
    $order = ‘asc’;
  }
  else
   $order = ‘asc’;

  $headers .= ‘<th><a href=”index3.php?pageno=’ . $params['pageno'] . ‘&sortby=’ . $colheader . ‘&order=’ . $order . ‘”>’;
  $headers .= $header_titles[$i] . “</a></th>”;
  $i++;
}

$headers .= “</tr>”;

echo $headers;
}

You also need to modify the BuildDataBrowser() function so that the sorting order is applied to the SQL query:

function BuildDataBrowser($firstrec, $recperpage)
{
global $params;

$sql = “SELECT artist.artist_name, genre.genre_desc ” .
   ”FROM artist INNER JOIN genre ON artist.genre_id = genre.genre_id ” .
   “ORDER BY ” . $params['sortby'] . ” ” . $params['order'] .
   ” LIMIT $firstrec, $recperpage;”;
$result = mysql_query($sql);

$html = ”;
$rowclass = “row”;

while($row = mysql_fetch_array($result))
{
  $html .= “<tr class=\”$rowclass\”>”;
  $html .= ‘<td valign=”top”>’ . $row['artist_name'] . “</td>\n”;
  $html .= ‘<td valign=”top”>’ . $row['genre_desc'] . “</td>\n”;
  $html .= “</tr>”;

  if($rowclass == “row”)
   $rowclass = “altrow”;
  else
   $rowclass = “row”;
}

echo $html;
}