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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

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

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

Post 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!?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Ignore the pause. I want to order by certain names, then after than by catname in alpha order, or I
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

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

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

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

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

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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".
(#10850)
Post Reply