How to include SQL output into an outgoing mail??

[huisjames]

I have a typical user registration/purchase confirmation scenario.  I need to send out a confirmation email but I'm not sure how to include mysql_fetch_array into the body of the mail. 

*WAMPServer 2.0

*PHP 5.3.0

*MySQL 5.1.36

*Using PHPMailer script I found and sending via Gmail SMTP server (sent mail with text works)

*The text contains Simplified Chinese

 

What's wrong with my code?

class database{

function query()
{
	$Paytype=  $_POST['Cardtype'];
	$CarID = $_POST['CarID'];
	$Calendar = $_POST['Calendar'];

	//1. Connect/Login to MySQL
	$con = mysql_connect("localhost", "fakeuser", "fakeuser");
	//echo !$con ? 'Could not connect: '.mysql_error() : 'Connection success!<p />';

	//2. Tells which db to connect to
	mysql_select_db("carshare", $con);

	/*On the MySQL side, set two session control variables: 
	character_set_client=utf8 and character_set_connection=utf8 
	when saving input text to the database table. 
	This is to tell MySQL server that my SQL statement is encoded as UTF-8 and keep it as UTF-8 when executing the statement.
	*/
	mysql_query("SET character_set_client=utf8", $con);
	mysql_query("SET character_set_connection=utf8", $con);
	//When retrieving text data from MySQL, I need to set one session control variable: 
	//character_set_results=utf8. This is to tell MySQL server that result set must be sent back in UTF-8 encoding.
	mysql_query("SET character_set_results=utf8", $con);

	//3. Queries the db
	$result = mysql_query("
	SELECT cars.CarID, cars.Img, cars.Year, cars.Make, cars.Model, cars.City, cars.Shift, cars.Distance, cars.Price
	FROM cars
	WHERE cars.CarID=".$CarID."
	") or die(mysql_error());

	$date = date('H:i, jS F Y');

	$resultcount = mysql_num_rows($result); 

	//4. Print results in an array
	while($row = mysql_fetch_array($result))
	{
		echo	"<b>Date: ".$date."</b><p />";
		echo "City: ".$row['City']."<p />";
		echo	"Calendar: ".$Calendar."<p />";
		echo	'<img src="img/large/'.$row['Img'].'" border="1" /><p />';
		echo	"Year: ".$row['Year']."<p />";
		echo	"Make: ".$row['Make']."<p />";
		echo	"Model: ".$row['Model']."<p />";
		echo	"Shift: ".$row['Shift']."<p />";
		echo	"Distance: ".$row['Distance']."<p />";
		echo	"Price: ¥".$row['Price']."<p />";
		echo "Payment method: ".$Paytype."<p />";
	}
	//5. Close connection with db
	$close = mysql_close($con);
}
}
$object = new database();
$object->query();

 

[trq]

What's wrong with my code?

 

Why don't you tell us what your expecting to happen and what actually is happening?

[huisjames]

Good feedback thrope.

 

Expected result:  Prints below in the mail body:

