[RESOLVED] Mysql query ordering

theelectricwiz

Hello,

I am trying to set up a menu from a mysql table. Every item in the menu can have a subitem. The way I used to create this was to use a recursive php script, but I am wondering if I can do the same in MySQL. I am looking for a command that can order the items in the menu table so that all the subitems are below their correct parent item. Below I have what is stored in the table and below that is the order I want the items to be once I run the query on the table.

information as retrieved by SELECT * FROM table:

id    parent_id    name
1     null         A first item
2     null         B second item
3     1            A first subitem of first item
4     null         C third item
5     2            B second subitem of second item
6     1            B second subitem of first item
7     2            A first subitem of second item
8     3            A first subitem of first subitem of first item

information as retrieved by wanted query:

id    parent_id    name
1     null         A first item
3     1            A first subitem of first item
8     3            A first subitem of first subitem of first item
6     1            B second subitem of first item
2     null         B second item
7     2            A first subitem of second item
5     2            B second subitem of second item
4     null         C third item

So what I am wanting is a mysql query that orders the items of the table so that all the items are sorted by their name in relation to the their level (item, subitem, subsubitem) and group (first item, second item) amd so that each subitem is directly under its parent item.

Can anyone help me with a MySQL command that does this?

Cheers for reading this.

From,
Jack

rulian

try SELECT * FROM table ORDER BY id, parent_id ASC

theelectricwiz

Hello,

No, unfortunately it is not quite that simple. At some stage I had something like this working

SELECT * FROM table ORDER BY IFNULL('parent_id', 'id'), 'parent_id', 'id';

but that only ordered by the IDs and I now want to sort them in their groups by their names (eg each subgroup is sorted a-z and then the top group is sorted a-z with the subgroups under each corresponding parent item)

bogu

The way you design your DB table I don't think you can retrieve the order you want without using a php recursion function to order your results, if you need all this using one query to retrieve the data you can use the preorder method, here is a nice article...
Storing Hierarchical Data in a Database

Ofc, preorder is not the only method, but this is the one I used more ...

theelectricwiz

Cheers for the good link. It got me thinking and I realised that in my 2 am structuring I had set it out correctly and I had already used one of the techniques in that link. Cheers though.