Send mail using database data

[LinusIT]

I've had help with this before and had excellent results. I decided to add more information to the email but can't get it reading the data. It all works fine apart from the grabbing the delivery address. I have tried what I know but to no avail.

Here's the code:

<?php
$orderid = Trim(stripslashes($_POST['ordernumber']));
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'psw';
$dbname = 'store';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$conn) {
die('Failed to connect to server: ' . mysql_error());
}
mysql_select_db($dbname);

$sql="SELECT * FROM orders_products WHERE orders_id = '".$orderid."'";
$result = mysql_query($sql);

$firstname = Trim(stripslashes($_POST['firstname']));
$lastname = Trim(stripslashes($_POST['lastname']));
$email = Trim(stripslashes($_POST['email']));
$mtcn = Trim(stripslashes($_POST['mtcn']));
$amount = Trim(stripslashes($_POST['amount']));
$country = Trim(stripslashes($_POST['country']));
$ordernumber = Trim(stripslashes($_POST['ordernumber']));
$currency = Trim(stripslashes($_POST['currency']));
$emailto = "[e-mail]";
$emailcc = "[e-mail]";
$subject = "Information - Order No:";
$headers = "From: $email" . "\r\n" . "CC: $emailcc";

// prepare email body text
$body .= "--------------------------------------\n";
$body .= "Details\n";
$body .= "--------------------------------------\n";
$body .= "First name: ";
$body .= $firstname;
$body .= "\n";
$body .= "Last name: ";
$body .= $lastname;
$body .= "\n";
$body .= "Email Address: ";
$body .= $email;
$body .= "\n\n";
$body .= "--------------------------------------\n";
$body .= "Products\n";
$body .= "--------------------------------------\n";

while($row=mysql_fetch_array($result)) {
$body .= $row['products_quantity']." x ".$row['products_name']." = ".sprintf("%.2f", ($row['products_quantity'] * $row['final_price'])) . "\n";
}
$body .= "\n";
$body .= "--------------------------------------\n";
$body .= "Delivery Address\n";
$body .= "--------------------------------------\n";
$body .= "Delivery Name:: ";
$body .= $row2['delivery_name'];
$body .= "\n";
$body .= "Address 1:: ";
$body .= $row2['delivery_street_address'];
$body .= "\n";
$body .= "Address 2: ";
$body .= $row2['delivery_suburb'];
$body .= "\n";
$body .= "Address 3: ";
$body .= $row2['delivery_city'];
$body .= "\n";
$body .= "Postcode: ";
$body .= $row2['delivery_postcode'];
$body .= "\n";
$body .= "Country: ";
$body .= $row2['delivery_country'];
$body .= "\n";

// send email
$send = mail($emailto, $subject . ' ' . $ordernumber, $body, $headers);

mysql_close($conn);

// redirect to success page
if ($send){
print "<meta http-equiv=\"refresh\" content=\"0;URL=sent.php\">";
}
else{
print "We encountered an error sending your mail";
}
?>

I'm sure you'll notice that row2 isn't defined anywhere, I did have $row2 = mysql_fetch_row($result)) but this didn't work.

Any help greatly appreciated.

[timster]

I'm sure you'll notice that row2 isn't defined anywhere

That's right, that definitely won't work. Not

If the delivery address is in the orders_products table, then you should use the $row variable to get the data, and you will need to put that inside the while block that prints the other data. (I'd suggest replacing "select *" with the names of all the fields you are selecting. Makes supporting the code, like we're doing now, easier.)

If the address is in another table, you will need another sql statement to grab that data.

Please give us a rundown on the MySQL tables involved.

[rocknbil]

Right, why are those at $row2 when you are storing the rows in $row? But first, some basics . . . what does this do?

$result = mysql_query($sql) or die("Cannot query data");

You can extend that to this, but remove the mysql_error before deployment:

$result = mysql_query($sql) or die("Cannot query data" . mysql_error());

The second thing, is orders_id numeric? This is more important than you think, potentially you could be doing

SELECT * FROM orders_products WHERE orders_id = '';

without knowing it, which will still query, just won't give you results (see comments below on easy ways to build error trapping into your programming so you can avoid that.) If it's numeric, leave off the quotes, if your input is empty it will error every time, alerting you to the problem. :-)

Lastly, as mentioned, where are the delivery data rows stored? In a single row along with the ordered products? This would be highly inefficient as it would have redundant data in each row for all the delivery data. If it is, only the last row would be stored in these fields

$body .= $row['delivery_name']; // etc.

because you are displaying them outside the while loop. If this is the case it should still work, but a better scenario is to store orderdata for base orders, then order items as a second table as there may be multiple items for each order.

Pseudo-logic,

