I have a list if products organized with a ParentID and ID and I need to be able to select and display them as organized Ordered by Name.

This article shows a good example using the World-Continent-Country-City-etc. example: http://microsoft.apress.com/asptodayarchive/72582/backing-up-files-from-asp

World
World-Asia
World-Europe
World-Europe-France
World-Europe-France-Paris
World-North America
World-North America-New York
World-North America-New York-New York City

but it does not work with mySQL

table: Items
fields: ID, Name, Description, ParentID, Cost
ID is primary and auto_increment, INT
ParentID is also INT

I was thinking of exporting them to a Hash list or even an Array, then resorting, but was hoping for one good mySQL statement to help. That was when I found out I could use this statement:

SELECT * FROM Items WHERE ParentID IN ( SELECT ID FROM Items ) AND Sto
+reID = 2
ORDER BY ParentID, Name
[download]

and I would need to run it for at least 4 levels deep.

Here's what I used to use when things were originally pre-sorted. New items were added and they now appear isolated at the bottom of the list.

my $select="SELECT * FROM Items WHERE StoreID = $storeID";
&ExecureDB($select);

while ( my ($ID, $NM, $DES, $PI, $PRC)=$sth->fetchrow_array ) {
my ($rate, $PAD); # Setup for   Padding

unless ($PI == 0) { $rate=4;
    if ($lp == $PI) { $c1 = $ID; } # Last Parent ($lp) & 1st Child ($c
+1)
 elsif ($c1 == $PI) { $c2 = $ID; $rate*=2; }      # Save 2nd Child ($c
+2)
 elsif ($c2 == $PI) { $c3 = $ID; $rate*=3; }      # Save 3rd Child ($c
+3)
 elsif ($c3 == $PI) { $c4 = $ID; $rate*=4; }      # Save 4th Child ($c
+4)
 else {$rate*=5;}

  $PAD="  " x $rate;

} else { ($c1, $c2, $c3) = ('', '', ''); $lp=$ID; } # Clear Memory

print "$PAD $NM - $PRC - $DES", br;
[download]

I use this for populating an <OPTION> field with values and all. It's just modified for easy testing now.

Thanks in advance for your guidance.