MySQL query - best method for sorting these results?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
boboat
Forum Newbie
Posts: 1
Joined: Sat Jan 01, 2005 8:44 pm

MySQL query - best method for sorting these results?

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
Post Reply