MySQL data organisation

Using query strings you can allow the user to change the ordering of the results from a database.

$order = 'id';
if($_GET['order']) $order = $_GET['order'];

Here we get the input variable $_GET['order'] if is has been set.

mysql_connect('localhost','username','password');
mysql_select_db('db');
$query = mysql_query("SELECT * FROM table ORDER BY $order");
echo '<table width="600">'; echo '<tr>';
echo '<th><a href="?order=id">ID</a></th>'
echo '<th><a href="?order=title">Title</a></th>';
echo '<th><a href="?order=description">Description</a></th>';
echo '<th><a href="?order=date">Date</a></th>';
echo '</tr>';

A connection to the database is made and the beginning of the table is output. Note how the headers for the table are links that give the ordering that is input into the database query as the order.

while ($output = mysql_fetch_assoc($query))
{
   echo '<tr>';
   echo '<td>'.$output['id'].'</td>';
   echo '<td>'.$output['title'].'</td>';
   echo '<td>'.$output['description'].'</td>';
   echo '<td>'.$output['date'].'</td>';
   echo '</tr>';
}
echo '</table>';

This loop outputs all the data in the table and ends the table element.

The technique above means that you have to manually print each column and add links to each column. We will now automate the process by creating an array of the fields we want to be able to order.

$sort = array('id', 'title', 'subject', 'date');

This array holds all the table fields that we will order the results by.

$order = $sort[0];
if(in_array($_GET['order'], $sort)) $order = $_GET['order'];
mysql_connect('localhost','username','password');
mysql_select_db('db');
$query = mysql_query('SELECT '.implode(',', $sort)." FROM table ORDER BY $order");
echo '<table width="600">';
echo '<tr>';

Again we use the query string as an input and check to see if it is contained within the array, if it is then we will use it otherwise we will pick the first item in the array. Note how the query is bound to the array, only the data items specified in the array are returned from the database.

foreach($sort as $arr)
   echo '<th><a href="?order='.$arr.'">'.$arr.'</a></th>';
echo '</tr>';

The table headers are output using the array we created earlier, the links are also automatically generated.

while ($output = mysql_fetch_assoc($query))
{
   echo '<tr>';

   foreach($sort as $arr)
      echo '<td>'.$output[$arr].'</td>'; 
   echo '</tr>';
}
echo '</table>';

Finally there are two nested loops. One to get the data from the database and the inner foreach loop that prints the data in columns.

Now that the user can choose which column to order and we can also allow the user to specify whether the order should be ascending or descending.

$order = 'id';
$order2 = 'asc';
if(($_GET['order']) $order = $_GET['order'];
if($_GET['order2'] == 'desc') $order2 = 'desc';

This code is similar to the first however a new variable has been created to control how the chosen field is ordered.

$query = mysql_query("SELECT * FROM table ORDER BY $order $order2");

The query is slightly different as it now includes the new variable to determine the orientation of the ordering.

echo '<table width="600">';
echo '<tr>';
echo '<th>ID <a href="?order=id&order2=asc">asc</a> <a href="?order=id&order2=desc">desc</a></th>';
echo '<th>Title <a href="?order=title&order2=asc">asc</a> <a href="?order=title&order2=desc">desc</a></th>';
echo '<th>Description <a href="?order=description&order2=asc">asc</a> <a href="?order=description&order2=desc">desc</a></th>';
echo '<th>Date <a href="?order=date&order2=asc">asc</a> <a href="?order=date&order2=desc">desc</a></th>';
echo '</tr>';

The table headers are output and two links are now in place, one for ordering the results by ascending field and one for descending field.

As before we can automate this process so that we only state what fields we want to order.

echo '<table width="600">';
echo '<tr>';
foreach($sort as $arr)
   echo '<th>'.$arr.' <a href="?order='.$arr.'&amp;order2=asc">up</a> <a href="?order='.$arr.'&order2=desc">down</a></th>';
echo '</tr>';

This code is similar to that before however the two new links are in place.