Reorder the results of a mySQL query using PHP without performing another query

Question

I don't know if this is possible. I display a table of results and want to be able to order the table by any of the columns. I don't need help making such a table; I just need information about reordering the results of a query without repeating it with a different ORDER BY instruction.

Answer 1

Perhaps you could store the results in some sort of array.

EDIT: Otherwise you can use JavaScript to sort the table without having to refresh the page. Here is a jQuery plugin that can do this for you.

Answer 2

I would probably do this with some sort of client-side script, I know there are several JavaScript or DHTML options out there. These would be executed much more quickly for your users (on most machines, anyway) and avoid unnecessary hits to the database. I think the last one I looked at was this: http://www.kryogenix.org/code/browser/sorttable/

But if you're really interested in doing it with your SQL query...so you have your table with results, something similar to:

<table>
<tr>
<th><a href='page.php?orderby=name'></th>
<th><a href='page.php?orderby=blah'></th>
....
</table>

Then when constructing the query, you could simply do something like:

<?php
$sql = "select * from sometable";
switch ( $_REQUEST['orderby'] ) {
    case "name": $sql .= " order by name"; break;
    case "blah": $sql .= " order by blah"; break;
    default: $sql .= " order by whatevercolumnyouwantasdefault";
}
...
?>

Or even something as simple as this, assuming you've checked the $_REQUEST values and determined they are legitimate:

<?php
$sql = "select * from sometable";
if ( isset($_REQUEST['orderby']) )
    $sql .= " order by ".$_REQUEST['orderby'];
else
    $sql .= " order by whatevercolumnyouwantasdefault";
?>