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

Question

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.

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — orourkek
– orourkek, Commented Jun 6, 2012 at 16:50
OK I will try to learn this system but still do not answer my problem :) — Byakugan
– Byakugan, Commented Jun 6, 2012 at 16:58

Robert Sim · Accepted Answer · 2012-06-06 17:02:52Z

2

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();
});

answered Jun 6, 2012 at 17:02

Robert Sim

1943 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

This is excellent... never would have thought to sort so much data client side before. Documentation could be a bit better, but once it's up and going it works great. Thanks!

jbnunn · Accepted Answer · 2012-06-08 14:56:51Z

1

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.

answered Jun 8, 2012 at 14:56

jbnunn

6,3654 gold badges43 silver badges66 bronze badges

1 Comment

jbnunn Over a year ago

And I know you didn't like @robert-sim's jQuery solution but it's relatively unobtrusive and much easier IMO. I've done it via jQuery and straight PHP and request vars--it's quite a headache going the PHP-only route.

orourkek · Accepted Answer · 2012-06-06 17:08:03Z

0

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

answered Jun 6, 2012 at 17:08

orourkek

2,11115 silver badges23 bronze badges

1 Comment

Byakugan Over a year ago

According to me this is just another way of doing my code but still not solving sorting by asc/desc when I click on "name" in site

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related