creating an array from MySQL results

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

   my $data = qq(SELECT word FROM swearwords);
   my $sth = $dbh->prepare($data);
   $sth->execute() or die $dbh->errstr;

   while(my $ref = $sth->fetchrow_arrayref) 
   {
       push @swearwords, $ref->[0];
   }
[download]

It connects properly to the database but @swearwords contains none of the stores swear words. I know they're in the database because my other script can read them different. I can't get the array to hold them though.

Did I do something wrong?

Comment on creating an array from MySQL results Download Code

Replies are listed 'Best First'.
Re: creating an array from MySQL results by rnahi (Curate) on Oct 30, 2005 at 19:21 UTC
Your code should work as it is. The advice I can give you is the same you may find at Before asking a database related question ..., where it is outlined how to test your SQL before resorting to the script. Additionally, you may see some different ways of creating an array from a DBI call in DBI Recipes. My favorite is : `my $arrayref = $dbh->selectcol_arrayref($query);` [download] HTH	[reply] [d/l]
Re: creating an array from MySQL results by pg (Canon) on Oct 30, 2005 at 19:24 UTC
The code itself does not appear to have any error. I modified your code a little bit to work with real table/column I have, and it worked for me: `use DBI; use Data::Dumper; use strict; use warnings; my $dsn = "DBI:mysql:database=test;host=foo"; my $dbh = DBI->connect($dsn, 'root', 'bar', {RaiseError => 1}); my $data = qq(SELECT person FROM or_mod); my $sth = $dbh->prepare($data); $sth->execute() or die $dbh->errstr; my @a; while(my $ref = $sth->fetchrow_arrayref) { push @a, $ref->[0]; } print Dumper(\@a);` [download] The loop part actually can be changed to a map: `my @a = map {$_->[0]} @{$sth->fetchall_arrayref};` [download]	[reply] [d/l] [select]
Re: creating an array from MySQL results by Juerd (Abbot) on Oct 30, 2005 at 21:34 UTC
Plugging my own module DBIx::Simple seems appropriate :) It has a method called `flat` that flattens whatever the query returned. Ideal for gathering lists like this. All you need after creating the database object (here `$db`) is: `my @swearwords = $db->query("SELECT word FROM swearwords")->flat;` [download] Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply] [d/l]