Fetching from your Queries

We have already talked about how to create a SELECT query using Perl and DBI. Here are two ways (there are more, but these two have covered all the bases for me so far!)

First, a little recap on the SELECT. Here is the SELECT statement that I am going to run:

$search=$dbh->prepare("SELECT user_id search_date FROM search_records WHERE company_id = 12345 ");
$search->execute();

This is going to return the user_id field and search_date values where the company_id is equal to 12345. Let's get our information back out with a fetchrow_array

while ( ($user,$searchdate)=$search->fetchrow_array ){
# do something with $user and $searchdate
print "User $user on $searchdate\n"; }

OR

while ( @outarray=$search->fetchrow_array ){
# The order of the information in the array is the same as the order in the SELECT
# statement!
print "User $outarray[0] on $outarray[1]\n"; }

SIMPLE!

Okay, the other fetch I like to use gives me my information in a hash with key values set to column names. This is the preferred method when I have a lot of fields to work with and I don't want to mess around with keeping track of the positions in an array. Let's perform our SELECT again:

$search=$dbh->prepare("SELECT * FROM search_records WHERE company_id = 12345 ");
$search->execute();

We can retrieve our information back out by using fetchrow_hashref:

while ($hash_ref=$sth->fetchrow_hashref){
$user=$hash_ref->{user_id};
$searchdate=$hash_ref->{search_date};
print "User $user on $searchdate\n"; }

With the fetchrow_hashref, you can more easily work with the output of tables with 4 or more columns. The only drawback is that it's slower. fetchrow_array is the most basic and fastest way to get information back from your SELECT statements. It's a matter of preference and convenience as to which method you choose.