in reply to MySQL results inserted into Perl variable

You are only fetching one array into @record at a time, so it should be like this: 
my $sql = "select * from location";
my $sth = $dbh->prepare( $sql );
$sth->execute();
while (my @record = $sth->fetchrow_array) {
  print join(", ", @record);
  print "\n";
}
$dbh->disconnect();

[download]
Here is another way to do it 
#!/usr/bin/env perl
use strict; 
use warnings;
use DBI;
use SQL::Abstract;
use Data::Dump;


my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
my $sql=SQL::Abstract->new;

# See perldoc SQL::Abstract
my($stmt, @bind) = $sql->select('location'); 

my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);
my $data  = $sth->fetchall_arrayref({});

dd @$data[0]; # First row from the database as hashref
dd @$data[1]; # Second row from the database as hashref
dd $data->[0]{field1}; #field1 from first row

[download]

Using SQL::Abstract will help avoid SQL injection attacks http://www.stonehenge.com/merlyn/UnixReview/col58.html

You can also grow into DBI::Class and other Object Relational Mappers later on if you need.

Replies are listed 'Best First'.
Re^2: MySQL results inserted into Perl variable
by locked_user sundialsvc4 (Abbot) on Jul 09, 2014 at 01:06 UTC

    The only change that I would make to the foregoing is that I would choose to specify the column-name, not as a bareword, but as a literal string, viz:

    ... $data->[0]{'field1'};

    By far, the most common practice in any case is to let the row of data be returned as a hashref, then to retrieve the fields from it by column-name.   If you feel cozy working with Perl variable-names from that point forward (and there’s certainly something to be said for that ...), that’s easily done.   After retrieving the row as a hashref, do this:

    my $field1 = $data->[0]{'field1'};

      ... not as a bareword, but as a literal string, ...

      Provided the bareword is a “single identifier” (i.e., something that would be a valid variable name if preceded by the appropriate sigil), Perl parses it as a string literal when it’s used as a hash subscript. From the Camel Book (4th Edition, p. 70):

      As in some shells, you can put braces around the identifier to distinguish it from following alphanumerics: "How ${verb}able!". An identifier within such braces is forced to be a string, as is any single identifier within a hash subscript. For example:

      $days{"Feb"}

      [download]

      can be written as:

      $days{Feb}

      [download]

      and the quotes will be assumed. Anything more complicated in the subscript is interpreted as an expression, and then you’d have to put in the quotes...

      (Emphasis added.)

      Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

      The only change that I would make to the foregoing is that I would choose to specify the column-name, not as a bareword, but as a literal string, viz:

      So you have more to type?

      my $field1 = $data->[0]{'field1'};

      That is not stupid at all , you really should have $field99 instead of that pesky $data array

[reply]

In Section Seekers of Perl Wisdom