PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sat Oct 20, 2018 11:15 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
PostPosted: Thu Nov 27, 2014 8:18 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4425
Location: United Kingdom
Syntax: [ Download ] [ Hide ]
$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.

Syntax: [ Download ] [ Hide ]
$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.


Top
 Profile  
 
PostPosted: Fri Nov 28, 2014 12:52 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
rcstock and comingsoon are both used at the same time to determine the sort order, so you can't use them separately.
Code:
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group