CODEWITHSUNDEEP
phpemailforeach
Mark
Mark

Reputation: 69

Problems with foreach loop in email

I'm having difficulty getting a foreach loop to function correctly when generating an email. I've searched for answers and I've found directions to insert a foreach loop into an email, but nothing about the loop not working.

I'm trying to email order receipts to customers after they place an order, and everything in the email gets delivered except for the list of items.

I'll try to include as much of my logic as possible: When the order is placed, the array of shopping cart items is serialized and then stored in the database. After the payment processor returns that the payment was successful, I display a receipt page on the website which the user can print. The order items are retrieved from the database as follows:

$stmt = $dbh->prepare("SELECT * FROM *table* WHERE orderID = :q0")
$stmt->bindValue(":q0",$orderNum)
$stmt->execute();
foreach($stmt as $row) {
    $items = unserialize($row['order_contents']);
}

at this point, if I echo $items, I see an array of the items from the order. So far, so good.

Now because I'm using PDO, a foreach loop within the query isn't something I've figured out how to do. Instead, I've initialized a variable to hold the items from the foreach loop: 

foreach($items as $prod_id => $value) {
    $q .= $prod_id . ',';
}
$q = substr($q, 0, -1);

This stores the array in a variable which I can now use in the PDO statement for the next query: 

$stmt2 = $dbh->prepare("SELECT * FROM *productTable* WHERE prod_ID IN ($q)");
$stmt2->execute();
foreach($stmt2 as $row) {
$itemName = htmlentities(strip_tags($row['prod_name']));
$id = htmlentities(strip_tags($row['prod_ID']));
$qty = htmlentities(strip_tags($items[$prod_id]['quantity']));
$price = $items[$prod_id]['price'];
echo "
    <tr>
        <td>".$qty."</td>
        <td>".$itemName."</a></td>
        <td>".$price."</td>                        
    </tr>";
} // THIS CODE WORKS. I can see each item from the order as a new row in the table

Once again, so far so good. I can run a foreach loop to echo the items to the page, and they all display with the proper item name as well as other information from the table which I left out from this example.

Skip ahead to the end of the file, and I've got the following script which does not work and I can't figure out why. The email sends successfully, but none of the products from the foreach loop show up in the email. 

$to = '[email protected]'
$subject = 'Your order receipt for order '.$orderNum
$headers = 'From: [email protected]'."\r\n".
           'Reply-To: [email protected]'."\r\n".
           'Content-Type: text/html; charset=UTF-8'."\r\n".
           'X-Mailer: PHP/'.phpversion();
$message =  '<html><body>';
$message .= '<h2>Thanks for your purchase!</h2><br>';
$message .= '<p>Here are the details of your order number '.$orderRef.':<br><table>';
$message .= '
<tr style="width: 100%;">
    <th scope="col" style="width: 20%;">Quantity:</th>
    <th scope="col" style="width: 60%;">Name:</th>
    <th scope="col" style="width: 20%;">Price:</th>
</tr>
';
foreach ($stmt2 as $row) {
    $message .= "
    <tr>
        <td>".$qty."</td>
        <td>".$itemName."</td>
        <td>".$price."</td>                        
    </tr>"; //THIS CODE DOESN'T WORK, Even though it's using the same variables as the loop from above. 
}
$message .= '</table><br>';
$message .= 'If you have any questions about your order, please call us at <b>XXX-XXX-XXXX</b>';
$message .= '</body></html>';
mail($to, $subject, $message, $headers);

The email sends, and I see everything before and after the foreach loop, but I don't see the actual items being generated. After the email code, I can echo the variables to the page and they all display properly, so the variables are still declared.

Additional information which may or may not be relevant: I'm trying to execute this code on a freshly imaged VPS with Ubuntu 14.04. I just installed an SSL certificate on Monday I installed UFW on my VPS recently. SMTP, IMAP, and UTP ports are not blocked. PHP version 5.5.9-1ubuntu4.21

Here's a screenshot of the email output:screen capture of email

Edit: The variables were already defined after the first foreach($stmt2 as $row) statement. I've added the additional code to reflect this.

Upvotes: 0

Views: 1047

Answers (3)

Mark
Mark

Reputation: 69

The solution I ended up with was adding the SQL query again, inside the email code: 

$message .= '<p>Here are the details of your order number '.$orderRef.':<br>
<table>';
$message .= '
<tr style="width: 100%;">
    <th scope="col" style="width: 20%;">Quantity:</th>
    <th scope="col" style="width: 60%;">Name:</th>
    <th scope="col" style="width: 20%;">Price:</th>
</tr>
';
$stmt2 = $dbh->prepare("
SELECT * FROM products WHERE prod_ID IN ($q) ORDER BY prod_id ASC
");
$stmt2->execute();
while ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
    $itemName = htmlentities(strip_tags($row['prod_name']));
    $id = htmlentities(strip_tags($row['prod_ID']));
    $qty = htmlentities(strip_tags($items[$prod_id]['quantity']));
    $price = $items[$prod_id]['price'];

    $message .= "
    <tr>
        <td>".$qty."</td>
        <td>".$itemName."</td>
        <td>".$price."</td>                        
    </tr>";
    }
$message .= '</table><br>';

and then the remainder of the code. It seems to work now, my test email showed both items from the test order.

Thank you all for your suggestions.

Upvotes: 0

Hossein
Hossein

Reputation: 3107

I think the problem is in the foreach loop.

foreach ($stmt2 as $row) {
    $message .= "
    <tr>
        <td>".$qty."</td> // $qty is undefined! You should use $row['qty'] or $row->qty
        <td>".$itemName."</td> // $itemName is not defined
        <td>".$price."</td> // $price is not defined
    </tr>";
}

I hope it helps.

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

$stmt2 isn't an array, it's a generator. Among other things, this allows to only load rows into memory as needed, rather than needing to read the whole thing into memory at once.

The catch is, you can't foreach it twice. You will need to convert it to an actual array. I haven't tested but you may be able to do this with $itemdata = iterator_to_array($stmt2) - failing that just $itemdata[] = $row inside your first foreach will allow you to refer to this $itemdata array later.

Upvotes: 1

Related Questions