...

View Full Version : How do you sort your MySQL results according to column name?



guvenck
01-05-2007, 01:59 AM
Hi,

I normally output the results of a mysql query according to ID. I display the results in tables.

Example:



<?php

// file.php

if(isset($GET['op'])) {
switch($op) {
case "sort_ID";
$query = "SELECT ID,name,surname,date FROM table ORDER BY ID";
break;
case "sort_name";
$query = "SELECT ID,name,surname,date FROM table ORDER BY name";
break;
case "sort_surname";
$query = "SELECT ID,name,surname,date FROM table ORDER BY surname";
break;
case "sort_date";
$query = "SELECT ID,name,surname,date FROM table ORDER BY date";
break;
}
} else { // use default sorting
$query = "SELECT ID,name,surname,date FROM table";
}

include("db.php");
$result = mysql_query($query);

echo '<table>';
echo '<tr>';
echo '<td><a href="file.php?op=sort_ID">ID</a></td>';
echo '<td><a href="file.php?op=sort_name">Name</a></td>';
echo '<td><a href="file.php?op=sort_surname">Surname</a></td>';
echo '<td><a href="file.php?op=sort_date">Date</a></td>';
echo '</tr>';

while ($row = mysql_fetch_array($result)) {
echo '<tr>';
echo '<td>' . $row['ID'] . '</td>';
echo '<td>' . $row['name'] . '</td>';
echo '<td>' . $row['surname'] . '</td>';
echo '<td>' . $row['date'] . '</td>';
echo '</tr>';
}
echo '</table>';

?>


When this table is displayed, I would like to sort the results according to name, surname or date when clicked on the column name. When clicked again, I want to sort them upside down (DESC).

What I did may work (except the reversal sorting) but what if I had many columns? Do I need to write so many lines of code?

Maybe there is a class for this?

whizard
01-05-2007, 02:04 AM
How about just doing this:



f(isset($GET['op'])) {
$op = $_GET['op'];
$query = "SELECT ID,name,surname,date FROM table ORDER BY ".$op;
}


HTH
Dan

dumpfi
01-05-2007, 12:51 PM
How about just doing this:



f(isset($GET['op'])) {
$op = $_GET['op'];
$query = "SELECT ID,name,surname,date FROM table ORDER BY ".$op;
}


HTH
DanWhat if I do a request to "file.php?op=ID;DROP DATABASE"? I can execute any SQL command with your solution.

This is a more secure version:
<?php
$sortColumns = array('ID', 'name', 'surname', 'date');
$sortOrder = array('ASC', 'DESC');

if(!isset($_GET['op']) || !isset($sortColumns[$_GET['op']]))
{
$_GET['op'] = 0;
}
if(!isset($_GET['ord']) || !isset($sortOrder[$_GET['ord']]))
{
$_GET['ord'] = 0;
}

$query = 'SELECT '.implode(', ', $sortColumns).' FROM table ORDER BY '.$sortColumns[$_GET['op']].' '.$sortOrder[$_GET['ord']];
$fileName = getenv('SCRIPT_NAME');
include 'db.php';
$result = mysql_query($query);

echo '<table>';
echo '<tr>';
foreach($sortColumns as $key => $column)
{
echo '<td><a href="',$fileName,'?op=',$key,'&ord=',($_GET['op'] == $key && 0 == $_GET['ord']) ? '1' : '0','">',$column,'</a></td>';
}
echo '</tr>';

while ($row = mysql_fetch_array($result))
{
echo '<tr>';
foreach($sortColumns as $column)
{
echo '<td>',$row[$column],'</td>';
}
echo '</tr>';
}
echo '</table>';
?>dumpfi

guvenck
01-05-2007, 01:48 PM
dumpfi, that is a great solution, it works... By adding or removing members to the array, you can build a sortable table output for any field...

I wonder, could this solution be embedded as a class?

guelphdad
01-05-2007, 06:41 PM
What if I do a request to "file.php?op=ID;DROP DATABASE"? I can execute any SQL command with your solution.

then you would just wrap the GET in mysql_real_escape_string to prevent that wouldn't you?

And more to the point you would not have such permissions for a user over the web, you would create a secure user with only select, insert and update privileges and none other.

ptmuldoon
02-07-2008, 04:29 PM
Searching is your friend :)

I'm looking to possibly do the same as above, and like the code posted by dumpfi to create the sortable table.

But can someone maybe help explain/show how you can possibly expand the sortable columns to include additional columns that do not officially exist in your database. I'd like to include columns/fields that are being created during the actual sql query such as:


$sql = "SELECT *, ( wins / (win + losses ) ) AS `Rating`, ";

Andrew Johnson
02-07-2008, 04:44 PM
Firstly, this snippet:



if(isset($GET['op'])) {
switch($op) {
case "sort_ID";
$query = "SELECT ID,name,surname,date FROM table ORDER BY ID";
break;
case "sort_name";
$query = "SELECT ID,name,surname,date FROM table ORDER BY name";
break;
case "sort_surname";
$query = "SELECT ID,name,surname,date FROM table ORDER BY surname";
break;
case "sort_date";
$query = "SELECT ID,name,surname,date FROM table ORDER BY date";
break;
}
} else { // use default sorting
$query = "SELECT ID,name,surname,date FROM table";
}


could be be written as: (just change the $_GET["op"] values from sort_ID,sort_name,etc to ID,name,etc)



$query = "SELECT ID,name,surname,date FROM table";

if (isset($_GET["op"]))
$query .= " ORDER BY " . $_GET["op"];


Secondly, to make them order in the opposite direction add " DESC" to the end of your query, so "SELECT * FROM table ORDER BY col" becomes "SELECT * FROM table ORDER BY col DESC"

Thirdly, if you'd like to sort by multiple rows just add a comma. For example "SELECT * FROM tables ORDER BY col1,col2 DESC,col3" would order first by col1 then by col2 (descending) then by col3

Hopefully all that can help you get your code working more efficiently.

aedrin
02-07-2008, 05:49 PM
Did you read the topic? You just repeated everything that was posted here.

Andrew Johnson
02-07-2008, 05:58 PM
Did you read the topic? You just repeated everything that was posted here.

I did not.

aedrin
02-07-2008, 06:01 PM
I did not.

You probably should, so you can add something useful to the topic, rather than confuse the user by making them think that this is something new. It also saves you time.

Andrew Johnson
02-07-2008, 06:20 PM
You probably should, so you can add something useful to the topic, rather than confuse the user by making them think that this is something new. It also saves you time.

Thanks for the tidbit of knowledge.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum