LinuxQuestions.org - Mysql reading via Perl DBI

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Mysql reading via Perl DBI (https://www.linuxquestions.org/questions/programming-9/mysql-reading-via-perl-dbi-286536/)

Mysql reading via Perl DBI

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) ?

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

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.