Adding "order by SOMETHING" to a join select statement.

OwenTheSamoan

I'm running a select statement using an "inner join"(I think lol).

Anyway, I want to add "order by SOMETHING" to the select statement but I keep getting errors. Here is what my select statement looks like. It's a bit complicated but I need it like this.

$query = "SELECT distinct a.* FROM table1 as a, table2 as b WHERE ";

if(!empty($color1) or !empty($color2)or !empty($color3)or !empty($color4))
$query .= " (a.color in('$color1 ','$color2 ','$color3 ','$color4 '))";

if(!empty($book1) or !empty($book2)or !empty($book3)or !empty($book4))
$query .= " AND(a.book in('$book1 ','$book2 ','$book3 ','$book4 '))";

if(!empty($image))
$query .=" AND a.userid = b.userid AND ( b.img_id != 0)";

Weedpacket

First off: does it run without the ORDER BY clause?
Second: where are you putting the clause? Should be on the end of that bit.
Third: what does the ORDER BY clause say?

OwenTheSamoan

Yes, it does work without the order by clause, I have been putting it at the end and I am doing the order by with when they login "order by login_time".

tomhath

Is login_time a column from table1? It can only order by a column in the result sset.

OwenTheSamoan

Yes, login_time is a column from table1.

Weedpacket

So what are the errors?
Also, echo your entire query before sending it to the database, and make sure it's saying what you want it to say.

keyurjbhatt

yes your query is not a perfect one,
if first if condition is not satisfied & second if condition is satisfied then it will join query string after where with and
so it will give error
so in second condition check whether it should join with and or it is first time after where clause.

so u must have to check in second if condition
that if first if condition is false then join query starting without

and