Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Mysql reply

by Generoso (Parson)
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 drinking their drinks and smoking their pipes about the Monastery: (11)
As of 2015-07-05 10:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (61 votes), past polls