[Resolved] mysql_query results in an email? How?

[macmatt]

For the book site I'm currently doing, I'd like to email customers with confirmation of their order. I've set up an email that gets sent once an order is confirmed that contains the order number and the total cost of the order (both as variables inserted into the mail() $message variable).

I would now like this email to contain a list of the books that they've ordered. I can get the contents of the order using the following query:

$order = mysql_query("SELECT books.title, books.price, cart.qty 
FROM cart INNER JOIN books on cart.itemId = books.id 
WHERE cart.cookieId = '$cookie_id'
ORDER BY books.title ASC");
$row_order = mysql_fetch_assoc($order);
$totalRows_order = mysql_num_rows($order);

I just can't figure out how to get the results into an email message.

Any help greatly appreciated!

Cheers,

Matt

[paulnaj]

Can't you just add the data you have retrieved from the database to the $message variable at the same place in your code as when you added the order number and total cost?

[macmatt]

To display the results on a page I can use this:

do {
		echo "<p>".$row_order['title']."</p>"; 
	} while  ($row_order = mysql_fetch_assoc($order));

and it works! However, in order to use it in my email $message I have tried turning the above into a variable:

$ordered = do {
		echo "<p>".$row_order['title']."</p>"; 
	} while  ($row_order = mysql_fetch_assoc($order));

and then putting $ordered into my message. All I get is a parse error for the first line of the above code!

I'm guessing there's something very obvious that I'm not doing in order to make the first bit of code above into a variable. Can anyone tell me what it is?

[paulnaj]

Yes, the statement ...

$ordered = do { 
        echo "<p>".$row_order['title']."</p>";  

    } while  ($row_order = mysql_fetch_assoc($order));

... looks dodgy to me. Why not the simpler ...

$ordered = "";
while  ($row_order = mysql_fetch_assoc($order)){
    $ordered .= "<p>".$row_order['title']."</p>";  

} 

[macmatt]

Originally posted by paulnaj
Why not the simpler ...

$ordered = "";
while  ($row_order = mysql_fetch_assoc($order)){
    $ordered .= "<p>".$row_order['title']."</p>";  

} 

[/B]

That works fine, and doesn't give me any errors, but by the time I insert the $ordered variable into the email, it has looped through all the results, so only the last result (row) appears in the email. If I try and insert the loop into the email message like this:

$message = "the text before I want to insert the loop.

Order No: $order_id
Titles Ordered: ".while  ($row_order = mysql_fetch_assoc($order)){
    $ordered .= "<p>".$row_order['title']."</p>";  

} 
."Total Cost: £$booktotal

Rest of email message, blah, blah";

..it just gives an error for the 'Titles ordered:' line. I think I'm doing something badly wrong when I insert the php statement into the email, I just don't know what it is!

Thanks for your help so far, anymore would be greatly appreciated!🙂

[macmatt]

I've sorted it! For the benefit of anyone searching the forums in the future, here's what I used in the end:

$message = "...the text I wanted in the email before Your Order:\n\n";

$message .= do {			

$message .=	$row_order['qty']." x ".$row_order['title']." - £".$row_order['price']." each\n";

	} while ($row_order = mysql_fetch_assoc($order));

$message . = "the rest of the email message";

I then inserted $message into the mail() function as normal and I get a nice list of ordered items within the email.

Thanks for the pointers, paulnaj, it pushed me in the right direction!🙂