Order By

Hi guys,

I'm doing a MySQL query via PHP for up to 4 items. The items are being displayed in the order that they're in in the DB. How can I get them to be displayed in the order that I've called them?

$Newest_Result = mysql_query("SELECT * FROM include WHERE cat_code='$cat1' OR cat_code='$cat2' OR cat_code='$cat3' OR cat_code='$cat4'");

$i=1;
while ($row = mysql_fetch_object($Newest_Result)) {
$cat[$i]=$row->cat_code;
$img_url[$i]=$row->img_url;
$item_name[$i]=$row->item_name;
$i += 1;
}

So, I'd like them to display cat1 first, cat2 2nd, etc. (just like the query) but they just don't come back that way.

Thanks for any help.

Rob

 

You will need a long chain of ifs. Something like:

order by if(cat_code='{$cat1}', 0, 1), if(cat_code='{$cat2}', 0, 1), if(cat_code='{$cat3}', 0, 1)

 

Thank you. Seems to work just like the doctor ordered. I've never seen this (at least in this context). Can anyone point me toward some documentation?

Thank you again.

Rob

 

Hmm, I can already tell I'm going to do a bad job of explaining this.

"order by" will let you list any number of expressions, which are evaluated for each row in the result set. If two rows have the same value for the first expression, it will move on and compare the second expression, and so on. For example, if you do "order by lastname, firstname", and the last names for some rows are identical, it will move on to comparing the first names.

Basically all you're doing here is putting either 0 or 1 in each of the expressions that it will consider for sorting. And you know that it will sort 0 before 1.

order by if(cat_code='{$cat1}', 0, 1), if(cat_code='{$cat2}', 0, 1), if(cat_code='{$cat3}', 0, 1) 

Code (markup):

If cat_code == $cat1, then the first expression will be 0 and the others will be 1. So for that row the ordering is based on 0, 1, 1.

On the other hand, of cat_code == $cat2, then the ordering is 1, 0, 1.

Of course 0, 1, 1 comes before 1, 0, 1. So the $cat1 row will come before the $cat2 row. And so on.

 

Thanks for responding again. Do I understand that it's sorting by binary count, which is derived from the if statements? If true, then 0, else 1? I get that part, but ORDER BY 011, ORDER BY 101, ORDER BY 110 (so 3, 5, 6) is that the way it's happening? Another words there are 3 ORDER BY statements and then it's comparing the binary and sorting then? Little fuzzy here.

Thanks again for trying to explain.

Rob

 

The 0/1 stuff with its binary look is kind of a red herring. It could just as easily be 5 and 27. The only thing that matters is that the first number is lower than the second one.

It's really the same as if you were listing a bunch of people and had:

ORDER BY lastname, firstname, height

That would put all the Chans before all the Wongs, and it would put Amy Wong before Darrell Wong. And if there was a Darrell Wong who was 165cm and one who was 181cm, it would put the 165cm one first.

Instead of having the person's last name as the primary sorting criterion, you are putting a number in there. Using the if() function, you are putting the number 0 if cat_code (in the database) == $cat1 (PHP variable), or 1 if it does not.

 

Ok, think I get it. I really appreciate you helping as well as explaining the solution. Thanks.

Rob