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