creating an array from MySQL results

[Anonymous Monk]

   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];
   }

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?

[rnahi]

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

HTH

[pg]

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

The loop part actually can be changed to a map:

my @a = map {$_->[0]} @{$sth->fetchall_arrayref};

[Juerd]

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;