Joomla query apparently running more than once

Question

I have a reporting component in the administrator of a Joomla site. The user can choose from a dropdown list of available report types, which then calls a function to run the query and output a CSV file. For the most part this works. However, for one report, I'm getting a different number of results through the Joomla function than I do if I run the query directly in mySQL. I added an error_log to see what was happening, and it appears as if the query is running twice. (Maybe.)

Here's the code that calls the function:

function getFullRedeemActivity($start, $end){

    return getReportFullRedeemActivityByDate("v.UpdateDT", strtotime($start),strtotime($end));

}

$start and $end are passed in; no problems there. The issue is in the getReportFullRedeemActivityByDate function. Here's the code for that:

function getReportFullRedeemActivityByDate($start, $end, $limitStart=null, $limitRows=null){
    //open db
    $db =& JFactory::getDBO();
    $where = ($low && $high) ? " and v.UpdateDT between ".$db->quote($low)." and ".$db->quote($high) : "";
     $sort = "v.UpdateDT";
     $limit = (is_int($limitStart) && is_int($limitRows)) ? " limit ".$db->quote($limitstart).", ".$db->quote($limitRows) : "";

    //set query
    $query = "select    r.RedeemAmt,
            v.VoucherNbr, v.BalanceInit, v.UpdateDT, v.BalanceCurrent, 
    m.SkuAbbr, m.MerchantNm,
    a.name,  a.email, a.company, a.address1, a.address2, a.city, a.state, a.zip, a.phone

            from arrc_RedeemActivity r
            left outer join arrc_Voucher v on v.VoucherID = r.VoucherID
            left outer join arrc_Merchant m on m.MerchantID = r.MerchantID
            left outer join jos_customers_addresses a on a.id = r.AcctID
           {$where} 
            order by {$sort} {$limit}";

    $db->setQuery($query);
    if (!$db->query()) error_log($db->stderr());

    if (!$db->getNumRows()){
        JError::raiseWarning( 100, 'No records returned' );
        return false;
    }
    else{
        error_log("there are ". $db->getNumRows()." rows");
    }

    //loop out records into array
    foreach ($db->loadAssocList() as $row){
        $data[$row['BalanceCurrent']] = $row;
        return $data;
    }
}

When I look at my error_log, what I see is:

[Tue Oct 19 09:37:30 2010] [error] [client xxx.xx.xx.xxx] there are 5 rows, referer: http://mysite.com/administrator/index.php?option=com_arrcard&section=reports
[Tue Oct 19 09:37:30 2010] [error] [client xxx.xx.xx.xxx] there are 2 rows, referer: http://mysite.com/administrator/index.php?option=com_arrcard&section=reports

Just like that; one after the other. Given that the line that prints that out is not inside a loop, I can't figure out why it's apparently running through that section twice.

Any ideas?

You could try adding `_SERVER["REQUEST_TIME"]` to the error log to see whether it's the same request the two queries are being made in, or whether the resource gets called twice — Pekka, Oct 19 '10 at 14:49
If it's two different requests: The most frequent reason why something gets called twice is an empty reference to a resource, e.g. `` will try loading the current page into the image — Pekka, Oct 19 '10 at 14:50
OK, I found where part of the problem was after adding request time: there are two separate places that it was printing out that line - one in the actual function where the query was running, and once in the function that the query data was returned to. So, apparently it's the foreach line causing the problem, as I'm getting the correct rownum before that, but the array returned as $data doesn't have the correct number of elements. — EmmyS, Oct 19 '10 at 15:08

score 0 · Accepted Answer · answered Oct 19 '10 at 15:24

It turns out that not all records being returned had a value in the BalanceCurrent column, so when the foreach hit this line:

 $data[$row['BalanceCurrent']] = $row;

it wasn't creating a row for those records. I switched the field name to one of the id columns that all rows definitely have, and everything's fine.

Joomla query apparently running more than once

1 Answers1