Multiple Ordering: order by this, then order by that.
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.
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?
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.
All the best from the United Kingdom.
Re: Multiple Ordering: order by this, then order by that.
ORDER BY name, age
also
ORDER BY name DESC, age ASC
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.
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.
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.
It's not working very well:
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'.
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());Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- 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.
So what is the order of the priority that is returned? NULL, then 0, then 1?simonmlewis wrote:It's not working very well:
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'.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());
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.
They are either NULL, or 1, 2, 3, 4, 5, 6, 7 etc.
At the moment, just NULL and just a single '1'.
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.
All the best from the United Kingdom.
- 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.
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.
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?
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.
All the best from the United Kingdom.
- 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.
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.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?
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, $rowsPerPagemysql_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.
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 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.
All the best from the United Kingdom.
- 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.
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.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.
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.
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.
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.
All the best from the United Kingdom.
- 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.
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.
I hate the annoying scripts that are simple but I cannot suss!!
This isn't doing anything! I leaves the field as a '0' And I am sure is it the line:
What daft thing I am doing wrong??
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>";
}Code: Select all
if ($priority == "0") { $priority = "NULL"; }Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- 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.
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.