On Fri, Dec 27, 2002 at 05:17:54PM -0500, Joel Gwynn wrote:
> Hello all. I have a general dbi question. In the past, I've done
> something like this:
>
> my $sql = "select id, sku, description from widgets";
> my $gw = $dbh->prepare($sql)
> $gw->execute;
> While (my $r = $gw->fetchrow_hashref){
> my $sql = "select sum(available) from inventory where
> widget_id=$$r{id}";
> my $ga = $dbh->prepare($sql);
> $ga->execute;
> $$r{available} = $ga->fetchrow_hashref;
> $ga->finish;
> push(@widgets, $r); # for an HTML::Template loop
> }
> $gw->finish;
>
> I'm just wondering, is it terrible to use the above method when I can
> get away with it, portability issues aside? Should I be hanging my head
> in shame for having lived this way so long? What are some other issues?
The easiest solution is to do all of the work in the SQL statement,
instead of micromanaging your database within a Perl script. Here's
an SQL statement that will select all the widgets, and the sum of their
'available' values in a single statement (tested with MySQL):
SELECT widgets.id, sku, description, sum(available) as available
FROM widgets INNER JOIN inventory ON widgets.id = inventory.id
GROUP BY inventory.id
If that (or a suitable variant) doesn't work with your SQL database,
then you can fix this by selecting out all of the widgets before
calculating their sums:
my $dbh = DBI->connect("DBI:mysql:database=dbi_test");
my $sql = "SELECT id, sku, description FROM widgets";
my $widgets = $dbh->selectall_hashref($sql, 'id');
my $stmt = $dbh->prepare("SELECT sum(available)
FROM inventory
WHERE id=?");
foreach my $id (keys %$widgets) {
$stmt->execute($id);
my ($available) = $stmt->fetchrow_array();
$widgets->{$id}->{available} = $available;
}
Z.
_______________________________________________
Boston-pm mailing list
[EMAIL PROTECTED]
http://mail.pm.org/mailman/listinfo/boston-pm