$orderitems=$body=null; 
$query = "select the base order data and get order_id"; 
$result = mysql_query($result) or die("cannot get base order data"); 
// 
if ($row=mysql_fetch_array($result)) { // use IF, there is only ONE 
 // 
 $query = "select the ordered items where order_id=$your_order_id"; // OK to recycle, we're done with it 
 $result2 = mysql_query($query) or die("cannot get the order items"); 
 while ($row2=mysql_fetch_array($result2)) { 
 $orderitems .= "Concatentate order item rows here using *$row2* data"; 
 } 
 $body .= "Add your shipping and base order data here"; 
 if ($orderitems) { $body .= $orderitems; } 
 // Error trapping is your friend! 
 else { $body .= "Hmm, no order items"; } 
} 
else { $body .= "Hmm, no order was found"; } 

You can see my message, adequate error trapping may seem frivolous but in the long run it will save you a **lot** of time. :-)

The outer loop we store data in the array $row. We don't want to overwrite $row, so on the *inner* loop we store data for the products in $row2.

We can overwrite $query in the inner loop - it's already been executed - but we want to preserve $result and $row so we use $result2 and $row2 on the inner loop. Grok?

[LinusIT]

Thanks for the replies.

The delivery address is stored in the "orders" table and the products are store in orders_products.

The delivery address is on a single line and the products are on multiple lines.

Order ID is numeric.

I will try and implement your suggestion rocknbil, thanks.

[LinusIT]

Ok, I've had a play and have got it working, or die("Cannot query data" . mysql_error()); helped no end.

Here's the working code, I have rearranged some lines to tidy it up.

<?php
$orderid = Trim(stripslashes($_POST['ordernumber']));
$dbhost = 'localhost';
$dbuser = 'dbadmin';
$dbpass = 'psw';
$dbname = 'store';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$conn) {
die('Failed to connect to server: ' . mysql_error());
}
mysql_select_db($dbname);

$sql="SELECT * FROM orders_products WHERE orders_id = '".$orderid."'";
$result = mysql_query($sql)or die("Cannot query orders_products data" . mysql_error());

$sql2="SELECT * FROM orders WHERE orders_id = '".$orderid."'";
$result2 = mysql_query($sql2) or die("Cannot query orders data" . mysql_error());

$firstname = Trim(stripslashes($_POST['firstname']));
$lastname = Trim(stripslashes($_POST['lastname']));
$email = Trim(stripslashes($_POST['email']));
$ordernumber = Trim(stripslashes($_POST['ordernumber']));
$emailto = "[e-mail]";
$emailcc = "[e-mail]";
$subject = "Information - Order No:";
$headers = "From: $email" . "\r\n" . "CC: $emailcc";

// prepare email body text
$body .= "--------------------------------------\n";
$body .= "Details\n";
$body .= "--------------------------------------\n";
$body .= "First name: $firstname" . "\n";
$body .= "Last name: $lastname" . "\n";
$body .= "Email: $email" . "\n";
$body .= "\n--------------------------------------\n";
$body .= "Products";
$body .= "\n--------------------------------------\n";

while($row=mysql_fetch_array($result)) {
$body .= $row['products_quantity']." x ".$row['products_name']." = ".sprintf("%.2f", ($row['products_quantity'] * $row['final_price'])) . "\n";
}
while($row2=mysql_fetch_array($result2)) {
$body .= "\n--------------------------------------\n";
$body .= "Delivery Address";
$body .= "\n--------------------------------------\n";
$body .= ucwords($row2['delivery_name']) . "\n";
$body .= ucwords($row2['delivery_street_address']) ."\n";
if($row2['delivery_suburb']) {
$body .= ucwords($row2['delivery_suburb']) . "\n";
}
if($row2['delivery_city']) {
$body .= $row2['delivery_city'] . "\n";
}
$body .= strtoupper($row2['delivery_postcode']) . "\n";
$body .= $row2['delivery_country'] . "\n";
}

// send email
$send = mail($emailto, $subject . ' ' . $ordernumber, $body, $headers);

mysql_close($conn);

// redirect to success page
if ($send){
print "<meta http-equiv=\"refresh\" content=\"0;URL=sent.php\">";
}
else{
print "We encountered an error sending your mail.";
}
?>

I'm wondering if there is a more elegant way of doing this, prehaps only selecting required fields?

[timster]

prehaps only selecting required fields?

It's generally better to select the fields by name with your SQL query. If there got to be some problem with your data table, say a field got renamed somehow, your mysql_error statement would throw another helpful message.

I would suggest selecting ALL the fields from the given tables by name, unless performance is a paramount concern. That makes it easy to access that data in your app later with minimal changes. Then put your SQL statments in an included file so you can access that data from any page you need it without writing more code.