Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Mysql reply

by Generoso (Vicar)
on Oct 14, 2012 at 04:10 UTC ( #998912=note: print w/ replies, xml ) Need Help??


in reply to SQL Database Table to Perl Script

Maybe this is what you are looking for.

!/usr/bin/perl use DBI; use strict; use warnings; my $dbh = DBI->connect('dbi:mysql:database','user','password') or die +"Couldn't connect"; 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;]; open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar +y file: $!\n"; print FH '<HTML><Body>'; foreach (@sql) { my $tran_cur = $dbh->prepare($_); $tran_cur->execute; print FH '<table border=1 cellspacing=0 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>$_</td>"}@data; print FH "</tr>"; } print FH "</table>"; } print FH "</Body></HTML>\n"; my $url = "file://".$ENV{"TMP"}."\\mytmp.html"; my $commandline = qq{start "$url" "$url"}; system($commandline) == 0 or die qq{Couldn't launch '$commandline': $!/$?}; close FH; $dbh->disconnect; sleep(5); unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM +P"}."\\mytmp.html: $!";

Result:


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

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

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


Comment on Mysql reply
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://998912]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (15)
As of 2015-01-27 13:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My top resolution in 2015 is:

















    Results (200 votes), past polls