retrieve data from array empty

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

I'm relatively new to Perl, but I'm trying to retrieve data from mysql. The first statement is working fine. But in the second statement, it always retrieves "0", which of course is not the case. If I enter the statement in phpMyAdmin it retrieves the correct data. The problem is that I can't get the correct data. Any hints on this? My (stripped) code:

#!/usr/bin/perl

use DBI;

$DBH = DBI->connect("DBI:mysql:database=$DB_DATABASE;host=$DB_SERVER",
+$DB_USER,$DB_PASS);

my $lastmonth=$mon-1;
my $statement = "select deb.id from fte_intra.debiteuren deb where deb
+.type='sms' AND (deb.Afloop_contract='0000-00-00' OR month(deb.Afloop
+_contract)='$lastmonth') group by deb.id order by deb.id";
my $sth = $DBH->prepare($statement);
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) 
    {
    $invoice_cid = $ref->{'id'};
    my $statement = "select mc.cid,mc.mail_month from gmsms.mail_count
+ mc where mc.cid = '$invoice_cid' AND mc.month='$lastmonth' AND mc.ye
+ar='$yearG'";
    my $sth = $DBH->prepare($statement);
    $sth->execute();
    toLog("$statement");
    while(my $mc = $sth->fetchrow_array()) {
    $invoice_mccid = $mc->{'cid'};
    toLog("In table: $invoice_mccid");
    if ($invoice_mccid) {
    $invoice_mail_month = $mc->{'mail_month'};
    toLog("In table: $invoice_mail_month");
    }
    else {$invoice_mail_month = '0'};
    toLog("Niet in table: $invoice_mail_month");
    }    
    toLog("INVOICE: $invoice_number [CID: $invoice_cid / MAIL: $invoic
+e_mail_month]");
[download]

Comment on retrieve data from array empty Download Code

Replies are listed 'Best First'.
Re: retrieve data from array empty by Corion (Patriarch) on May 24, 2015 at 09:32 UTC
I don't have experience with how MySQL behaves, but many other database drivers do not like having more than one statement handle active. My approach to check this would be to first fetch all rows from your first SQL statement and then loop over them to fetch the rows for the second SQL statement: my @customers= $dbh->selectall_arrayref(<<SQL, { Slice => {} }); select deb.id from fte_intra.debiteuren deb where deb.type='sms' AND (deb.Afloop_contract='0000-00-00' OR month(deb.Afloop_contract +)='$lastmonth') group by deb.id order by deb.id SQL for my $ref (@customers) { { $invoice_cid = $ref->{'id'}; my $statement = "select mc.cid,mc.mail_month from gmsms.mail_count + mc where mc.cid = '$invoice_cid' AND mc.month='$lastmonth' AND mc.ye +ar='$yearG'"; my $sth = $DBH->prepare($statement); ... }; [download] Personally, I would try to eliminate the two database queries in favour of one SQL statement that combines finding out who is to be billed and the sent bills, potentially something like `select deb.id , mc.cid , mc.mail_month from fte_intra.debiteuren deb left join gmsms.mail_count mc on (mc.cid = deb.id) where deb.type='sms' AND (deb.Afloop_contract='0000-00-00' OR month(deb.Afloop_contract)= +'$lastmonth') AND mc.month='$lastmonth' AND mc.year='$yearG' group by deb.id order by deb.id` [download] That way you offload most of the work onto the database. On the Perl side, you now need to remember the last customer and find out the places in the resultset where a new customer begins.	[reply] [d/l] [select]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: retrieve data from array empty by afoken (Chancellor) on May 24, 2015 at 09:37 UTC
The code you posted ... does not compile lacks `use strict;` and `use warnings;` is not properly indented does not use placeholders in SQL may have $lastmonth set to nonsense values uses the same variable $sth for two different statement handles (that may work because of different scopes) You should repair those problems first. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
A reply falls below the community's threshold of quality. You may see it by logging in.