From: hengky <[EMAIL PROTECTED]> > i'm working on database with a lot of category... > > here i example for category that i like to create > > Computer > Computer/Motherboard > Computer/Motherboard/AMD > Computer/Motherboard/Intel > Computer/Drive & Storage > Computer/Driver & Storage/SATA > Computer/Driver & Storage/ATA > Computer/Driver & Storage/ATA/7200 RPM/ > Computer/Driver & Storage/ATA/10000 RPM/ > Elektronik > Elektronik/Television > Elektronik/Television/CRT > Elektronik/Television/CRT/14 " > and go on > > so i use on mysql table > > table Category > > --------------------------------------- > | id_cat | nama_category | id_parent | > --------- ----------------- ----------- > | 001 | Computer | TLCAT | > | 002 | Motherboard | 001 | > | 003 | AMD | 002 | > | 004 | Intel | 002 | > | 005 | Drive & Storage | 001 | > | 006 | SATA | 005 | > | 007 | ATA | 005 | > | 008 | 7200 RPM | 007 | > | 009 | Elektronik | TLCAT | > | 010 | 10000 RPM | 007 | > | 011 | Television | 009 | > | 012 | CRT | 011 | > | 013 | 14" | 012 | > --------- ----------------- -----------
If the structure is only at most 4 (well, a fixed not too big number) levels deep you can do this directly in the SQL. Something like: SELECT CASE WHEN C2.nama_category is NULL THEN C1.nama_category WHEN C3.nama_category is NULL THEN C2.nama_category + '/' + C1.nama_category WHEN C4.nama_category is NULL THEN C3.nama_category + '/' + C2.nama_category + '/' + C1.nama_category ELSE C4.nama_category + '/' + C3.nama_category + '/' + C2.nama_category + '/' + C1.nama_category END as nama_category FROM Category as C1 LEFT JOIN Category as C2 ON C2.id_cat = C1.id_parent LEFT JOIN Category as C3 ON C3.id_cat = C2.id_parent LEFT JOIN Category as C4 ON C4.id_cat = C3.id_parent Except that you seem to be using 'TLCAT' in the id_parent column for the categories that have no parent. This looks strange to me. The id_parent should be an Int column, just like the id_cat, not a varchar! I don't know mysql so it may handle the id_cat=id_parent comparison just fine even if the id_parent equals to 'TLCAT', but I would definitely not do this. > this is the script > > #!/usr/bin/perl > > use DBI; > > > $dbh = DBI->connect("DBI:$dbtype:$dbname",$dbuser,$dbpass); > $sth = $dbh->prepare("select * from Category where id_parent = > 'TLCAT';"); > $sth->execute; > while(@DataCat = $sth->fetchrow_array() ) { > $tl_name = @DataCat[1]; Please go look up the difference between @DataCat[1] and $DataCat[1] ! > ONE: > while() { > $sth2 = $dbh->prepare("select * from Category where > id_parent='@DataCat[2]';"); > $sth2->execute; ! Use placeholders ! $sth2 = $dbh->prepare("select * from Category where id_parent=?;"); $sth2->execute($DataCat[2]); As you can see the $sth2 doesn't contain anything iteration specific so you may move the statement out of the loop so speed things up. > while(@DataCat2 = $sth2->fetchrow_array() ) { > $tl_name .= "/@DataCat2[1]"; > print "$tl_name/@DataCat[1]"; 1. The $tl_name already contains $DataCat[1], you assigned it to there yourself a few lines above. 2. You keep appending to $tl_name in this loop, but you never reset it! Replace those two lines by just print "$tl_name/$DataCat2[1]\n"; > } > $sth2->finish; > last ONE: > } > } > $sth->finish; > $dbh->disconnect; Anyway this would print only up to the second level of the category structure, you will need to use a recursive function if you want to support arbitrarily deep structures. Jenda ===== [EMAIL PROTECTED] === http://Jenda.Krynicky.cz ===== When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>