                        echo	"<b>Date: ".$date."</b><p />";
		echo "City: ".$row['City']."<p />";
		echo	"Calendar: ".$Calendar."<p />";
		echo	'<img src="img/large/'.$row['Img'].'" border="1" /><p />';
		echo	"Year: ".$row['Year']."<p />";
		echo	"Make: ".$row['Make']."<p />";
		echo	"Model: ".$row['Model']."<p />";
		echo	"Shift: ".$row['Shift']."<p />";
		echo	"Distance: ".$row['Distance']."<p />";
		echo	"Price: ¥".$row['Price']."<p />";
		echo "Payment method: ".$Paytype."<p />";

Actual result 1: I tried

$mailbody = "Here is your email confirmation: ".$object->query().""

but the web page now prints two copies and nothing shows in the email.

 

Actual result 2: If I do:

$mail = $object->query();
$mailbody = $mail;

It returns me with Catchable fatal error: Object of class PHPMailer could not be converted to string

 

[trq]

Firstly, assuming your only expecting one result, you don't need a while loop.

 

Then, instead of echoing the result within the method itself, it should simply return the result. So, this....

 

while($row = mysql_fetch_array($result)
{
echo "<b>Date: ".$date."</b><p />";
echo "City: ".$row['City']."<p />";
echo "Calendar: ".$Calendar."<p />";
echo '<img src="img/large/'.$row['Img'].'" border="1" /><p />';
echo "Year: ".$row['Year']."<p />";
echo "Make: ".$row['Make']."<p />";
echo "Model: ".$row['Model']."<p />";
echo "Shift: ".$row['Shift']."<p />";
echo "Distance: ".$row['Distance']."<p />";
echo "Price: ¥".$row['Price']."<p />";
echo "Payment method: ".$Paytype."<p />";
}

 

Should be....

 

$row = mysql_fetch_array($result);
$return = "
  <b>Date: $date</b><br />
  City: $row{['City']}<br />
  Calendar: $Calendar<br />
  <img src='img/large/{$row['Img']}' border='1' /><br />
  Year: {$row['Year']}<br />
  Make: {$row['Make']}<br />
  Model: {$row['Model']}<br />
  Shift: {$row['Shift']}<br />
  Distance: {$row['Distance']}<br />
  Price: ¥{$row['Price']}<br />
  Payment method: $Paytype<br />
";
return $return;

 

Now you can either echo the results of $object->query() or use them in your email.

 

Not that I also changed all the <p /> tags to <br />. There is no such tag as <p /> in either html or xhtml.

 

Also, on a side note. I have no idea why this is within a class. Especially one named database. Your method name is also very undescriptive.

[trq]

ps: Your image won't work in an email unless you prepend your domain to it.

[huisjames]

Hi thorpe,

 

Thanks for the tips.  I tried putting your code into a function and simplified it to see if I can pass even one variable:

 

function query($result) {

			$row = mysql_fetch_array($result);							
			$return = "Year: ".$row['Year'];
                                return $return;  
	}

echo query($result);

 

Expected: Show the year or return an error

Actual: No value shown and no error. 

 

Questions:

1. Could it be that mysql_fetch_array needs a while loop to display results?

2. I also tried this and no luck:

function query() {
			global $result;	
			$row = mysql_fetch_array($result);							
			$return = "Year: ".$row['Year'];
                                return $return;  
	}

echo query();

3. I found a work-around which is to declare a variable for each row of SQL output within the while loop.  I.e. $City = $row['City']; $Year = $row['Year']; and just call these variables individually for the mail body.  But it doesn't seem to be the most efficient way to code.  I'm just looking for the smartest way to do this.  Perhaps this method is fine?

 

Thanks so much

[huisjames]

So I tried some more and this now displays on the page. 

 

function query() {
			global $result; 	
			$row = mysql_fetch_array($result);					
			$return = $row['City'].$row['Year'].$row['Make'];
			return $return;
	}

echo query();

 

But when I receive the email it is empty.  Here is the code of PHPMailer I'm using (see $mail->Body line):

require(".\PHPMailer\class.phpmailer.php");
$mail = new PHPMailer(); 
$mail->IsSMTP(); // send via SMTP
//IsSMTP(); // send via SMTP
$mail->SMTPSecure = "tls"; // sets the prefix to the server
$mail->Host = "smtp.gmail.com"; // sets GMAIL as the SMTP server
$mail->Port = 587; // set the SMTP port for the GMAIL server)
$mail->SMTPAuth = true; // turn on SMTP authentication
$mail->Username = "[email protected]"; // SMTP username
$mail->Password = "mypassword"; // SMTP password
$webmaster_email = "[email protected]"; //Reply to this email ID
$email="[email protected]"; // Recipients email ID
$name="Bob"; // Recipient's name
$mail->From = $webmaster_email;
$mail->FromName = "Me";
$mail->AddAddress($email,$name);
$mail->AddReplyTo($webmaster_email,"Webmaster");
$mail->WordWrap = 50; // set word wrap
//$mail->AddAttachment("/var/tmp/file.tar.gz"); // attachment
//$mail->AddAttachment("/tmp/image.jpg", "new.jpg"); // attachment
$mail->IsHTML(true); // send as HTML
$mail->Subject = "This is the subject";
$mail->Body = "Thank you: ".query()."";
$mail->AltBody = "This is the body when user views in plain text format"; //Text Body
if(!$mail->Send(	))
{
echo "Mailer Error: " . $mail->ErrorInfo;
}
else
{
echo "Message has been sent";
}

[trq]

I don't see where you actually create $result (which by the way should be passed to your function as an argument, using globals defeats the entire purpose of functions).