Order by statement

[assgar]

Hi
I would like offer the user a choice of displaying
search results. They should be able to select which column
to ORDER BY and in ascending or descending order.
$sort_order contain colums name
$sort_type contain desc or asc
How can I use these two arrays in the order by statement?
Option 1
I am getting this
error message: Error, query failed with this config

<?
$query ="select item number description
  from test_table
  where item = '$item'
  order by ($sort_order) '$sort_type'";
?>

Option 2

The search results are displaying in ascending order.
But the "ORDER BY" statement is not working.

<?
$query ="select item number description
  from test_table
  where item = '$item'
  order by '$sort_order' '$sort_type'";
?>

[boo_lolly]

first, connect to the database, second, mysql_query() is necessary. third, don't put single quotes around your variables inside your sql query. and fourth, 'item = $item' is unnecessary; simply put $item.

<?php

$query = mysql_query("SELECT item, number, description FROM test_table  WHERE $item ORDER BY $sort_order, $sort_type");

?>

now if you want to return your search query in an array (which i'm sure is best for your situation)....

<?php

$query = mysql_query("SELECT item, number, description FROM test_table  WHERE $item ORDER BY $sort_order, $sort_type");

$result = mysql_query($query);

?>

hope this helps.

[assgar]

Thanks for the suggestions
I know this is not difficult and that was what's annoying me.
The missing commas on the example was not the issue, the real code has commas.
The options below all work. I discovered the problem is php does not like it (for the first two options) if the ending "; is on the same line as the order by.
Option 1

<?
$query ="select item, number, description
  from test_table
  where item = '$item'
  order by '$sort_order' '$sort_type'
  ";
?>

Option 2 chosen approach

<?
$query ="select item, number, description
  from test_table
  where item = '$item'
  order by $sort_order sort_type
  ";
?>

Option 3

<?
$query ="select item, number, description
  from test_table
  where item = '$item'
  ORDER BY '$sort_order' ".$sort_type." ";
?>

[boo_lolly]

that doesn't make any damn sense. it should make no difference what line the ';' is on.

[nickclarson]

The problem being that you cannot do that. The reason is because for the same reason you cannot include "ORDER BY" in a variable, you also cannot include "DESC" or "ASC".

You have to do something like this:

// This can be DESC or ASC
$sort_type = "DESC";

if($sort_type == "DESC"){
    $query = mysql_query("SELECT item, number, description FROM test_table WHERE $item ORDER BY $sort_order DESC");
}

if($sort_type == "ASC"){
    $query = mysql_query("SELECT item, number, description FROM test_table WHERE $item ORDER BY $sort_order ASC");
}

Hope this is helpful =)