order by problem. Like to order it as a folder tree structure

[r_a_s_robin]

I'm having troubles ordering my table as a folder tree structure. Who can help me out here?

_{(Assume i have the newest Mysql version. To keep the example easy to look at, the messages just say 'bla bla')}

 

I have the following table:

IDMessagechild_from

------------------

0bla blaNULL

1bla blaNULL

2bla bla0

3bla bla1

4bla bla1

5bla bla0

6bla blaNULL

7bla bla3

8bla bla4

9bla bla8

 

I'd like to order the table in such a way, that it would become like this:

 

IDMessagechild_from

------------------

0bla blaNULL

2bla bla0

5bla bla0

1bla blaNULL

3bla bla1

7bla bla3

4bla bla1

8bla bla4

9bla bla8

6bla blaNULL

 

How do i do that with just one Query??

 

the goal is to create something like this:

0 bla bla

-- 2 bla bla

-- 5 blabla

1 bla bla

-- 3 bla bla

---- 7 bla bla

-- 4 bla bla

----8 bla bla

------9 blabla

6 bla bla

 

[Mark1inLA]

I'm not sure if this is possible in MySQL alone... the way I'd approach this problem is by creating a recursive function in PHP that checks to see:

 

A. if the message is a top level category.

B. if there are children to that category.

 

Just recently, I read about this very subject in a book that shows how to handle this situation. When i get home and if this problem is unresolved, ill share the example.

[r_a_s_robin]

Thanks for your reply. I think i know how to manage this with php though. (2 for loops).

 

I'm really looking for a solution with mysql

[fenway]

With a few JOINs, that shouldn't be too hard... but what are you going to order by? Don't say UID...

[r_a_s_robin]

well, actually i'm ordering by something that isn't really there. As you can see i started by taking the lowest ID without a 'child_from'. Then there is the lowest ID row with child_from='0' (this happens to be ID=2), Then, because there is no child_from='2', it searches for child_from='0' again, etc etc

 

Because i'm ordering by something that isn't there, i've been thinking about (and trying to make) a additional column with AS in the mysql statement. But i haven't succeeded so far and i'm not sure it's the way to do it.

[fenway]

Yeah, that's almost impossible to do... usually, I have a sortorder within each level of category... so a simple order by clauses suffices.