Page 1 of 1

Order By: can you set the order after assinging it by name?

Posted: Fri Jul 12, 2013 2:11 pm
by simonmlewis
We have a side menu where we have this:

Code: Select all

$result = mysql_query ("SELECT id, catid, catname FROM products WHERE pause = 'off' AND catid IS NOT NULL AND pause = 'off' GROUP BY catname ORDER BY 
catname='test1' DESC,  
catname='test2' DESC,
How do I set it to sort by ID after the rest, as by default on this site, it isn't!?

Re: Order By: can you set the order after assinging it by na

Posted: Fri Jul 12, 2013 3:25 pm
by AbraCadaver
1. You have the expression pause = 'off' listed twice in the WHERE clause.
2. Odd query, not sure what you want.

Code: Select all

SELECT id, catid, catname FROM products
WHERE pause = 'off' AND catid IS NOT NULL
ORDER BY catname DESC, id DESC

Re: Order By: can you set the order after assinging it by na

Posted: Fri Jul 12, 2013 3:30 pm
by simonmlewis
Ignore the pause. I want to order by certain names, then after than by catname in alpha order, or I

Re: Order By: can you set the order after assinging it by na

Posted: Fri Jul 12, 2013 4:46 pm
by Christopher
simonmlewis wrote:I want to order by certain names, then after than by catname in alpha order
That sounds like two queries: one for "certain names" and on for everything else but "certain names" -- each sorted differently.

Re: Order By: can you set the order after assinging it by na

Posted: Fri Jul 12, 2013 4:50 pm
by simonmlewis
Perhaps it is.
If the start is for catnames, how does it sort the rest if not those names, as on one site it does it randomly, yet on another it doesn

Re: Order By: can you set the order after assinging it by na

Posted: Fri Jul 12, 2013 10:14 pm
by Christopher
The opposite of "catid IS NOT NULL" is "catid IS NULL".

You might want to try just fetching all the records. Then as you are looping through them to generate the HTML, assign the "catid IS NULL" ones to the to one variable containing the HTML for the top of the list and assign the "catid IS NOT NULL" ones to a second variable that contains the HTML for the bottom of the list. That adds an if() in the loop, but that probably better than two queries.

Re: Order By: can you set the order after assinging it by na

Posted: Sat Jul 13, 2013 2:28 am
by simonmlewis
Nooo you really don't get it.
Ignore that part of the script. The problem is the ORDER.
So - we order and place them in specific orders by the "catname" extracted. But after it's found five of them, I want to know what order it puts the rest in. ID, Title, or randomly, how does it do it?

Re: Order By: can you set the order after assinging it by na

Posted: Sat Jul 13, 2013 10:34 am
by Christopher
simonmlewis wrote:Nooo you really don't get it.
Ohhhhh ... I've known that from the start! ;)
simonmlewis wrote:So - we order and place them in specific orders by the "catname" extracted. But after it's found five of them, I want to know what order it puts the rest in. ID, Title, or randomly, how does it do it?
If you do "ORDER BY catname" it will order the list by catname, but if two values have the same value in catname then it will order them depending on how they are fetched from the specific database you are using. For all practical purposes there is no order, and you should not consider there to be one. You need to give it a second value in the ORDER BY clause, such as "ORDER BY catname, title".