Multiple Ordering: order by this, then order by that.

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

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

Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

Hi

I want to be able to put a table into the order of two fields.

So for instance, if the table had NAME and AGE, I want to put it in order of NAME, then by AGE.

John 33
Paul 34
Ring 54

I know how to ORDER BY.... but no idea how to do that essentially.... twice?

Can anyone help?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Multiple Ordering: order by this, then order by that.

Post by Kurby »

ORDER BY name, age

also

ORDER BY name DESC, age ASC
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

Oh crikey, that simple? Thanks very much for the quick reply.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

It's not working very well:

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg'  AND pause = 'off' AND deleted IS NULL ORDER BY priority, title LIMIT $offset, $rowsPerPage") or die (mysql_error());
It's ordering it by title ONLY. I have one item in this category with a 'priority' number of 1. The rest are NULL or '0'.
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

simonmlewis wrote:It's not working very well:

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg'  AND pause = 'off' AND deleted IS NULL ORDER BY priority, title LIMIT $offset, $rowsPerPage") or die (mysql_error());
It's ordering it by title ONLY. I have one item in this category with a 'priority' number of 1. The rest are NULL or '0'.
So what is the order of the priority that is returned? NULL, then 0, then 1?
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: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

They are either NULL, or 1, 2, 3, 4, 5, 6, 7 etc.

At the moment, just NULL and just a single '1'.
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

What I'm asking is what order is returned with the query you are using and why is it not correct? Maybe post the data that's returned.
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: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

It's in ASC order.
The item marked as '1' is "White Teeth Fast Light Technology".

Clearly this is to test it, and in theory if all the others in the table have NULL in the priority field, and this has '1', shouldn't this be first?
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

simonmlewis wrote:It's in ASC order.
The item marked as '1' is "White Teeth Fast Light Technology".

Clearly this is to test it, and in theory if all the others in the table have NULL in the priority field, and this has '1', shouldn't this be first?
NULL will be first. So you can modify the query to make the NULLS a higher priority. If you don't have an upper bound to use, such as 100, then maybe Z.

Code: Select all

SELECT *, IFNULL(priority, 'Z') AS real_priority
   FROM products
   WHERE category = '$mycateg'  AND pause = 'off' AND deleted IS NULL
   ORDER BY real_priority, title LIMIT $offset, $rowsPerPage
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: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

Sorry but you are starting to talk Greek to me.

I would have thought that NULL would be nothing, so something in the field would be higher.

I need to have NUMBERS to come first, in number order, and then NULLs.
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

simonmlewis wrote:Sorry but you are starting to talk Greek to me.

I would have thought that NULL would be nothing, so something in the field would be higher.

I need to have NUMBERS to come first, in number order, and then NULLs.
I told you that NULL will come first, so you must force them to be last. The query I posted will return `priority` as `real_priority` unless `priority` is NULL, then it will return `real_priority` as Z which will appear at the end of the list if all the other priorities are numeric.
Last edited by AbraCadaver on Thu Feb 25, 2010 11:09 am, edited 1 time in total.
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: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

Ok - that has sort of worked - but I have just realised that the options give '0' as one of the priority levels.

At the moment they are all NULL, but if someone hits 'Go' and doesn't touch the dropdown list of levels, it will mark it as '0' (zero).

I can set them ALL to zero, but I don't quite know what to do now. I didn't think 0 would be assigned a level of priority.
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

You need to figure out the acceptable values for priority. If 0 is not valid then don't offer it as a choice, or check for 0 and don't insert it, which will be NULL.
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: Multiple Ordering: order by this, then order by that.

Post by simonmlewis »

I hate the annoying scripts that are simple but I cannot suss!!

Code: Select all

if ($update == "priority") {
if ($priority == "0") { $priority = "NULL"; }
mysql_query ("UPDATE products SET priority = '$priority' WHERE id = '$id'");
echo "<div class='admincompletedbox'>The priority level has been set.</div>";
}
This isn't doing anything! I leaves the field as a '0' And I am sure is it the line:

Code: Select all

if ($priority == "0") { $priority = "NULL"; }
What daft thing I am doing wrong??
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: Multiple Ordering: order by this, then order by that.

Post by AbraCadaver »

NULL is not a value, it's the absence of a value. To keep a field NULL you don't insert anything:

Code: Select all

if ($priority != "0") {
    mysql_query ("UPDATE products SET priority = '$priority' WHERE id = '$id'");
    echo "<div class='admincompletedbox'>The priority level has been set.</div>";
}
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.
Post Reply