Page 1 of 2

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

Posted: Wed Feb 24, 2010 11:43 am
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?

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

Posted: Wed Feb 24, 2010 11:44 am
by Kurby
ORDER BY name, age

also

ORDER BY name DESC, age ASC

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

Posted: Wed Feb 24, 2010 11:52 am
by simonmlewis
Oh crikey, that simple? Thanks very much for the quick reply.

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

Posted: Thu Feb 25, 2010 7:14 am
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'.

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

Posted: Thu Feb 25, 2010 9:20 am
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?

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

Posted: Thu Feb 25, 2010 9:27 am
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'.

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

Posted: Thu Feb 25, 2010 9:47 am
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.

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

Posted: Thu Feb 25, 2010 9:58 am
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?

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

Posted: Thu Feb 25, 2010 10:28 am
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

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

Posted: Thu Feb 25, 2010 10:45 am
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.

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

Posted: Thu Feb 25, 2010 10:51 am
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.

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

Posted: Thu Feb 25, 2010 11:08 am
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.

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

Posted: Thu Feb 25, 2010 11:11 am
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.

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

Posted: Thu Feb 25, 2010 11:20 am
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??

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

Posted: Thu Feb 25, 2010 11:36 am
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>";
}