Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

In my script I have these caetgories for MySQL which is basically set up lke this
id name parent description view_cats_prod image org
Edit Delete 26 PWC 0 aaa no store/cat/wave.jpg 4
Edit Delete 24 Truck 0 aa no store/cat/truck.jpg 5
Edit Delete 22 Snowmobile 0 aaa no store/cat/snow.jpg 1
Edit Delete 30 Parts 22 aaa no   1
Edit Delete 31 Accessories 22 aaa no   2
Edit Delete 32 Off Road Bikes 0 Everything for dirt bikes no http://dtdyno.com/store/cat/cr125.jpg 3
Edit Delete 33 Road Bikes 0 Everything for cruiser to cafe no http://dtdyno.com/store/cat/gsxr.jpg 2
Edit Delete 34 HJC 31 Helmet no   1
Edit Delete 40 Reeds 26   no   1
Edit Delete 41 Intake Grates 26   no   2
Edit Delete 44 Accessories 24   no   2

Now parent means, that the direcotry it came from, so this is a sub categgory script, so a pair of categories would be. PWC->Reeds-> and there is much more in the datbase like I have PWC->Reeds->Demo->Pop. What I have been trying to do is, get each category and print them like this, PWC->Reeds->Demo->Pop, but how can I do this I tried something but it doesnt work, the page wont even load. 

(@fer) = find_all_categories();
sub find_all_categories {
 $id = shift;
 if($id) {
  $sthm = $dbh->prepare("SELECT * FROM category WHERE id='$id'");
  $sthm->execute or die $dbh->errstr;
 } else {
  $sthm = $dbh->prepare("SELECT * FROM category");
  $sthm->execute or die $dbh->errstr;
 }
 while (@hi = $sthm->fetchrow_array) {

 if($hi[2] eq "0") {
   push(@env, @hi[0])
 } else {
   $a = find_all_categories($slog[0]);
   $i += $hi[3];
   $i + $a;
 }

 }
 push(@env,$i);
 return(@env);
}

[download]

Replies are listed 'Best First'.
Re: Looping through a subrouting
by DamnDirtyApe (Curate) on Jul 27, 2002 at 18:11 UTC

    I used DBD::CSV to make a small table containing IDs, category names and parent categories. Hopefully you can adapt this to your needs.

    Code 
    #! /usr/bin/perl

use strict ;
use warnings ;
$|++ ;

use DBI ;

my $cat_id = 11 ;

my $dbh = DBI->connect( "DBI:CSV:f_dir=.;csv_eol=\n" )
    or die "Cannot connect: " . $DBI::errstr ;

my $sth = $dbh->prepare( "SELECT * from categories where id = ?" )
    or die "Cannot prepare statement: " . $DBI::errstr ;

print cat_list( $sth, $cat_id ), "\n" ;

sub cat_list
{
    my ( $sth, $cat_id ) = @_ ;

    $sth->execute( "$cat_id" )
        or die "Cannot execute statement: " . $DBI::errstr ;

    my $data = $sth->fetchrow_hashref
        or die "No record found for category " . $cat_id ;

    return $data->{'parent'} == 0
           ? $data->{'name'}
           : cat_list( $sth, $data->{'parent'} )
             . '->'
             . $data->{'name'} ;
}



__END__

    [download]
    categories 
    id,name,parent
1,Catalogue,0
10,Trucks,1
20,Snowmobiles,1
11,Parts,10
12,Accessories,10
21,Parts,20
22,Clothing,20

    [download]

    _______________
    D a m n D i r t y A p e
    Home Node | Email
[reply]
[d/l]
[select]
Re: Looping through a subrouting
by vladb (Vicar) on Jul 27, 2002 at 17:59 UTC
    I know that if it were Oracle database, I could simply execute a query like this: 
    select description, image, org, level from category
connect by parent = prior id
where id = '$id'

    [download]
    This would return hierarchy like structure (parent rows followed by child). level is a special 'hidden' column that is appended to the results of hierarchical queries. This column simply holds a number representing 'level' of the category. So, top most parent will have level of 0, and so on.

    Unfortunately, as per this document Things That Must be Done in the Near Future this feature has not yet been implemented in MySQL. However, they promise us the work is being done in this regard ;)

    In the mean time, I'd like to suggest a few enhancements to your code. Things that should speed your process are caching your query and also using value bindings instead of generating ever changing query string (since $id is changing...) 
    
(@fer) = find_all_categories();
sub find_all_categories {
 $id = shift;
 if($id) {
  $sthm = $dbh->prepare_cached("SELECT * FROM category WHERE id=?");
  $sthm->execute($id) or die $dbh->errstr;
 } else {
  $sthm = $dbh->prepare("SELECT * FROM category");
  $sthm->execute or die $dbh->errstr;
 }
 while (@hi = $sthm->fetchrow_array) {

 if($hi[2] eq "0") {
   push(@env, @hi[0])
 } else {
   $a = find_all_categories($slog[0]);
   $i += $hi[3];
   $i + $a;
 }

 }
 push(@env,$i);
 return(@env);
}

    [download]


    _____________________ 
    # Under Construction

    [download]
[reply]
[d/l]
[select]
Re: Looping through a subrouting
by rattusillegitimus (Friar) on Jul 28, 2002 at 04:32 UTC

    This might be totally overkill for what you're doing, but here goes. My favorite hammer is, surprise surprise, a combination of XML::LibXML and XML::LibXSLT. I've use them on a similar problem where I have a list of bookmarks in arbitrarily-deeply nested folders stored in a databse. My solution was to turn each row in the table into a single XML element, then use XSLT to transform it into HTML. I threw together the following script to generate an XML document containing the categories with the names formatted how you wanted them:

    Read more... (5 kB)
[reply]
[d/l]
[select]
Re: Looping through a subrouting
by Anonymous Monk on Jul 28, 2002 at 04:46 UTC 
    (@fer) = find_all_categories();
sub find_all_categories {
 $id = shift;
 if($id) {
  $sthm = $dbh->prepare_cached("SELECT * FROM category WHERE id=?");
  $sthm->execute($id) or die $dbh->errstr;
 } else {
  $sthm = $dbh->prepare("SELECT * FROM category");
  $sthm->execute or die $dbh->errstr;
 }
 while (@hi = $sthm->fetchrow_array) {

 if($hi[2] eq "0") {
   push(@env, @hi[0])
 } else {
   $a = find_all_categories($slog[0]);
   $i += $hi[3];
   $i + $a;
 }

 }
 push(@env,$i);
 return(@env);
}

    [download]
    Yea im getting this little error

    Can't call method "prepare" on an undefined value at cart.pm line 1612.
[reply]
[d/l]
      It would seem that the function can't "see" $dbh. Did you create $dbh using my? If so, $dbh is out of scope. You'll need to pass it to the function as well along with $id.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom