[RESOLVED] Order by statement

assgar

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

bretticus

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

$sort_order is a column reference and not a char/varchar/text type. No need for the single quotes (aka your syntax is wrong.) Do not use ORDER BY parenthesis either as that is wrong syntax I'm almost sure.

bretticus

Hold the presses, you are also using arrays for the variables...

bretticus

$sort_order contain colums name
$sort_type contain desc or asc

How can I use these two arrays in the order by statement?

So which are they? For example is $sort_order an array of column name strings or a string with one column name?

assgar

I am willing to consider other ways to accomplish the task.
This is what I can think of.

The arrays $sort_order and $sort_type are both receiving a single value from dropdown selection boxes.

example $sort_type will contain desc or asc (to determine ascending or

descending) and $sort_order will contain a column name so results will
be ordered by the chosen column.

note: this does not work as well

order by $sort_order 'sort_type' ";

bretticus

note: this does not work as well
PHP Code:
order by $sort_order 'sort_type' ";

I'm surprised it works at all.

The proper syntax should be...

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

Where $sort_order is the variable with the name of the column and $sort_type is the direction of the ORDER BY: DESC or ASC.

assgar

bretticus wrote:
I'm surprised it works at all.

The proper syntax should be...
<?php
$query ="select item, number, description
     from test_table
     where item = '$item'
     C?>
Where $sort_order is the variable with the name of the column and $sort_type is the direction of the ORDER BY: DESC or ASC.

Thanks for the suggestion

I tried the suggested order by. The search result did not display with the order by. The same code worked without the order by.

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

bretticus

Please review SELECT syntax. No offense, but it is obvious you are new to SQL because unless your browser, etc is removing commas (or I'm ignorant or crazy), you'll never make a query work SELECTing columns without commas between them like in

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

item, number and description NEED to be separated by commas. See my previous example.

Also, your variable substitutions in your ORDER BY clause are not to be parsed by MySQL as strings, but as an actual column reference and a DESC or ASC moniker. You have single quotes around them signifying to MySQL that they are strings...don't!

It just seems to me that you have skipped ahead without learning the fundamentals which is your real problem here. So again, I'd advise a refresher course.

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." ";

?>