Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

How to add MySql Records To Array or Hash?

by jdlev (Scribe)
on Sep 09, 2013 at 17:30 UTC ( [id://1053052]=perlquestion: print w/replies, xml ) Need Help??

jdlev has asked for the wisdom of the Perl Monks concerning the following question:

I still continue to struggle with this stuff. I just can't seem to form an array based on a mysql query using perl. I was hoping someone could show me a working example. My first question is should I use an array or a hash to store the data? Here is what I'm trying to do:

I was hoping someone could break this down for me in steps:

ex.
Drew Brees ID=12345 Position=QB Salary=15,000 Points=20
Aaron Rodgers ID = 54321 Postition=QB Salary=14,500 Points=19

Step 1: Get salary data & insert into an array/hash based on player ID as key, and player salary as value.

So the array/hash for @QBsalary would simply be represented as [12345 => 15,000, 54321=>14,500]

Step 2: Get the points data & insert into an array/hash based on player ID as key, and player points as value.

So the array/hash for @QBpoints would simply be represented as  [12345 => 20, 54321=>19]

Step 3: How would I iterate through every QB pulling in both their salary & points? This is very confusing to me. Thanks for any help!

I love it when a program comes together - jdhannibal

Replies are listed 'Best First'.
Re: How to add MySql Records To Array or Hash?
by Generoso (Prior) on Sep 09, 2013 at 19:15 UTC

    This example will execute all the SQL stored in the @SQL array en generate a temp file with HTML code with the results.

    #!/usr/bin/perl use DBI; use strict; use warnings; my $dbh = DBI->connect('dbi:mysql:test2','user','password') or die "Ca +nnot connect: $DBI::errstr\n"; my @sql =(); $sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count( +*) as Total_No, sum(Amount) as Amount FROM transaction GROUP BY AccNum, Type union SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A +mount,Amount)) FROM transaction GROUP BY AccNum;]; $sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU +ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount FROM transaction where chequeno is not null GROUP BY AccNum, Type union SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, +count(*) as Total_No, sum(Amount) as Amount FROM transaction where DDNO is not null GROUP BY AccNum, Type;]; $sql[2] = qq[desc transaction;]; $sql[3] = qq[select * from animals3;]; $sql[4] = qq[select * from transaction;]; $sql[5] = qq[call test.coldif("test.animals","test2.animals")]; open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar +y file: $!\n"; print FH '<HTML><Body>'; foreach (@sql) { my $l = $_; $l =~ s/\n/<br>/g; print FH '<p>'.$l.'</p>'; my $tran_cur = $dbh->prepare($_)or print FH '<p><font color="red"> +Cannot prepare: '."$DBI::errstr</font></p>"; $tran_cur->execute or print FH '<p><font color="red">SQL Error: '. +"$DBI::errstr</font></p>"; unless(defined $DBI::err){ print FH '<table border=1 cellspacing=1 cellpadding=2><tr>'; map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}}; print FH "</tr><br/>"; while( my @data = $tran_cur->fetchrow_array) { print FH "<tr>"; map {print FH "<td>"; my $r = defined ($_) ? $_ : '&nbsp;'; print FH length ($r)>0 ? $r: '&nbsp;'; print FH "</td>";}@data; print FH "</tr>"; } print FH "</table>"; } } print FH "<p>".$ENV{"TEMP"}."\\result.html</p>\n"; my $url = "file://".$ENV{"TMP"}."\\mytmp.html"; my $commandline = qq{start "$url" "$url"}; system($commandline) == 0 or die qq{Couldn't launch '$commandline': $!/$?}; print FH "</Body></HTML>\n"; close FH; $dbh->disconnect; sleep(5); unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM +P"}."\\mytmp.html: $!"; #print $ENV{"TEMP"}."\\result.html"; __DATA__ SELECT AccNum, Type, SUM(Amount) FROM transaction GROUP BY AccNum, Typ +e; SELECT AccNum, count(*), max(date), Type, sum(Amount) FROM transaction + GROUP BY AccNum, Type union SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A +mount,Amount)) FROM transaction GROUP BY AccNum; SELECT * FROM transaction t where chequeno is not null or ddno is not +null; SELECT AccNum, Type, sum(Amount) FROM transaction e GROUP BY AccNum, T +ype; DROP TABLE IF EXISTS test2.transaction; CREATE TABLE test2.transaction ( TranID int(10) unsigned NOT NULL, Date date NOT NULL, AccNum int(10) unsigned NOT NULL, Type enum('Credit','Debit') NOT NULL, Amount decimal(10,2) NOT NULL, ChequeNo int(10) unsigned DEFAULT NULL, DDNo int(10) unsigned DEFAULT NULL, PRIMARY KEY (TranID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into entries values(TranID,Date,AccNum,Type,Amount,ChequeNo,DDN +o'); insert into entries values(657520,'1999-07-02',0181432,'Debit',16000,4 +65774,null); insert into entries values(657524,'1999-07-02',0181432,'Debit',13000,n +ull,569086); insert into entries values(657538,'1999-07-09',0181432,'Credit',11000, +null,null); insert into entries values(657548,'1999-07-18',0181432,'Credit',15500, +null,null); insert into entries values(657519,'1999-07-02',0181432,'Debit',12000,n +ull,null); insert into entries values(657523,'1999-07-02',0181432,'Credit',11000, +null,null); insert into entries values(657529,'1999-07-03',0181432,'Debit',15000,4 +66777,null); insert into entries values(657539,'1999-07-10',0181432,'Credit',10000, +null,null); insert into entries values(657541,'1999-07-11',0181432,'Debit',12000,n +ull,null); insert into entries values(657525,'1999-07-03',0181432,'Debit',15000,n +ull,569999); insert into entries values(657533,'1999-07-05',0181432,'Credit',12500, +null,null);

    Result

    SELECT AccNum as Account, max(date) as Date, Type, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction GROUP BY AccNum, Type
    union
    SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*Amount,Amount))
    FROM transaction GROUP BY AccNum;


    AccountDateTypeTotal_NoAmount
    1814321999-07-18Credit560000.00
    1814321999-07-11Debit683000.00
    1814321999-07-18Balance11-23000.00

    SELECT AccNum as Account, max(date) as Date, Type, 'CHEQUENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction where chequeno is not null GROUP BY AccNum, Type
    union
    SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction where DDNO is not null GROUP BY AccNum, Type;


    AccountDateTypeSubtypeTotal_NoAmount
    1814321999-07-03DebitCHEQUENO231000.00
    1814321999-07-03DebitDDNO228000.00

    desc transaction;


    FieldTypeNullKeyDefaultExtra
    TranIDint(10) unsignedNOPRI  
    DatedateNO   
    AccNumint(10) unsignedNO   
    Typeenum('Credit','Debit')NO   
    Amountdecimal(10,2)NO   
    ChequeNoint(10) unsignedYES   
    DDNoint(10) unsignedYES   

    select *
    from animals3;

    SQL Error: Table 'test2.animals3' doesn't exist

    select *
    from transaction;


    TranIDDateAccNumTypeAmountChequeNoDDNo
    6575191999-07-02181432Debit12000.00  
    6575201999-07-02181432Debit16000.00465774 
    6575231999-07-02181432Credit11000.00  
    6575241999-07-02181432Debit13000.00 569086
    6575251999-07-03181432Debit15000.00 569999
    6575291999-07-03181432Debit15000.00466777 
    6575331999-07-05181432Credit12500.00  
    6575381999-07-09181432Credit11000.00  
    6575391999-07-10181432Credit10000.00  
    6575411999-07-11181432Debit12000.00  
    6575481999-07-18181432Credit15500.00  

    call test.coldif("test.animals","test2.animals")


    idnamecategoryidpFechaCost
    1doghome pet != pet12009-01-11 
    2cathome pet22009-05-06000222.23 != 300.00
    4lax != lax2wile12009-09-01000123.44
    5whalefish1 != fish22009-11-12000100.00 != Null
    9test1 != test2test1 != test212008-01-01000150.10
    12 != NulltestNull1 != NullTesttNull != Null5 != Null  
    18 != Nullcastor != NullWiled != Null7 != Null2010-09-15 != Null000001.01 != Null
    Null != 11Null != testNull2Null != TesttNullNull != 5  

    C:\Users\GMONTE~1\AppData\Local\Temp\result.html

