Mysql reading via Perl DBI

ivanatora · 02-05-2005, 08:12 AM

I use the DBI module and I wander if my way to retrieve data from tables is good.
There are a few rows in the table, I scan them all and build an array from each of it:

Code:

my $sth = $dbh->prepare ("SELECT date FROM msgs");
$sth->execute();
while (@a_date = $sth->fetchrow_array){
push(@dates,@a_date)}

$sth = $dbh->prepare ("SELECT nick FROM msgs");
$sth->execute();
while (@a_nick = $sth->fetchrow_array){
push(@nicks,@a_nick)}

$sth = $dbh->prepare ("SELECT msg FROM msgs"); 
$sth->execute();
while (@a_msg = $sth->fetchrow_array){
push(@msgs,@a_msg)}

I have 3 arrays: dates, nicks and msgs that holds the whole info from the table, but that seems kind of slow to me. Is there a better way to do that job in Perl (with DBI module) ?

chrism01 · 02-06-2005, 10:46 PM

As usual, TIMTOWTDI, but this would work:

Code:

$sth = $dbh->prepare ("SELECT date, nick, msg FROM msgs");
$sth->execute();
while( $date, $nick, $msg) = $sth-fetchrow() )
{
    $info{$nick} = [ $date, $msg ];
}

creates a hash keyed on nick. Depends what you want to do with the data really.
The "while()" line could be
while( @p_info = $sth->fetchrow() )
for the same SQL as I've used.... and do some thing different with the hash assignment line...

HTH

Sunil Kartikey · 03-21-2007, 04:03 AM

preparing a query is a relatively expensive operation.
if you have single table .. select * from the table fetch all data inside a hash or array and use it where ever required.. no need to prepare saperately for each column date nick and msg.