Using Multiple Statements MySQL

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

I can't pur this MySQL statement in the while loop cause it will display the second returned value of the second statement and not the first.

 $sth = $dbh->prepare("SELECT id,name FROM category WHERE parent IS NU
+LL");
 $sth->execute or die $dbh->errstr;

 while ( @slog = $sth->fetchrow_array ) {

    $sth = $dbh->prepare("SELECT id,name FROM category WHERE parent = 
+'$slog[0]'");
    $sth->execute or die $dbh->errstr;
    $rv = $sth->rows;
    $subs = $rv;

    print qq~
       <tr>
        <td bgcolor="#CCCCCC" class="darktext" width="200">
         $slog[1]
        </td>
        <td bgcolor="#CCCCCC" class="darktext" width="50">
        $subs
        </td>
        <td bgcolor="#CCCCCC" class="darktext" width="50">
        506
        </td>
        <td align="center" bgcolor="#CCCCCC" class="darktext" width="7
+0">
        settings
        </td>
       </tr>
    ~;
 }
[download]

How else could I do this

Comment on Using Multiple Statements MySQL Download Code

Replies are listed 'Best First'.
Re: Using Multiple Statements MySQL by gryphon (Abbot) on Jul 19, 2002 at 20:05 UTC
Greetings andrew, Always use strict. Thereafter, I'd prepare the second query outside the while using a different name than your first. With DBI, you can have multiple queries running this way. Also, bind values (also known as placeholders) via DBI are your friend. `use strict; my $sth_null = $dbh->prepare(q{ SELECT id, name FROM category WHERE parent IS NULL }); $sth->execute or die $dbh->errstr; my $sth_parent = $dbh->prepare(q{ SELECT id,name FROM category WHERE parent = ? }); while ( my @slog = $sth_null->fetchrow_array ) { $sth_parent->execute($slog[0]) or die $dbh->errstr; my $rv = $sth_parent->rows; my $subs = $rv; ...` [download] Using placeholders with your prepare and execute statements lets DBI deal with making sure quotes are handled correctly. It also makes for nicer looking code, IMHO. -gryphon code('Perl') \|\| die;	[reply] [d/l]
Re: Using Multiple Statements MySQL by Nightblade (Beadle) on Jul 19, 2002 at 19:40 UTC
use another variable name for inner query: like $sth2, $sth_inner, etc. `$sth = $dbh->prepare("SELECT id,name FROM category WHERE parent IS NUL +L"); $sth->execute or die $dbh->errstr; while ( @slog = $sth->fetchrow_array ) { $sth2 = $dbh->prepare("SELECT id,name FROM category WHERE parent = + '$slog[0]'"); $sth2->execute or die $dbh->errstr; ...` [download]	[reply] [d/l]
Re: Using Multiple Statements MySQL by gav^ (Curate) on Jul 19, 2002 at 21:07 UTC
You could just save some trips and do: `# get a hash ref keyed to id my $data = $dbh->selectall_hashref( 'select id, name, parent from category', 'id' );` [download] Then you can work out yourself what is the parent of each item. gav^	[reply] [d/l]