Re: How to add MySql Records To Array or Hash?
by McA (Priest) on Sep 09, 2013 at 18:45 UTC

    Hi,

    the decision whether to use arrays of hashes depends on the size of the result set and other aspects (e.g. maintainability). Hashes and access by field names makes it IMHO more readable.

    I would propose the following structure:

    my %hash = ( 12345 => { id => 12345, position => 'QB', salary => 15000, points => 20, }, 54321 => { id => 54321, position => 'QB', salary => 14500, points => 19, }, );

    To get this structure from a database via DBI have a look at http://search.cpan.org/dist/DBI/DBI.pm#selectall_hashref.

    Regards
    McA

      That looks a lot like what I'm looking for. It says in the DBI documentation that the $key_fields should be called as an array if you want to call multiple columns. Any idea on how to do that? I haven't been able to find any example :(

      I love it when a program comes together - jdhannibal

        You don't need it IMHO as you have ID as primary key, so just set $key_fields to the field name 'ID'.

        One thing you have to be aware of is the case of the identifier. Look at NAME_uc and NAME_lc in the DBI documentation.

        Regards
        McA

Re: How to add MySql Records To Array or Hash?
by trippledubs (Deacon) on Sep 09, 2013 at 18:50 UTC

    I'm kind of a beginner, but here is how I would start. I like to use SQL::Abstract and then use empty braces in the fetchall statement to make each element in the array a hash of that row from the SQL table.

    #!/usr/bin/env perl use strict; use warnings; use SQL::Abstract; use DBD::mysqlPP; use Data::Dumper; my $dsn = "dbi:mysqlPP:database=$database;host=$hostname"; my $dbh = DBI->connect($dsn,$user,$pass); my $sql = SQL::Abstract->new; my ($stmt,@bind) = $sql->select('quarterbacks'); my $sth = $dbh->prepare($stmt); $sth->execute(@bind); my $results = $sth->fetchall_arrayref({});

    Your results ref is an array of hashes. So your first full record might be

    $results->[0]; // Reference to a hash ref of Row_1 my %row_1 = %{$results->[0]}; // Hash of Row 1 $results->[0]{name}; // Name column from Row_1 // to iterate over the record set you might do this: // id, position, name are column names from the table for (@{$results}) { my $id = $_->{id}; my $position = $_->{position}; my $qb_name = $_->{name}; }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1053052]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (5)
As of 2024-03-28 16:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found