What is the best method for sorting columns with PhP and MySQL?

Question

Hello,

I would like to know which is the best way to sort table columns with PHP and MySQL. Is there a way to do this without having to set a variables like the following?

$strASC = $_GET["order"];

if ($strASC == "ASC") 
{
    $strASC = "DESC";
}

or does exist an SQL query that reverses the ASC or DESC depending on current status?

Answer 1

Using only PHP and the database, I do not think there is a better way that you could do that.

But, if you wanted to try using javascript and not running another query, you could take a look at jquery and tablesorter.

Answer 2

no mysql doesnt do it automatically. u can reduce it though

$strASC = $_GET['order']=='ASC'?'DESC':'ASC';

Answer 3

When you get a result set from a database it is in the order you specify. The database does not reorder the data in it's tables so therefore it does not know which "status" it is currently in.

Also watch out for SQL Injection in your example.

Answer 4

There's no function in particular for flipping the sort order, but you could do something like this:

$sortDir = $_GET['order'] == 'ASC' ? 'DESC' : 'ASC';
mysql_query('SELECT * FROM table ORDER BY col ' . $sortDir);

Answer 5

You're having a $_GET in that code so I assume that you want to use url params to set the order direction of your query.

Your code looks a bit confusing, you're setting $strASC to "DESC" which is a little bit of a contradiction ;)

What about something like that:

$sortDirection = $_GET['order'];

if ($sortDirection == 'ASC' || $sortDirection == 'DESC')
{
    $sql = "SELECT mystuff FROM mytable WHERE mycrit ORDER BY " . $sortDirection;
}
else
{
    echo 'Invalid sort direction';
}