Perl selectall_array - retrieve all data from array of array ref

Question

I am using DBI selectall_array to fetch all the data out of mysql. My query returns 1 columns for every row. But the selectall_array function returns an array of array ref for each row as listed below

$VAR1 = [
          '1.ptmp'
        ];
$VAR2 = [
          'e1.ptmp'
        ];
$VAR3 = [
          's2.ptmp'
        ];
$VAR4 = [
          'sw1.ptmp'
        ];
$VAR5 = [
          'w1.ptmp'
        ];

What I would like is a way to extract all the domains into a single array (no refs) or is there a DBI function which returns a full array of data without any references? I dont want to loop through all data or do fetchrow_array in a while loop. Looking for a single statement to do it all if possible?

Please see my code below:

my @mysqldata = $dbh->selectall_array('SELECT `domain` FROM dfs WHERE `id` IN ('.join(',',('?')x(scalar(@params))).')', {}, @params);
my @minus = array_minus( @all, @mysqldata );

David Collins · Answer 1 · 2017-12-29T14:54:50.487

5

Since you are only extracting one column you can try the selectcol_arrayref() method - de-referencing the array inline.

my @mysqldata = @{ $dbh->selectcol_arrayref('SELECT `domain` FROM dfs WHERE `id` IN ('.join(',',('?')x(scalar(@params))).')', {}, @params) };

UPDATE

As ysth suggested in the comments, you can use a postfix operator in newer versions of Perl (v5.20.0 and later) to dereference the value returned by selectcol_arrayref() - as in the following.

$dbh->selectcol_arrayref('SELECT domain FROM dfs', undef, @params)->@*;

edited Dec 29 '17 at 14:54

answered Dec 28 '17 at 14:41

David Collins

2,852
9
13

2

Or postfix dereference: `= $dbh->selectcol_arrayref(...)->@*;`. Or just keep it as a reference. – ysth Dec 28 '17 at 17:23
Postfix dereference is a nice new feature, but note it only became available in experimentally `v5.20` and is now stable as of `v5.24` – beasy Dec 29 '17 at 13:29
@ysth: Thanks. I have updated my answer to include your suggestion. – David Collins Dec 29 '17 at 14:55

beasy · Accepted Answer · 2017-12-28T15:43:53.487

2

You can dereference the arrayrefs into one big array in one line like this:

my @all = map {@$_} $dbh->selectall_array($sql);

The map block applies @$_ to each row returned by selectall_array, which dereferences the arrayref.

edited Dec 28 '17 at 15:43

answered Dec 28 '17 at 13:38

beasy

1,227
8
16

Perl selectall_array - retrieve all data from array of array ref

2 Answers2