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>
