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?

Your Common Sense · Accepted Answer · 2010-03-20 15:38:42Z

2

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

edited Mar 20, 2010 at 15:38

answered Mar 20, 2010 at 14:45

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

3 Comments

Yvonne Over a year ago

What exactly would I put within my <th> tags then as the link? Is it stil necessary to use the $_SERVER global

Your Common Sense Over a year ago

@Derek No, You don't need to use $_SERVER, especially if you fail to write in proper case. <a href="?orderby=userid">

Yvonne Over a year ago

this worked an absolute treat! Thanks a lot. However, is there a way that when I click the column title again, the order is reserved? At the moment it sorts it in descending (i.e. 1, 5, 12) If I click again, I want it to be 12, 5, 1? Is this a simple solution?

Max Shawabkeh · Accepted Answer · 2010-03-20 14:37:07Z

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.

answered Mar 20, 2010 at 14:37

Max Shawabkeh

38.7k10 gold badges85 silver badges92 bronze badges

1 Comment

Yvonne Over a year ago

Yea I just saw that issue, however I changed it and still no luck, nothing happens :(

Your Common Sense · Accepted Answer · 2010-03-20 17:57:21Z

1

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?>">

answered Mar 20, 2010 at 17:57

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related