From: Eve Atley [mailto:[EMAIL PROTECTED]
> Hi. I had a MySQL DB set up and recently added a field
> 'order' to allow for exceptions in a web site menu
> heirarchy. Fields should be ordered by 'order' field
> first where it does not contain 'NULL', and then by
> field 'title'. I had this previously:
>
> select * from navigation WHERE id = '".$category."' AND
> active='y' ORDER BY title
>
> ...and now, when I put in:
>
> select * from navigation WHERE id = '".$category."' AND
> active='y' ORDER BY order, title
>
> ...my menu shows nothing. How can I write my statement to
> allow for ORDER by order where 'order' does not contain
> 'NULL', and then title?
I'm curious about what you're using for a MySQL client. Are you using a web-based tool
such as phpMyAdmin? If so, it probably added this new column using backticks, which
allowed a reserved word (order) to be used as the name. I'm fairly certain that's why
your menu is not displaying anything -- your query is dying when it gets to the ORDER
BY clause.
So first step, in your query, add backticks around order:
ORDER BY `order`, title
That'll allow MySQL to see it as a column name and not a reserved word.
My second point is that I saw two replies to this, both of which said something along
the lines of:
SELECT * FROM navigation
WHERE id = '".$category."'
AND active='y'
AND order IS NOT NULL
ORDER BY order, title
...which is blatantly wrong. You asked for all records matching your original WHERE
clauses, order first by the 'order' column if not null, and then by 'title' if 'order'
is null. This query above will not return records for which 'order' is null.
While it may not be the best way to go about it, give this a shot:
SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord
FROM navigation
WHERE id = '".$category."'
AND active='y'
ORDER BY sort_ord ASC, title
This generates a temporary column in your result set that is used as the sort order
based on the values of 'order.' Note that if you use values higher than 99 in 'order,'
you'll want to set the 99 higher.
For an example, try this out:
===
mysql> CREATE TABLE test (title VARCHAR(10), `order` INT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('a', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('b', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('c', 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('d', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('e', 3);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (title, `order`) VALUES ('f', 2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
+-------+-------+
| title | order |
+-------+-------+
| a | NULL |
| b | NULL |
| c | 1 |
| d | NULL |
| e | 3 |
| f | 2 |
+-------+-------+
6 rows in set (0.00 sec)
mysql> SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM test ORDER BY
sort_ord ASC, title ASC;
+-------+-------+----------+
| title | order | sort_ord |
+-------+-------+----------+
| c | 1 | 1 |
| f | 2 | 2 |
| e | 3 | 3 |
| a | NULL | 99 |
| b | NULL | 99 |
| d | NULL | 99 |
+-------+-------+----------+
6 rows in set (0.00 sec)
===
If someone knows a better way to do this, I'd be curious to hear it. I imagine there's
a more efficient way to do it, but I can't seem to stumble upon it.
Anyway, hope this helps. :)
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
