Linux Documentation - Building source, Linux kernel, cross compiling, embedded


		Wednesday February 22, 2012 19:50:58 UTC

Different ways to pull MySQL data in Perl

One of the reasons I continue to use Perl for any MySQL or other DB access is because of its very cool functionality for pulling database data in a variety of different formats with few lines of code. Here is a breakdown of some of those functions I use all the time for work.

#!/usr/bin/perl

use DBI ;
use Data::Dumper ;

my $dbh= DBI->connect("dbi:mysql:;host=localhost", "user", 'password') ;

selectall_arrayref - return reference to array of hashes

my $p_ = $dbh->selectall_arrayref("SELECT * FROM testdb.testtable", { Slice => {} }) ;

print Dumper($p_) ;

# Combine multiple database tables with similar columns together into one hash table with the top-level key set to a field of your choosing

my %h ;

foreach ( @{ $dbh->selectall_arrayref("SELECT cola,colb,colc FROM testdb.testtable1", { Slice => {} } ) } )
					{ push @{$h{$_->{cola}}}, $_ }

foreach ( @{ $dbh->selectall_arrayref("SELECT cola,colb,colc FROM testdb.testtable2", { Slice => {} } ) } )
					{ push @{$h{$_->{cola}}}, $_ }

print Dumper(\%h) ;

# Hash table with values set to an array indexed by bit and array values set to DB row name/value pair

my %h ;
foreach ( @{ $dbh->selectall_arrayref("SELECT field, bit, name FROM testdb.testtable", { Slice => {} } ) } )
                                        { $h{$_->{field}}[$_->{bit}]= $_ }

print Dumper(\%h) ;

selectall_hashref - return reference to hash table of hash tables where the top-level key is set to a field of your choosing

my $p_ = $dbh->selectall_hashref("SELECT cola,colb,colc,cold FROM testdb.testtable", 'colb') ;

print Dumper($p_) ;

# Return hash table instead of reference to hash table

my %h= %{ $dbh->selectall_hashref("SELECT cola,colb,colc,cold FROM testdb.testtable", 'colb') } ;

print Dumper(\%h) ;

selectcol_arrayref - return reference to array of a column

my $p_ = $dbh->selectcol_arrayref("SELECT cola FROM testdb.testtable") ;

print Dumper($p_) ;

# Return array instead of reference to array

my @a = @{ $dbh->selectcol_arrayref("SELECT cola FROM testdb.testtable") } ;

print Dumper(\@a) ;

# Return key value pair of two columns

my %h = @{ $dbh->selectcol_arrayref("SELECT cola,colb FROM testdb.testtable", { Columns => [1, 2] }) } ;

print Dumper(\%h) ;

selectrow_array - return one row of columns

my ($cola,$colb,$colc,$cold)= $dbh->selectrow_array("SELECT cola,colb,colc,cold FROM testdb.testtable") ;