complex query

Cornplanter

Hi,

I have a table for product categories, sub-categories, sub-sub.. and so on:

id CHAR
parent_id CHAR
name
ordering

each category has id like 01, 02, 03
each subcategory has id like 01.01, 01.02

in other words, subcategories parent_id is equal to first two digits of it's own id.

So when I do select ... ORDER BY id, I have complete tree-view:

01 fruits
01.01 apples
01.02 plumbs
02 cars
02.01 audi
02.02 ferrari
02.02.01 red one
02.03 mazda
03 planes
03.01 boeing

.. and so on. Hope it is clear. The problem is, I have another field "order" which represents order of that category within it's parent category. Sometimes I need change ordering within parent category like this: (numbers after the name represents order within it's parent category).

03 planes 1
03.01 boeing 1
01 fruits 2
01.02 plumbs 1
01.01 apples 2
02 cars 3
02.03 mazda 1
02.01 audi 2
02.02 ferrari 3
02.02.01 red one 1

So the question is: what mySQL query "ORDER BY" do I need to make such a tree-view? Because simple "order by ordering" will return something like this:

03 planes 1
03.01 boeing 1
01.02 plumbs 1
02.03 mazda 1
01 fruits 2
01.01 apples 2

.. hope it's clear. It returns complete chaos, not a tree-view 🙂

Thanks a lot 😉

Sxooter

Wow, you sound like you're ready to graduate to a more extensible database, where you could
write your own functions to order by, and even create indexes with them.

In MySQL, you'll likely have to either pull all the data and order it in php, or use a secondary table of some kind to get proper ordering. Or use a HUGE set of switches inside a case statement.

Cornplanter

Sounds bad but thanks 😉 Can you suggest any alternate similar method maybe?

I think about converting ordering column to CHAR, syntax the same as id. So I would be able just order by ordering. Drawbacks are: much coding on php side to convert those 01.02.01 to comparable numeric values for comparisions, substracts and addons (when updating categorie's ordering), and vice versa after calculating before updating database. Not sure it is the optimal way 😉

Something very different isn't good too, lots of code are already written. Don't want to throw it to recycle bin 😉 The main goal is, i need to create tree-view like array on php side.

Sxooter

I think that for now, your best bet is to store a sort type key in the separate column that is computed from the first column you mentioned.