mysql DBI

Andrew Wilson andrew at rivendale.net
Sat Aug 25 15:23:13 CDT 2001


On Sat, Aug 25, 2001 at 06:18:09PM +0000, Scott McWhirter wrote:
> hey all,
> 
> this is more a 'what would you do' question than anything else. I'm 
> wondering... What's the best way to get back multiple fields in muliple rows  
> from a table. An example would be well appreciated.

There are lots of options, i'll give you my favourite.  This sets up a 
statement handle, binds some variables to the values that will be returned
and then does a little loop.  Every time you call $sth->fetch it sets the
three bound variables to the values of the next row.

my $query = qq{ # Q-Some_identifier-001
  SELECT foo bar baz
    FROM animal
   WHERE goes = ?
  };

my $sth = $dbi->prepare($query);

$sth->execute('moo') or die "can't $query";
$sth->bind_columns(\my ($foo, $bar, $baz)) or dir "can't bind columns $query";

while ($sth->fetch) {
  print "Foo = $foo";
  print "Bar = $bar";
  print "Baz = $baz";
}

Or you can do something like:

my $query = qq{ # Q-Some_identifier-001
  SELECT foo bar baz
    FROM animal
   WHERE goes = ?
  };

my @lol = @{ $dbh->selectall_arrayref($query, undef, 'moo') };

foreach my $row (@LOL) {
  my @row = @{ $row };
  print "Foo = $row[0]";
  print "Bar = $row[1]";
  print "Baz = $row[1]";
}

These two examples have showed how to set up a statement handle and
then call a fetch method on that or just call select method directly
on the database handle.  You can do things to get back hashes where
the keys of the hash are the column names in your table this is all
pretty well covered in the DBI POD.  DO perldoc DBI.

cheers

Andrew



More information about the Belfast-pm mailing list