Is there a more efficient way to run queries inside a loop? Memory issues.

Question

I have a Joomla website that I've written a custom shopping cart component for. The user is basically purchasing codes we're storing in our database - these are associated with a printed incentive card. When the user checks out, I need to grab a chunk of codes from the database (however many they've purchased), then loop through the list of codes and update other tables with information in my cart. The cart is stored as an array of arrays in a session variable, like this:

$cart = Array ( 
[0] => Array ( [TypeFlag] => S [qty] => 25 [denom] => 50  [totalPrice] =>  100 )
[1] => Array ( [TypeFlag] => V [qty] => 10 [denom] => 25  [totalPrice] => 25 ) 
[2] => Array ( [TypeFlag] => C [qty] => 100 [denom] => 25  [totalPrice] => 25 ) 
) 

where each internal array is one line item in the cart. It's the qty that's causing the problem; when they're low there's no problem running all the insert and update queries inside the loop. However, when the qty elements are high, I start getting memory allocation errors. This is understandable, as it's basically running several queries hundreds of times. The issue is, a user could potentially order a thousand cards or more at a time (this is a corporate incentive program), so I need to be able to get all records inserted and updated, regardless of how big the qty is.

Here's the relevant code:

First, the loop:

//loop through vouchers to create purchase records, update voucher records, create certificates
$rightNow = date("YmdHis");
foreach($vouchers as $voucher) {
    $VoucherID = $voucher['VoucherID'];
    $VoucherIDList .= $VoucherIDList ."," . $voucher['VoucherNbr'];
    //create purchase record            
    $purchData = array("CcAuthCode"=>$ccAuthCode,"VoucherID"=>$VoucherID,"PurchAmt"=>$realFinalTotal, "ShipHandFee"=>number_format($shippingCharge,2),  "PurchDT"=>$rightNow,  "AcctID"=>$accountIDs['UserAcctID'], "ShipAddrID"=>$accountIDs['MailingAcctID']);
    $purchID = $model->createPurchaseRecord($purchData);    

    //update voucher
    $model->updateVoucherInfo($VoucherID,$accountIDs['BillingAcctID'], $denom, $purchID,$message);
}

The actual queries are inside the createPurchaseRecord and updateVoucherInfo functions in the model:

function createPurchaseRecord($data){    
    $db =& JFactory::getDBO();
    $insFields = "";
    $valFields = "";

    foreach ($data as $f => $v){
        $insFields .= "," . $f;
        $valFields .= "," . $db->quote($v);
    }

    $insFields = substr($insFields,1);
    $valFields = substr($valFields,1);

    $query = "insert into arrc_PurchaseActivity ({$insFields}) values ({$valFields})";
    $db->setQuery($query);
    if (!$db->query()) error_log($db->stderr());

    return $db->insertid();
}

function updateVoucherInfo($voucherID,$billingAcctId, $balanceInit, $purchID, $certMessage) {
    //set ActivatedDT, BalanceInit
    $rightNow = date("YmdHis");
    $db =& JFactory::getDBO();
    $query = "UPDATE arrc_Voucher 
        set ActivatedDT=".$db->quote($rightNow).", BalanceInit=".$db->quote($balanceInit) . ", BalanceCurrent=".$db->quote($balanceInit).
    ", AcctID=".$db->quote($billingAcctId).", PurchActvtyID=".$db->quote($purchID) . ", certMessage=".$db->quote($certMessage)
    . " WHERE VoucherID=".$db->quote($voucherID);

    $db->setQuery($query);
    if (!$db->query()) error_log($db->stderr()); 
    $certificateNumber = $voucherID;
    return $certificateNumber;

}

Can anyone help me out? There has to be a way to make this more efficient; right now it's throwing a memory error when I try to do any more than 30 or so at a time; given the requirement for 1,000+, this is a big deal. This is the error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 71303153 bytes) in /var/www/html/mysite.com/components/com_arrcard/controllers/checkout.php on line 110

Line 110 is this line from the loop above:

   $VoucherIDList .= $VoucherIDList ."," . $voucher['VoucherNbr'];

Answer 1

Hello,

$VoucherIDList .= $VoucherIDList ."," . $voucher['VoucherNbr'];

You are doing this wrong. You concatenate the list to itself resulting in the variable growing exponentially.

Correct way:

$VoucherIDList .= "," . $voucher['VoucherNbr'];

or

$VoucherIDList = $VoucherIDList ."," . $voucher['VoucherNbr'];

Regards, Alin

Answer 2

$VoucherIDList .= $VoucherIDList ."," . $voucher['VoucherNbr'];

With the .= operator you are doing a concatenation of $VoucherIDList to itself.

With the statement above, you then also add $VoucherIDList to the list again.

Like Alin said above, you are adding the variable to itself exponentially every time the loop runs.

I would guess this is why you are getting the error problems.

Answer 3

To make your code little cleaner and eliminate unnecessary calls.

Instead of

foreach ($data as $f => $v){
    $insFields .= "," . $f;
    $valFields .= "," . $db->quote($v);
}

Use

$valFields = implode(',', $data);
$insFields = implode(',', array_keys($data));

Increase memory usage in php.ini

If you are using PHP 5, loose &.

Instead of looping through array of arrays. Load $vouchers as array of objects, objects are passed by reference instead of by value.

foreach($vouchers as $voucher) {