Page 1 of 1
Sorting a result set by column name
Posted: Thu Jun 13, 2002 12:31 pm
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
Posted: Thu Jun 13, 2002 9:16 pm
by Peter
You can use the
array_reverse() to reverse the order of your elements.
Posted: Thu Jun 13, 2002 11:35 pm
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.
Posted: Fri Jun 14, 2002 2:57 am
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.
Posted: Fri Jun 14, 2002 3:51 am
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")) {
if (mysql_num_rows($result) > 0) {
echo '<table>
<tr>';
$fieldcount = mysql_num_fields($result);
for ($x = 0; $x < $fieldcount; $x++) {
$neworder = $order;
$fieldname = mysql_field_name($result, $x);
$imgstr = "";
$neworder = "asc";
if ($fieldname == $field && $order == "asc") {
$neworder = "desc";
} else if ($fieldname == $field && $order == "desc") {
$neworder = "asc";
}
echo '<td><a href="mypage.php?field='.$fieldname.'&order='.$neworder.'><u>'.$fieldname.'</u></a></td>';
}
echo '</tr>';
while ($row = mysql_fetch_assoc($result)) {
// print your result set here...
}
echo '</table>';
?>