MySQL Array Query

[LinusIT]

Hi

I've got a form that people fill in which sends me an email, this works fine. They must fill in their Order Number on the form to submit. What I'm trying to do is pull all the rows from a database which match the Order Number entered in the form.

It's working sort of but not 100%. Here's the code I've got:

while($row=mysql_fetch_array($result)) {
$Body .= $row['final_price'] =$row['products_name'] =$row['products_quantity'];
}

In the email that I receive it looks like this:

Products: 24

Where as it should look like this:

Products: ItemName 2 ItemName 4

I have no idea how to split it onto seperate lines but that's the least of my worries at the moment. It's collecting the quantity of each line but nothing else. I've used a select * query and checked for spelling mistakes etc.

Hope someone can help

[Matthew1980]

Hi there LinusIT,

while($row=mysql_fetch_array($result)) {
$Body .= $row['final_price'].$row['products_name'].$row['products_quantity'];
}

Try that, you want to join them, not overwrite them :)

Cheers,
MRb

[LinusIT]

Thanks very much MRb, that's worked

Now it's working I'm wondering If it can be improved upon. It now looks like this:

35.0000512Mb DDR275.00001GB DDR4

This would be much better:

35.0000 512Mb DDR x 2 75.0000 1GB DDR x 4

On seperate lines would be fantastic especially for large orders.

[Matthew1980]

Hi there LinusIT,

Well that's fine, it's all to do with how you join the pieces of data together:-

$Body .= $row['final_price'].$row['products_name'].$row['products_quantity'];

Produces:-

35.0000512Mb DDR275.00001GB DDR4

But as there are only three reference variables there, and I don't know where they start and stop, I can't show you what to do other than this:-

$Body .= $row['final_price']." ".$row['products_name']." ".$row['products_quantity'];

The ." ". between each variable referenced, gives you a space, and in those spaces you can place html to format better for example, it's just a case of have a play and see what you come up with.

Hope that makes sense.

Cheers,
MRb

[LinusIT]

That's great, I've managed to split it up onto seperate lines and make it far easier to read.

$Body .= $row['products_quantity']." x ".$row['products_name']. "\n";

It now produces:

1 x Cheese Biscuits
3 x Jam Tarts
2 x Sugar Cubes

Fantastic :)

[Matthew1980]

Hi there LinusIT,

Excellent, glad it does what you wanted!

Happy coding.

Cheers,
MRb

[rocknbil]

I've managed to split it up onto seperate lines and make it far easier to read.

But you're still dealing with plain text, and as your app grows, grow with it. HTML emails will not only give you a far more professional email response, it will be far easier to organize, and you can use it for both email and thank you page responses. Take a step back and consider . . .

// First query the general order: user name, email, address, all that stuff.
// Store it in the var $userdata it into TABLE ROW data like
// <tr><td colspan="2" align="right"><strong>Name:</strong></td><td colspan="2"> $firstname $lastname</td></tr>
// We revert to table layout for HTML emails because styles and external elements are poorly supported
//

$total = 0; 
$products = null; 
while($row=mysql_fetch_array($result)) { 
 $price = $row['final_price']; 
 $pname = $row['products_name']; 
 $quan = $row['products_quantity']; 
 $subtotal = sprintf(("%.2f",$row['final_price']); // format "1234.00" 
 $total += $subtotal; 
 $products .= " 
 <tr> 
 <td align=\"center\">$pname</td> 
 <td align=\"center\">$quan</td> 
 <td align=\"center\">$price</td> 
 <td align=\"center\">\$$subtotal</td> 
 </tr>"; 
}  
// Output 
// 
if ($orderdata) { 
 $output = " 
 <table width=\"600\" align=\"center\"> 
 $orderdata 
 "; 
 if ($products) { 
 $total = sprintf(("%.2f",$total); 
 $output .= " 
 <tr> 
 <td align=\"center\" color=\"#fff\" background=\"#000\">ITEM</td> 
<td align=\"center\" color=\"#fff\" background=\"#000\">QUANTITY</td> 
<td align=\"center\" color=\"#fff\" background=\"#000\">PRICE</td> 
<td align=\"center\" color=\"#fff\" background=\"#000\">SUBTOTAL</td> 
</tr> 
$products 
<tr><td colspan=\"3\" align=\"right\" color=\"#fff\" background=\"#000\">TOTAL:</td> 
<td align=\"center\">\$$total</td> 
</tr> 
 "; 
 } 
 else { $output .= "<tr><td colspan=\"4\">OOPS no products!</td></tr>"; } 
 $output .= "</table><p>&nbsp;</p>"; 
} 
// 
else { output = "<p>Oops, no order data.</p>"; } 
// 
echo $output; 

Then . . . take it one step further. Put this inside a function and do

return $output;

instead, and what have you got? You have an order summary for **both** your emails and the thank you confirmation page.

$Body = get_order_data($customer_order_id);
$ThankYouResponse = get_order_data($customer_order_id);

(Not working code, typed on the fly, may have errors . . . )