How to best sort data from MySQL table in PHP script?


Problem :

I have a script in PHP like this one:

if(isset($_GET["ord"]))
{
    switch($_GET["ord"])
    {
      case 1: $ord = "name ASC";
              break;

      case 2: $ord = "surname ASC";
              break;

      case 3: $ord = "date ASC";
              break;

      case 4: $ord = "zone ASC";
              break;

      case 5: $ord = "latency DESC";
              break;

      default: $ord = "name ASC";
               break;

    }
}
else
{
    $ord = "name ASC";
}

And using it in code like this:

$res = mysql_query("SELECT people.name AS Name, surname, date, zone, latency FROM people WHERE online=1 ORDER BY $ord");
$content->ADD('<table class="online"><tr><th><a href="?s=online&amp;ord=1">Name</a></th><th><a href="?s=online&amp;ord=2">Surname</a></th><th><a href="?s=online&amp;ord=3">Date</a></th><th><a href="?s=online&amp;ord=4">Zone</a></th><th><a href="?s=online&amp;ord=5">Latency</a></th></tr>');
while ($resss = mysql_fetch_array($res))
{
  $content->ADD('<tr><td align="center">'.$resss["name"].'</td>');
  $content->ADD('<td>'.$resss["surname"].'</td>');
  $content->ADD('<td>'.$resss["date"].'</td>');
  $content->ADD('<td>'.$resss["zone"].'</td>');
  $content->ADD('<td>'.$resss["latency"].'</td>');
  $content->ADD('</tr>');

}

It is actually working quite awsome but I have problem when I need to change ASC and DESC sorting - so my question is how to rewrite my script to also be in acs/desc sorting available in outup data? Thank you.



Solution :

Assuming you have a header row with name, something like:

$content->ADD('<tr><td align="center">Name</td>');

you could change it to be:

$sort_order = "name ASC";
if($ord == 'name ASC') {
    $sort_order = "name DESC";
}
$content->ADD('<tr><td align="center"><a href="?ord=$sort_order">Name</a></td>');

then, when your script processes the "ord" variable, it will be based off your "Name" column header. Once you get this going, you'll probably want to change your variables a bit, using something like a "?sortby=name&sortorder=ASC" request variable, but get the above working first and then you'll see it.


Mysql Tutorials

Mysql Howto..

How to count number of Items in a column by year in PHP

HTML & PHP - How to make multiple forms submittable

How to execute two task with context switching in C++ with Boost

How to retrieve MySQL table rows using versioning with pagination

How to fetch 3 first places of each game from the score table in mysql?

How to make multiple tables share the id column in MySQL

How to format a date to the Italian way in PHP?

ZEND and MySQL - How to search and ORDER BY (sort) relevance?

How do I rename duplicates in MySQL using PHP or just a MySQL

How to create a MySQL user without password - needed for remote login - to be used in bash insert script?

How to check if I/O operation is blocking or non-blocking?

How to hash auto increments in mysql

MySQL : How to make a Having clause only filter by records in a Where clause?

How to modify MySQL export to CSV?

How to make MySQL table primary key auto increment?

How to allow empty string for integer in MySQL?

How do I implement a trigger in MYSQL to prevent insertion when row has duplicate data?

How can I get values from mysql db, replace some text from the values, and then get the averate of the numeric values?

How to compare scientific notation and decimal numbers in mysql MIN() aggregate function?

mysql how to edit my.ini on some hosting site

Mysql: how to select groups having certain values?

How to include MySQL database schema on GitHub?

How can I convert this php mysql code to SQL which is acceptable by SQL server ? [closed]

MySql table type, how do I draw column data from one table to another automatically?

MySQL: How to store a file into a medium blob field binary with HEX

How to design subtype in mysql workbench?

How to perform 'between' in Arabic (hijri) calendar and save it as 'date' in MySQL?

how to display the database records with MySQL Query

How to import CSV file to MySQL table

how to save or retrieve data from mysql when we cannot open it anymore?