How do you sort your MySQL results according to column name?

[guvenck]

Hi,

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

Example:

PHP Code:

<?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]

How about just doing this:

PHP Code:

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


HTH
Dan

[dumpfi]

Originally Posted by whizard

How about just doing this:

PHP Code:

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


HTH
Dan

What 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 Code:

<?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]

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]

Originally Posted by dumpfi

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]

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:

PHP Code:

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


[Andrew Johnson]

Firstly, this snippet:

PHP Code:

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)

PHP Code:

$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]

Did you read the topic? You just repeated everything that was posted here.

[Andrew Johnson]

Originally Posted by aedrin

Did you read the topic? You just repeated everything that was posted here.

I did not.

[aedrin]

Originally Posted by Andrew Johnson

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]

Originally Posted by aedrin

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.