How do I order MySQL by fields, effectively?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I order MySQL by fields, effectively?

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I order MySQL by fields, effectively?

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I order MySQL by fields, effectively?

Post by simonmlewis »

"Parse error: syntax error, unexpected 'yes' (T_STRING) in ".....
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I order MySQL by fields, effectively?

Post 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...
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I order MySQL by fields, effectively?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I order MySQL by fields, effectively?

Post by requinix »

What's the rest of the query?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I order MySQL by fields, effectively?

Post 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");
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I order MySQL by fields, effectively?

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I order MySQL by fields, effectively?

Post 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 ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I order MySQL by fields, effectively?

Post by requinix »

Yes, or 0,1 and sort in descending order, whichever makes more sense to you.
Post Reply