Page 1 of 1
How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 8:22 am
by simonmlewis
I need to set an order on some fields. So ORDER BY black, rcstock, title.
Problem is, 'black' sometimes is empty', and we need to ensure anything where black is NOT empty, and says "yes" in the field, come before the empty ones.
Help please. As if I set it like so
Code: Select all
ORDER BY black DESC, rcstock, title ASC
It doesn't show those where "black" is empty.
Just tried this too, and it doesn't do anything differently:
Code: Select all
ORDER BY rcstock, title ASC, FIELD(black, 'yes', '') DESC
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 12:28 pm
by requinix
A UNION of two queries may be more effective than
Code: Select all
ORDER BY IF(black = "yes", 0, 1) ASC, other fields
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 12:35 pm
by simonmlewis
"Parse error: syntax error, unexpected 'yes' (T_STRING) in ".....
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 1:02 pm
by requinix
What I posted, and what you posted before too, is SQL. Not PHP code. If you put it in a PHP string then you might have to escape (or change) quotes...
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 1:08 pm
by simonmlewis
Sorry yes I see what you mean, however with that code, it's now not showing those where "black" is empty. Only those where there is a 'yes' in there.
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 1:48 pm
by requinix
What's the rest of the query?
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 2:05 pm
by simonmlewis
Code: Select all
$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND enable = 'yes' ORDER BY IF(black = 'yes', 0, 1) ASC, rcstock, title ASC LIMIT $offset, $rowsPerPage");
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 3:34 pm
by requinix
That looks right. What happens if you run it manually? Have you checked if it's your code that's not showing those?
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 3:43 pm
by simonmlewis
Yes that is working, it seems products that were not set to "black", should have been, hence it showed results incorrectly.
Thanks a mill. If I wanted black 'yes' to be last, is it .... 1, 0 ??
Re: How do I order MySQL by fields, effectively?
Posted: Thu Oct 17, 2013 4:04 pm
by requinix
Yes, or 0,1 and sort in descending order, whichever makes more sense to you.