help needed with sorting databse columns

[bump909]

Hi,

I've searched this forum for scripts for an administrator to sort various rows of data from a database (e.g, quotes generated by users) but all seem outdated, and when I did find one it was very buggy. (Maybe I haven't looked hard enough).

I would be very grateful if someone could either provide or point to an upto date script, i.e with Register Globals Off, that will order all columns if the title of that column is clicked. A simple analogy is windows explorer - click 'type' and all files are ordered by type, click 'date' and all entries are ordered by date (descending), click date again and all columns are ordered by date (Ascending).

I hope I'm not asking for much here, but I figure hundreds of people must have written or used such a script.

Any upto date scripts/pointers would be appreciated.

Thanks,
Karmakoza
http://www.21stsentry.net

[LordShryku]

Here's a bastard mysql version, destined to be torn apart by the board. Completely untested, and of course, you'll have to fill in the actual field names and so forth.

<?php
# Current URL
$url = empty($_SERVER['QUERY_STRING']) ? $_SERVER['PHP_SELF'] : $_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING'];

# Sort Field
if(isset($_GET['sort'])) {
   $url = str_replace('&sort='.$_GET['sort'], '', $url);
   switch($_GET['sort']) {
      case 'a': $sort = 'field_1';    break;
      case 'b': $sort = 'field_2';    break;
      case 'c': $sort = 'blah_field'; break;
      default:  $sort = 'field_1';    break;
   }
}
else {
   $sort = 'field_1';
}

#ASC or DESC
if(isset($_GET['dir'])) {
   $dir = 'DESC';
   $url = str_replace('&dir='.$_GET['dir'], '', $url);
}
else {
   $dir  = 'ASC';
   $url .= '&amp;amp;dir=d';
}

# Make URL XHTML compatible
$url = str_replace('&', '&amp;amp;', $url);

# Connect, Perform Query
$conn = mysql_connect('server', 'user', 'pass');
        mysql_select_db('db', $conn);

$sql  = "SELECT field_1, field_2, blah_field
           FROM table
          WHERE something = 'something'
       ORDER BY ".$sort.' '.$dir;
$res  = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) > 0) {
   # We have rows, make the table
?>
<div class="container">
  <table cellspacing="0">
    <tr>
      <th><a href="<?php echo $url; ?>&amp;amp;sort=a" title="Sort By Field 1">Field 1</a></th>
      <th><a href="<?php echo $url; ?>&amp;amp;sort=a" title="Sort By Field 2">Field 2</a></th>
      <th><a href="<?php echo $url; ?>&amp;amp;sort=a" title="Sort By Blah Field">Blah Field</a></th>
    </tr>
<?php
   # Display Results
   while($row = mysql_fetch_array($res)) {
?>
    <tr>
      <td><?php echo empty($row['field_1']) ? '&amp;nbsp;' : $row['field_1']?></td>
      <td><?php echo empty($row['field_2']) ? '&amp;nbsp;' : $row['field_2']?></td>
      <td><?php echo empty($row['blah_field']) ? '&amp;nbsp;' : $row['blah_field']?></td>
    </tr>
<?php
   }
?>
  </table>
</div>
<?php
}
else {
   # Default - No rows returned
   echo '<p>No rows found</p>';
}
mysql_close($conn);
?>

[bump909]

Thanks LordShryku

That worked a treat. I did alter some of the code to suit my app but it really did give me some extra useful tools I did not know about. I did spot one error though. Where you wrote:

?>
<div class="container">
<table cellspacing="0">
<tr>
<th><a href="<?php echo $url; ?>&amp;sort=a" title="Sort By Field 1">Field 1</a></th>
<th><a href="<?php echo $url; ?>&amp;sort=a" title="Sort By Field 2">Field 2</a></th>
<th><a href="<?php echo $url; ?>&amp;sort=a" title="Sort By Blah Field">Blah Field</a></th>
</tr>
<?php

I changed the values of the sort=a to sort=b and sort=c. In the last 2 instances.
All in all, a great script.