Page 1 of 1

MySQL query - best method for sorting these results?

Posted: Sat Jan 01, 2005 8:51 pm
by boboat
Hi,

I am extracting information from a database, and am not too hot on mysql, before i spend hours messing about with various arrays and whatnot, I was wondering if anybody knows of a simple and painless way of ordering the information retrieved in a particular way..

The database currently looks like this:

Code: Select all

ID                 |   name              |   parentid
----------------------------------------------------------------
31                 |   ultrachild        |   30
30                 |   achild            |   28
28                 |   toplevel          |   0
32                 |   notherchild       |   28
ok, this orders the names into a hierachy, the rows parentid showing which row is its parent..
thus, "ultrachild 's" parent is "achild", "achild's" parent is "toplevel", "notherchild's" parent is also "toplevel",
or graphically it is:

Code: Select all

.                     toplevel
                          |
             achild           notherchild
                |
           ultrachild
now, my task is to extract the names in order, i.e.

Code: Select all

toplevel
  achild
    ultrachild
notherchild
where children are placed after their parents (rows of the same level can be either order) - (rows will be inputted to the dbase in any order btw)

im finding this somewhat confusing - im hoping theres a nice simple way of doing this in mysql, failing this, does anybody know how best to go about this!

thanks in advance
bob

Posted: Sat Jan 01, 2005 9:11 pm
by feyd
I've tried this before, and there's no simple way for mysql to do it unless you keep an ordering field. It's fairly simple for php to do this though, but there has to be a order to how the entries show up. With a completely arbitrary id numbering, you need an ordering value to know how to sort them (ordering by parentid will help, though)