Get all data from DB using fetchall_hashref

Question

I am using fetchall_hashref to get data satisfying a condition from a mysql DB.The data retrieved will be stored in a hash and then be used futhur by a javascript function.

I want to retrieve all the rows of 3 columns and store it in a hash but not being able to do that.

The table structure..

table structure

the table data..

table data

the code being used..

#!/usr/bin/perl -w
#print "Content-type: text/html\n\n";
use DBI;
use CGI;
use strict;
use warnings;
use diagnostics;
use Data::Dumper;

use CGI::Carp qw (fatalsToBrowser);
my $q = CGI->new;
print  $q->header;
my $dsn = "DBI:mysql:Demo:localhost";   # Data source name
my $username = "mint";                 # User name
my $password = "MINT123";              # Password
my $dbh;
my $sth;    
my $b;
my $c;  
        # Database and statement handles
 $dbh = DBI->connect($dsn, $username, $password);


 my $hash = $dbh->selectall_hashref("SELECT `desc`,date1,riskval from FIR2 where date1 between date_sub(now(),INTERVAL 1 WEEK) and now() order by date1", 'riskval');
 print Dumper($hash);
 $b=join(",",$hash);
 delete $_->{riskval} for values %$hash;

 $dbh->disconnect();

The output i am getting in browser...

browser output

the output in putty..

enter image description here

As you can see I want to print the row where "riskval" is null,the value of "riskval" is 5 in 2 places but only 1 row is getting printed..

after i replaced selectall_hashref with selectall_arrayref i got the following error message in putty..

second error message

Please help..

hash can't have multiple identical keys so you'll have to change approach by using hash of arrays or something similar. — mpapec, May 31 '13 at 05:17
sure, http://perldoc.perl.org/perldsc.html#Declaration-of-a-HASH-OF-ARRAYS — mpapec, May 31 '13 at 08:52
i mean an example similar to mine where they values are being retrieved from a mysql DB and stored in hash of arrays.. — Lucy, May 31 '13 at 09:35

Dave Sherohman · Answer 1 · 2013-05-31T11:53:54.103

4

i mean an example similar to mine where they values are being retrieved from a mysql DB and stored in hash of arrays

According to the DBI documentation,

If $slice is a hash reference, fetchall_arrayref fetches each row as a hash reference...

For example, to fetch all fields of every row as a hash ref:

$tbl_ary_ref = $sth->fetchall_arrayref({});

Replace your fetchall_hashref with that and you should get all records as an array of hashes, including those with duplicate riskvals.

Edit: I missed that OP is using selectall_* rather than fetchall_*. In that case, the relevant section of the DBI documentation is:

You may often want to fetch an array of rows where each row is stored as a hash. That can be done simple using:
my $emps = $dbh->selectall_arrayref(
    "SELECT ename FROM emp ORDER BY ename",
    { Slice => {} }
);

edited May 31 '13 at 11:53

answered May 31 '13 at 09:52

Dave Sherohman

45,363
14
64
102

i replaced "fetchall_hashref" with "fetchall_arrayref" and got the error message in putty as posted above in the second picture... – Lucy May 31 '13 at 10:46
Again, look at the documentation. Their example for using `selectall_arrayref` to get an array of hashes is: `my $emps = $dbh->selectall_arrayref( "SELECT ename FROM emp ORDER BY ename", { Slice => {} } );` Note that the only parameters are the query and `{ Slice => {} }`. – Dave Sherohman May 31 '13 at 11:50

Get all data from DB using fetchall_hashref

1 Answers1