[RESOLVED] query results into a mail ?

30equals

so i have made a shopping cart and everything works except for the confirmation mail.
i already searched the forum and found one similar problem, but that solution doesn't work with me...strangely enough.

all i want is to loop results from a uery so the customer gets a confirmation mail with all the items they ordered. when i get the mail, i get all the other info , but not the item name, title or quantity.

is it because i misplaced my query or something ?

code:

<?php
	session_id();
	session_start();

$shippingmethod = $_POST['shippingmethod'];
$paymentmethod = $_POST['paymentmethod'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$add1 = $_POST['add1'];
$add2 = $_POST['add2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$country = $_POST['country'];
$email = $_POST['email'];
$shipfirst = $_POST['shipfirst'];
$shiplast = $_POST['shiplast'];
$shipadd1 = $_POST['shipadd1'];
$shipadd2 = $_POST['shipadd2'];
$shipcity = $_POST['shipcity'];
$shipstate = $_POST['shipstate'];
$shipzip = $_POST['shipzip'];
$shipcountry = $_POST['shipcountry'];
$shipemail = $_POST['shipemail'];
$total = $_POST['total'];
$sessid = session_id();
$today = date("Y-m-d");



$query = "SELECT * FROM br_customers WHERE 
		(firstname = '$firstname' AND lastname = '$lastname' AND add1 = '$add1' AND add2 = '$add2' AND city = '$city' AND country ='$country')";
		$result = mysql_query($query) or die(mysql_error());
		$rows = mysql_num_rows($result);


if($rows <1) {


$query2 = "INSERT INTO br_customers (firstname, lastname, add1, add2, city, state, zip, country, email)
VALUES (
'$firstname',
'$lastname',
'$add1',
'$add2',
'$city',
'$state',
'$zip',
'$country',
'$email')";
$insert = mysql_query($query2) or die(mysql_error());
$custid = mysql_insert_id();

}


 if($custid) $custnum = $custid;



 $query3 = "INSERT INTO br_ordermain (orderdate, custnum, subtotal, shipping, shipfirst, shiplast, shipadd1, 
 			shipadd2, shipcity, shipstate, shipzip, shipcountry, shipemail, shippingmethod, paymentmethod)
			VALUES (
			'$today',
			'$custnum', 
			'$total',
			'$shipping',
			'$shipfirst',
			'$shiplast',
			'$shipadd1',
			'$shipadd2',
			'$shipcity',
			'$shipstate',
			'$shipzip',
			'$shipcountry',
			'$shipemail',
			'$shippingmethod',
			'$paymentmethod')";
	$insert2 = mysql_query($query3) or die(mysql_error());
	$orderid = mysql_insert_id();


	$query = "SELECT * FROM br_carttemp WHERE sess='$sessid'";
	$result = mysql_query($query) or (mysql_error());


	while ($row = mysql_fetch_array($result)){

		extract ($row);
		$query4 = "INSERT INTO br_orderdet (ordernum, qty, prodnum)
		VALUES (
		'$orderid',
		'$quan',
		'$prodnum')";
		$insert3 = mysql_query($query4) or (mysql_error());


	}

	$query5 = "SELECT * FROM br_products WHERE prodnum='$prodnum'";
	$result5 = mysql_query($query5) or (mysql_error());
	while ($row = mysql_fetch_array($result5)) {            
    $name = $row['name'];
	$title = $row['title'];
	}  
	//delete from temp table
	$query = "DELETE FROM br_carttemp WHERE sess='$sessid'";
	$delete = mysql_query($query);




	//email confirmations to us and the customer
	/*recipients */
	 $to  = "<".$email.">";
	 $bcc  = "<order@mydomain.com>";//example mail address for the forum


	//subject
	$subject = "order confirmation";
	$subjectbcc = "new order received ";

	/*¨message*/
		/*top of message*/
		$message = "Thank you for shopping at . Your order was received on &nbsp;".$today."<br>";
		$message .=	"It will be processed as soon as possible<br>";
		$message .= "We'll check if all items are still in stock and let you know the total with postage<br>";		
		$message .= "-------------------------------------------------------------------------------------<br>";
		$message .= "-------------------------------------------------------------------------------------<br>";
		$message .= "order number:&nbsp;".$orderid."<br>";
		$message .= "items:<br>";
		while ($row = mysql_fetch_array($result5)) {            
    	$message .=    $name.$title.$quan."<br>";
        }  
		$message .= "total of your purchases without postage:&nbsp".$total;
		$message .= "<br><br>";
		$message .= "Your info:<br>";
		$message .= $firstname."&nbsp;".$lastname."<br>";
		$message .= $add1."<br>";
		if(!empty($add2)){
		$message .= $add2;
		}
		$message .= $zip."&nbsp;".$city."<br>";
		if(!empty($state)){
		$message .= $state."<br>";
		}
		$message .= $country."<br><br>";
		$message .= "you've chosen to ship&nbsp;".$shippingmethod."&nbsp;and to pay with&nbsp;".$paymentmethod;



	/*headers*/
	$headers ="MIME-VERSION: 1.0\r\n";
	$headers .="Content-type: text/html; charset=utf-8\r\n";
	$headers .="From: <order@mydomain.com>\r\n";

	/*mail it*/
	mail ($to, $subject, $message, $headers);
	mail ($bcc, $subjectbcc, $message, $headers);


	//show them their order & give them an order number
	?>


<b>Thank you for your order!</b><br>
<br>orderdate: <?php echo $today; ?><br>
your ordernr. is <?php echo $orderid; ?>
<br>
<br>
you will get a order confirmation email <br>
(if you do not receive this, please contact us)

thanx in advance!

Weedpacket

This loop:

while ($row = mysql_fetch_array($result5)) {            

        $name = $row['name'];
        $title = $row['title'];
        }

is reading through $result5 to the end - not doing anything with it - and leaving nothing for you to loop through $result5 again later when you're building the email (which, incidentally, might as well not be in HTML if you're not doing any HTML....)

