[RESOLVED] Sort (ASC / DESC) Of Column Returns From MySQL

macmonkey

As posted in a previos thread - I've got a "survey_results.php" in the form of:

EXAMPLE
/////////////////////////////////////////////////////////////////////////////////////////////////
/// ID /// your_name /// club_name /// city /// state /// danceability ---- etc.///
/////////////////////////////////////////////////////////////////////////////////////////////////
/// blah blah // jon doe /// doe club /// doeville /// TN /// 8 /// ------/////////////////
/// blah blah // jon doe /// doe club /// doeville /// TN /// 8 /// ------/////////////////
/// blah blah // jon doe /// doe club /// doeville /// TN /// 8 /// ------/////////////////
/// blah blah // jon doe /// doe club /// doeville /// TN /// 8 /// ------/////////////////
/// blah blah // jon doe /// doe club /// doeville /// TN /// 8 /// ------/////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////

I need to be able to allow user to sort results by ID, Your Name, Club Name etc....

I've seen before where there is a little up and down arrow that user can click next to each table heading.

Anyone have tips on how I might achieve this?

Thanks

macmonkey

Here is my php

<link rel="stylesheet" type="text/css" href="style.css">

<?php
// Make a MySQL Connection
require_once('db_connect.php');
mysql_select_db("marco_marco") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM survey_results ORDER BY 'id'")
or die(mysql_error());
echo "<div id='results'>";
echo "<table border='0'>";
echo "<tr> <th>Song</th> <th>Your Name</th> <th>Club Name</th> <th>City</th> <th>State</th> <th>Danceability</th> <th>Dance Participation</th> <th>Crowd Participation</th> <th>Rating</th> <th>Comments</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row['your_name'];
echo "</td><td>";
echo $row['club_name'];
echo "</td><td>";
echo $row['city'];
echo "</td><td>";
echo $row['state'];
echo "</td><td>";
echo $row['danceability'];
echo "</td><td>";
echo $row['dance_participation'];
echo "</td><td>";
echo $row['crowd_participation'];
echo "</td><td>";
echo $row['rating'];
echo "</td><td>";
echo $row['comments'];
echo "</td></tr>";
}
echo "</table>";
echo "</div>";
?>

Right Now I'm ordering by ID just to allow user to see song id groupings.

LoganK

Make a link on each of your table headers to something like "page.php?order=id" and "page.php?order=name".

Then, in your SQL query, replace "ORDER BY 'id'" with "ORDER BY '{$_GET['order']}'".

Keep in mind this is not the most secure option, as many gurus will probably come flaming to tell me so in about...10 minutes 😃 So you may want to sanitize it first and then put it into the SQL query.

Jasp182

I don't think I'd expose an insert point to my sql right there in the URL, but you could stuff your results into a multidimensional array and sort the array with usort and a comparison function. So, instead of allowing the user to stick a GET variable into your query, you just use this variable to tell the sort function which column to sort by.

You can build the array in your while() loop instead of building your html there. And then use a foreach loop to build your html after you've sorted your array. As far as the sorting, here's a really simple explanation of how to sort a multidimensional array with usort:
http://www.samspublishing.com/articles/article.asp?p=31840&seqNum=6&rl=1

LoganK

Jasp182 wrote:
I don't think I'd expose an insert point to my sql right there in the URL, but you could stuff your results into a multidimensional array and sort the array with usort and a comparison function. So, instead of allowing the user to stick a GET variable into your query, you just use this variable to tell the sort function which column to sort by.

I know that; that's why I told him to sanitize it. However, that is the most effecient way of doing it. If you really wanna be secure, use this:


switch ($_GET['order'])
{
     case "name":
          $order = $_GET['order'];
          break;
     case "age":
          $order = $_GET['order'];
          break;
... // And so and so forth, until
     case "id":
     default:
          $order = "id";
          break;
}

$sql  = "SELECT * FROM table ORDER BY $order";
$result = mysql_query($sql) or die("MySQL error: $sql<br />" . mysql_error());

while ($row = mysql_fetch_array($result))
{
     // DB data processing stuff goes here.
}

This way, it can only be what you put in the switch() statement; anything else will default to "id". You wanna be even more secure? Add "$orderBy = addslashes($GET['order']);" to the top and replace all references to $GET['order'] with $orderBy.

macmonkey

Awesome... that gives me plenty to work on. I'll investigate both methods and get back to you guys!

Thanks tons for your help.

macmonkey

You guys kick ass! I used the file.php?order=id method for now but I'm grasping the concept of the other which I'll work on later - keeping it for my examples file 😃.

Now this sorting works for ASC but what if for some reason I wanted to do DESC values? I'm hitting google right now.

Thanks again

Jasp182

Have two variables - one to store what the current sort field is and another to store whether the sort is ASC or DESC. If the field is not what is currently being sorted by, link to file.php?order=id;sort=<default sort for this column>. If the field is currently being sorted, link to file.php?order=id;sort=<opposite of sort variable>.

LoganK

Jasp182 wrote:
Have two variables - one to store what the current sort field is and another to store whether the sort is ASC or DESC. If the field is not what is currently being sorted by, link to file.php?order=id;sort=<default sort for this column>. If the field is currently being sorted, link to file.php?order=id;sort=<opposite of sort variable>.

Actually, the GET variables are separated by ampersands (&) not semicolons. But close enough 😃

Yes, Jasp is correct - make two button images, an up arrow and a down arrow, and make them the links I suggested earlier. Make the up one link to "file.php?order={rowname}&sort=asc" and the down one "file.php?order={rowname}&sort=desc". Then just include this in your SQL query:

SELECT * FROM table ORDER BY $order {$_GET['sort']}

Or for you security-conscious folks, a quick if statement beforehand:

if ($_GET['sort'] == "asc")
     $sort = "asc";
else
     $sort = "desc";

and replace "$_GET['sort']" in the SQL query with "$sort".

Jasp182

LoganK wrote:
Actually, the GET variables are separated by ampersands (&) not semicolons. But close enough 😃

My bad. I guess ampersands are the standard, but php recognizes both and I'm used to using semicolons.
(Now to look up if there is a clearly defined standard to decide whether or not to switch my convention. lol)

LoganK

You mean you put URL variables like this: page.php?blah=test;false=true;true=false
and it works?

Amazing. What other wild ideas will the human race think of next?

😃

macmonkey

HAHA... thank you guys once again.

LoganK

You're welcome...remember to mark thre - well, I see you already did that, didn't you? 😃