How do I query MySQL by two fields, and one field's content?

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 query MySQL by two fields, and one field's content?

Post by simonmlewis »

Code: Select all

$query = ("SELECT * FROM products WHERE catid = :c AND pause = 'off' ORDER BY rcstock, comingsoon DESC, $order LIMIT $offset, $rowsPerPage");
I need to show all products where rcstock = 'in stock' fiirst, but that can be done by ASC, and then show all comingsoon = 'yes' after that.

At the moment comingsoon is all appearing at the top for some reason, probably because rcstock doesn't have anything in it.

So how do I do the ORDER BY so rcstock ASC comes first, then comingsoon = NULL comes at the end?
I've tried this as well, but it doesn't work.

Code: Select all

$query = ("SELECT * FROM products WHERE catid = :c AND pause = 'off' ORDER BY if(comingsoon = '' or comingsoon is null,1,0), rcstock 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 query MySQL by two fields, and one field's cont

Post by requinix »

rcstock and comingsoon are both used at the same time to determine the sort order, so you can't use them separately.

Code: Select all

ORDER BY (CASE WHEN rcstock='in stock' THEN 0 WHEN comingsoon='yes' THEN 1 ELSE 2 END) ASC, other sort criteria, ... LIMIT $offset, $rowsPerPage
If you're doing pagination then you should guarantee that the query always returns the same results in the same order. Sort by whatever you want first, then other useful fields after, then by a unique field.
Post Reply