30equals

Weedpacket wrote:
This loop:
while ($row = mysql_fetch_array($result5)) {            

        $name = $row['name'];
        $title = $row['title'];
        }   
is reading through $result5 to the end - not doing anything with it - and leaving nothing for you to loop through $result5 again later when you're building the email (which, incidentally, might as well not be in HTML if you're not doing any HTML....)

actually, when composing my message
i'm using the results..

/*¨message*/
			/*top of message*/
			$message = "Thank you for shopping. Your order was received on &nbsp;".$today."<br>";
			$message .=	"It will be processed as soon as possible<br>";
			$message .= "We'll check if all items are still in stock and let you know the total with postage<br>";		
			$message .= "-------------------------------------------------------------------------------------<br>";
			$message .= "-------------------------------------------------------------------------------------<br>";
			$message .= "order number:&nbsp;".$orderid."<br>";
			$message .= "items:<br>";
			while ($row = mysql_fetch_array($result5)) {            

        	$message .=    $name.$title.$quan."<br>";
            }  

			$message .= "total of your purchases without postage:&nbsp".$total;
			$message .= "<br><br>";
			$message .= "Your info:<br>";
			$message .= $firstname."&nbsp;".$lastname."<br>";
			$message .= $add1."<br>";
			if(!empty($add2)){
			$message .= $add2;
			}
			$message .= $zip."&nbsp;".$city."<br>";
			if(!empty($state)){
			$message .= $state."<br>";
			}
			$message .= $country."<br><br>";
			$message .= "you've chosen to ship&nbsp;".$shippingmethod."&nbsp;and to pay with&nbsp;".$paymentmethod;

Weedpacket

30equals wrote:
actually, when composing my message
i'm using the results..

No, you're not. (See previous post).

30equals

 while ($row = mysql_fetch_array($result5)) {            

            $message .=    $name.$title.$quan."<br>";
            }

so this is doing nothing ?

i think you lost me here...

so what can i change to actually get results then ?

thnx

Weedpacket

30equals wrote:
so this is doing nothing ?

Exactly.

The first time you loop through $result5 (the code I exceprted) you're putting values from each row into two variables. You never use the values from any of those rows - you just overwrite them with the values from the next row when you process it.

That continues until the end of the result set is reached. After that, mysql_fetch_array() returns false and the loop stops.

At this point, those two variables will contain the values from the last row in the result set (and only that row). But you never see them, because the only place you try and use them is....

Later on in the program is the bit you just excerpted. Where you try to use a loop to extract rows from the $result5 result set. But guess what? You've already reached the end of the $result5 result set, so mysql_fetch_array() returns false and the loop ends before it even properly begins.

Most obvious solution: ditch the first loop, because it's doing nothing except burning up your result set.

30equals

thanx for the good explanation. things like that are more appreciated than just providing a snippet of code 😉

i got it working now. i removed the first loop. and adjusted the query so i get all the products.
with using the query in the example i was just getting one item name. (obviously, because it had to match the prodnum... stupid me)

working query

$query5 ="SELECT * FROM br_products left join br_carttemp on br_carttemp.prodnum = br_products.prodnum WHERE sess='$sessid'  ";

Weedpacket

No worries🙂