Sorting Table Columns in DESCENDING Order

[geosite]

I just got a great PHP script that allows visitors to sort columns on my database tables. The only problem is that it only sorts in one direction.

Can the script below be modified so that if visitors click a link, it will sort in ascending order, and if they click it a second time it sorts in DESCENDING order, similar to the JavaScript function I'm replacing?

Also, is there a way to sort several tables together, similar to an Excel spreadsheet? In other words, a visitor could sort Column 1 first, then choose Column 3 as a secondary sort.

Thanks.

<?php
//page.php
switch($_GET['order'])
{
   case 'age':
       $order = 'Age0';
       break;
   case 'pop':
       $order = 'Pop';
       break;
   case 'nam':
   default:
       $order = 'Name';
       break;
}

$res = mysql_query('SELECT * FROM cia_people ORDER BY ' .$order);
?>
<table>
  <tr>
     <th><a href="index2.php?order=nam">Name</a></th>
     <th><a href="index2.php?order=pop">Population</a></th>
     <th><a href="index2.php?order=age">Age</a></th>
  </tr>
<?
while($row = mysql_fetch_assoc($res))
{
echo "<tr><td>" . $row['Name'] . "</td><td>" . $row['Pop'] . "</td><td>" . $row['Age0'] . "</td></tr>"; 
} 
?>

[arborint]

You probably want to change from links to a form. Then you can select the sort field (or 1st,2nd,.. sort) and also click a radio button for ascending or descending.

[geosite]

You probably want to change from links to a form. Then you can select the sort field (or 1st,2nd,.. sort) and also click a radio button for ascending or descending.

OK, I understand, though I've scarcely touched forms. Would anyone know of any scripts like this already in use that I can use as a guide?

[arborint]

<form action="index2.php" method="post">
<select name="order">
<option value="1">Age</option>
<option value="2">Population</option>
<option value="3">Name</option>
</select>
<input type="radio" name="direction" value="0">Ascending
<input type="radio" name="direction" value="1">Descending
<input type="submit" name="submit" value="Submit">
</form>

switch(intval($_REQUEST['order']))
{
   case 1:
       $order = 'Age0';
       break;
   case 2:
       $order = 'Pop';
       break;
   case 3:
   default:
       $order = 'Name';
       break;
}
if (intval($_REQUEST['direction'])) {
    $direction = ' DESC';
} else {
    $direction = '';
}
$res = mysql_query('SELECT * FROM cia_people ORDER BY ' . $order . $direction); 

[geosite]

Wow, that's an exceptionally cool script. Thanks!

One more question - does anyone know how I can add a choice to return to the table's default setting? When I tri to refresh the page, I get that ubiquitous warning about resending POSTDATA, or something like that.

[augathra]

POST will do that, if your form doesn't INSERT data into your DB, GET should work...method="GET"

[geosite]

POST will do that, if your form doesn't INSERT data into your DB, GET should work...method="GET"

OK, I changed POST to GET, so it now looks like this...

<form action="index4.php" method="GET">
<select name="order">
<option value="1">Age</option>
<option value="2">Population</option>
<option value="3">Name</option>
</select>
<input type="radio" name="direction" value="0">Ascending
<input type="radio" name="direction" value="1">Descending
<input type="submit" name="submit" value="Submit">
</form> 

I no longer get the warning message about resending POST data. However, the table doesn't return to its default settings. Is there a way to do that?

I just thought of another thing that would make this script even more useful. Is there a way to add a sort function for an item that's in the database but isn't displayed on the page?

For example, this table displays each country's name, population and the average age of its citizens. Suppose there's another column in the database for capitals. Could I then rig it so that first column - names of countries - sorts according to each country's capital?

[gcioffi]

I know this is probably too late. But I'm just getting into PHP and MySQL and found this code to be very helpful. Also, a simple work around I used for resetting the page was creating a simple link <p><a href="nameofpage.php">Default Settings</a></p> that reloads the page. It's not fancy, but it works.