Anyone familiar with ADO, ADO.NET and PHP will grumble when it appears that DBI, the standard database access module for Perl, prefers to return arrays of values instead of hashes. The reason we want to get hashes is that we can address the elements of our recordset with the names of the columns instead of knowing the number of the column. ADO and ADO.NET objects are powerful enough to allow both numeric (Set.Item(x)) and symbolic (Set.Item('Value')) access but with DBI you need to choose.
For my web application I wrote 2 classes, one a database abstraction class that encapsulates connecting to the database and provides some shortcuts for getting queries executed and second a Business Logic (BL) class that implements the, well, business logic of the application. I'm implementing a test management database so the BL class does such things as entering results, getting lists of tests that fit certain criteria etc.
sub all_array_hashes($) {
my $self = shift;
my $sql = shift;
my $sth = $self->{"dbh"}->prepare($sql, {RaiseError=>1});
$sth->execute();
return $sth->fetchall_arrayref({});
}
Here is the all_array_hashes method of my DB class. Note the standard naming of $sth and the $dbh objects. The tricky part is the empty hash {} being passed into fetchall_arrayref({}). This tells the method to construct an array of references to hashes and return a reference to it. Tada! Try explaining that after drinking a few glasses of alcoholic beverage of choice. I use this method for most queries that return a bunch of rows with several fields. I'm sure I give up some efficiency by doing this but I'm a firm believer in spending a few $$ on proper servers to save lots of $$$ on code writing and maintenance. Of course this is in contradiction to using Perl/MySQL instead of ASP.NET/SQL Server but that's a decision I could not make.
Here is an example of a BL method that uses the method shown above:
sub testcase_results($) {
my ($self, $testcase_id) = @_;
if (!$testcase_id || $testcase_id eq "") {
return 0;
}
my $sql = qq{
SELECT * FROM TestStatus WHERE Testcase_ID=$testcase_id
ORDER BY Date_Time_Finished DESC
};
return $self->{'db'}->all_array_hashes($sql);
}This routine returns a reference to an array of references to hashes that contains all the results for a particular testcase_id. Here is a snippet that uses the result:
my $results = $bl->testcase_results($testcase_id);
my @status_array = @{$results};
for (my $index = 0; $index <= $#status_array; $index++) {
my $status = $status_array[$index]->{'Status'};
$stat_table .= "<tr " . QAUtil::row_css($index) . ">" .
"<td>" . $status_array[$index]->{'Date_Time_Finished'} . "</td>" .
"<td>" . $status_array[$index]->{'Build'} . "</td>" .
"<td class=$status>$status</td>" .
"<tr>\n";
}
You can see how I first dereference the $result parameter into the array @status_array. Then I walk the array, taking care to use <= because $#status_array provides the last valid index of the array, not the number of elements. So each element $status_array[$index] is dereferenced with ->{Element} where Element is the name of the column from our query.
You'll note that I hardcode the table structure rather than using CGI::td functions etc. This is how I code PHP although I use Table, TableRow and TableCell in ASP.NET. I guess the learning curve of figuring out how to make each CGI function do what I wanted to do was a tad steep given the project schedule pressures so I stuck to what I knew. Besides, it's good to keep your hands in the HTML sometimes as that really shows you what you're generating. The QAUtil::row_css() routine returns a css class appropriate for the index. In this case it alternates between class=odd and class=even which gives the rows different colors.
A more Perly way of traversing the array is as follows:
for my $element (@{$element_array}) {
print "<tr><td>" . $element->{SomeField} . "</td></tr\n";
}
Where $element_array was previously returned from testcase_results().
I hope this is useful for someone! If it is please post a comment. Please don't take this example as 'gospel'. There are so many ways of achieving the same goal with Perl and CGI that I won't try very hard to defend mine. It's just what worked for me. Use what you need and find your own way of doing things.