[RESOLVED] Send a MySQL row via email

[mattwidge]

I'm building a feedback system & I'm trying to get emailing to work.

What I'm trying to do:
1- I press the Mail icon on the comment I want emailed.
2- The comment gets emailed to me.

What the problem is, I can get it to send successfully with everything but the body working. Here's my code:

<?php
include('connect.php');
$query = "SELECT message from visitordata where id='".$id."'";

$to = "eatthelizard@gmail.com";
$subject = "Your feedback, sir.";
$body = "Here's the feedback you requested:<br />".mysql_query($query)."";
mail($to, $subject, $body);
?>

* the $id is set in by mail.php?id=xx. The id is of the comment I chose.
Here's the body of what it comes out as:
"Here's the feedback you requested:<br />Resource id #7"

How can I get this to work?

[NogDog]

You'll have to use one of the mysql_fetch_*() functions to fetch a result row from the query result set.

[Dasher]

One way;

$result = mysql_query($query,$link);

while ($a_row = mysql_fetch_array($result){
$message = $a_row['message'];
}

mail ($to, $subject, $message, $headers); 

It is also likely that the message will need formatting before it can be sent.

[mattwidge]

I changed my code to this:

<?php
include('connect.php');
$query = "SELECT message from visitordata where id='".$id."'";
$to = "eatthelizard@gmail.com";
$subject = "Your feedback, sir.";


$result = mysql_query($query,$db); 

while ($a_row = mysql_fetch_array($result)) { 
$message = $a_row['message']; 
} 

mail ($to, $subject, $message, $headers);
?>

But it didn't work. I got a blank email. Any suggestions?

[NogDog]

First check to see if $result is false after the query is executed, and if so die() or otherwise echo/log the error and the query to debug it. If not false, check to see if mysql_num_rows() is > 0. If not, again log/repot the query string to see if it looks correct, and test it by directly entering it into the MySQL command line interface to verify that your query/logic is correct. Ultimately you'll want some sort of defensive coding approach like:

$result = mysql_query($query);
if($result)
{
   if(mysql_num_rows($result))
   {
      $message = mysql_result($result, 0);
      if(mail($to, $subject, $message, $headers))
      {
         // success message here
      }
      else
      {
         echo "Sorry, there was a problem sending the email.";
      }
   }
   else
   {
      error_log("No row returned: $query");
      echo "Sorry, no matching data was found, email not sent.";
   }
}
else
{
   error_log(mysql_error()."\n".$query);
   echo "Sorry, there was a problem with the database--email not sent.";
} 

[mattwidge]

I'm getting the "Sorry, no matching data was found, email not sent." error. What does this mean?

[NogDog]

It means that your query did not return any matching rows from the database. Check the error log to see what the query looked like, or change error_log() to die() to display it to the browser (assuming error reporting settings allow it). At a guess, $id might be empty -- where does it get set?

[mattwidge]

The id is 32 which is in the database & it is set in the URL ie: mail.php?id=32

[NogDog]

Any difference if you get rid of the quotes around the value in the query?

$query = "SELECT message from visitordata where id=" . (int) $id; 

[mattwidge]

Still get the same error.

I changed error_log() to die() & got:
No row returned: SELECT message from visitordata where id=0

[mattwidge]

Sorry for double post, but I put in

echo $id; 

& got a blank page. I then tried

echo "".$id.""; 

& still got a blank page.

I don't think the id variable is being set.

[NogDog]

It's certainly not explicitly set in the code you've provided. Where is it supposed to come from?

[mattwidge]

From a file called display.php.

A list of MySQL rows is displayed & there is a link specific to each row that goes to the mail.php file. The link works like this:

<a href=\"mail.php?id=".$r['id']."\"><img src=\"email.png\" border=\"0\"></a> 

that is how the id is set. When it gets to mail.php the URL is mail.php?id=*row number*

If you need any thing else, let me know.

[NogDog]

How about using $_GET['id'] instead of $id. ($id would only work if register_globals is enabled, and since that "feature" is deprecated, you should not depend upon it in any case.)

[mattwidge]

SUCCESS!!!!!!

Here it is:

<?php
include('connect.php');
$query = "SELECT message FROM visitordata WHERE id='".$_GET['id']."'"; 
$to = "eatthelizard@gmail.com";
$subject = "Your feedback, sir.";

$result = mysql_query($query);
if($result)
{
   if(mysql_num_rows($result))
   {
      $message = mysql_result($result, 0);
      if(mail($to, $subject, $message, $headers))
      {
         echo "IT WORKED!!!!";
      }
      else
      {
         echo "Sorry, there was a problem sending the email.";
      }
   }
   else
   {
      echo "Sorry, no matching data was found, email not sent.<br>";
      die("No row returned: $query");
   }
}
else
{
   echo "Sorry, there was a problem with the database--email not sent.<br>";
   die(mysql_error()."\n".$query);
}
?>