Page 1 of 1

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

Posted: Thu Nov 27, 2014 7:18 am
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");

Re: How do I query MySQL by two fields, and one field's cont

Posted: Thu Nov 27, 2014 11:52 pm
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.