ORDER BY column_name help (via link in HTML table view) (PHP MySQL

Question

My output for my table in HTML has several columns such as userid, name, age, dob.

The table heading is simply the title of the column name, I want this to be a link, and when clicked, the selected column is sorted in order, ASC, and then DESC (on next click). I thought this was pretty straight forward but I'm having some difficulty.

So far, I have produced this, and no output is taken, apart from the URL works by displaying 'users.php?orderby=userid'

<?php
          if(isset($_GET['orderby'])){ 
$orderby = $_GET['orderby']; 
$query_sv = "SELECT * FROM users BY ".mysql_real_escape_string($orderby)." ASC"; 
} 
//default query 
else{ 
$query_sv = "SELECT * FROM users BY user_id DESC"; 
} 
?>


              <tr>
                <th><a href="<?php echo $_SERVER['php_SELF']."?orderby=userid";?>">User ID</a></th>

Hoefully if I get this working, I can sort the users by D.O.B. next also using the same principles. Does anyone have any ideas?

Answer 1

It should be SELECT * FROM users ORDER BY ... instead of SELECT * FROM users BY .... Also you might want to check that the passed parameter is a valid column name, otherwise your query will fail.

Answer 2

$orders=array("name","price","qty");
$key=array_search($_GET['orderby'],$orders));
$orderby=$orders[$key];
$query="SELECT * FROM `table` ORDER BY $orderby";

Answer 3

To add a direction would be more complicated.

First you need to make a conditional parameter.

if (empty($_GET['asc'])) {
  $orderby.=" DESC"; 
  $dir="&asc=1";
} else {
  $orderby.=" ASC"; 
  $dir="";
}

Now put $orderby into query and $dir into link:

<a href="?orderby=userid<?php echo $dir?>">