Sorting a result set by column name

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Spinball
Forum Newbie
Posts: 6
Joined: Thu Jun 13, 2002 12:23 pm

Sorting a result set by column name

Post by Spinball »

Hi folks,
I've seen one answer to my question here already but I'm looking for a more efficient way.
I want to present a table of data and have the column header clickable to resort the data by that column.
Except I don't want to re-SELECT the data from the database as this could potentially be a complex search.
A more efficient way must be to resort and redisplay the result set from the initial SELECT.
Is there an easy way to do this?
I appreciate this will probably end up being an ARRAY type answer.
Thanks
Peter
Forum Commoner
Posts: 28
Joined: Mon Jun 10, 2002 12:40 am
Location: Brisbane, Australia

Post by Peter »

You can use the array_reverse() to reverse the order of your elements.
User avatar
roninblade
Forum Newbie
Posts: 21
Joined: Thu Jun 13, 2002 7:12 pm

Post by roninblade »

first off, to sort the result set in a different way cannot be done without doing a page refresh unless you save the result set in an array and pass it in the same page.

now, if you want dont want to refresh the page you have to save the result set in a javascript array and print the table with a new column order.

IMO, this is much more complicated unless you *really* know your javascript. i'd recommend doing a search, its much more easier than arranging a multidimensional array.
Spinball
Forum Newbie
Posts: 6
Joined: Thu Jun 13, 2002 12:23 pm

Post by Spinball »

Thanks guys. Array reverse wouldn't help as I'd want to sort the array by a different column.
And it's not a page refresh I have a problem with, it's doing another SELECT. Of course I appreciate that I'm going to have to call PHP_SELF, but I can pass the result set and a column name or number to re-sort the array by before redisplaying it.
User avatar
roninblade
Forum Newbie
Posts: 21
Joined: Thu Jun 13, 2002 7:12 pm

Post by roninblade »

i found this lying around in my harddrive. it's an example of what you want, i hope this helps you

Code: Select all

<?php
  if (!isset($order)) $order = "asc";
  if (!isset($field)) $field = "primary_key";

  if ($result = mysql_query("select * from $your_table order by $field $order")) &#123;
  if (mysql_num_rows($result) > 0) &#123;
  echo '<table>
        <tr>';
  $fieldcount = mysql_num_fields($result);
  for ($x = 0; $x < $fieldcount; $x++) &#123;
    $neworder = $order;
    $fieldname = mysql_field_name($result, $x);

    $imgstr = "";
    $neworder = "asc";
    if ($fieldname == $field && $order == "asc") &#123;
      $neworder = "desc";
    &#125; else if ($fieldname == $field && $order == "desc") &#123;
      $neworder = "asc";
    &#125;

    echo '<td><a href="mypage.php?field='.$fieldname.'&order='.$neworder.'><u>'.$fieldname.'</u></a></td>';
  &#125;
  echo '</tr>';
  while ($row = mysql_fetch_assoc($result)) &#123;
    // print your result set here...
  &#125;
  echo '</table>';
?>
Post Reply