CODEWITHSUNDEEP
phpmysqlsorting
Byakugan
Byakugan

Reputation: 981

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

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.

Upvotes: 0

Views: 4088

Answers (3)

jbnunn
jbnunn

Reputation: 6355

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.

Upvotes: 1

orourkek
orourkek

Reputation: 2101

That whole switch is somewhat unnecessary. When performing ORDER BY, you can specify which column to sort by its numeric place (order) in the query.

select `uid`, `name` from `test` order by `uid` asc

should give the same results as 

select `uid`, `name` from `test` order by 1 asc

You could modify the code with the switch to accept the argument for desc / asc, and simply use the numeric as the order by target. The switch you posted has the benefit of not producing weird queries like order by 555, but that can be solved in other ways as well. This is just some basic information to get you started with the coding, but if you need clarification(s), drop a comment

Upvotes: 0

Robert Sim
Robert Sim

Reputation: 194

Off the fly, I can only think of jQuery solution, if you do not mind: jQuery DataTables plugin. Using it would eliminate the need to make calls to your database unnecessarily.

The most basic implementation would be:

$(document).ready(function(){
    $('#example').dataTable();
});

Upvotes: 2

Related Questions