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.

In reply to Parent/Child Association with mySQL statements by JayBee

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.