By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,729 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,729 IT Pros & Developers. It's quick & easy.

Sending results of an SQL query via email

P: 15
Please help -I have now searched for days for an answer to this!
I am attempting to query a database and send the results in table format via email (so I assume in HTML). I can query the table correctly and display the information but how do I now send it - if at all possible???
So far:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $con = mysql_connect("*****","*****","*****");
  3. if (!$con)
  4.   {
  5.   die('Could not connect: ' . mysql_error());
  6.   }
  7.   mysql_select_db("*****", $con);
  8.   $result = mysql_query("SELECT * FROM contactdetails");
  9.   echo "<table border='1'>
  10.   <tr>
  11.     <th>Name:</th>
  12.     <th>Surname:</th>
  13.     <th>Number:</th>
  14.     <th>Email:</th>
  15.     <th>Moving From:</th>
  16.     <th>Moving To:</th>
  17.     <th>Date:</th>
  18.   </tr>";
  19.  
  20.   while($row = mysql_fetch_array($result)){
  21.     echo "<tr>";
  22.     echo "<td>" . $row['Name'] . "</td>";
  23.     echo "<td>" . $row['Surname'] . "</td>";
  24.     echo "<td>" . $row['Number'] . "</td>";
  25.     echo "<td>" . $row['Email'] . "</td>";
  26.     echo "<td>" . $row['MovingFrom'] . "</td>";
  27.     echo "<td>" . $row['MovingTo'] . "</td>";
  28.     echo "<td>" . $row['Date'] . "</td>";
  29.     echo "</tr>";
  30.   }
  31.   echo "</table>";
  32.  
  33.   mysql_close($con);
  34. ?>
Thanks in anticipation!
Apr 20 '10 #1
Share this Question
Share on Google+
9 Replies


Atli
Expert 5K+
P: 5,058
Hey.

There are several methods to send a mail in PHP.

The simplest (somewhat) is PHP's own mail function, which is usually used when there is an available sendmail or local SMTP server. It's best suited for plain-text mails and simple HTML mails.

You can also use one of the PHP mailer classes, like PHPMailer or SwiftMailer. They can be used to connect to most types of servers, local and remote. - For example, see this PHPMailer Gmail example.
Apr 20 '10 #2

P: 15
Hi Alti,

Thank you for your response.
I've been trying to use PHP's mail function but I obviously have it all wrong.
This is what I've tried:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $con = mysql_connect("*****","*****","*****");
  3. if (!$con)
  4.   {
  5.   die('Could not connect: ' . mysql_error());
  6.   }
  7.   mysql_select_db("*****", $con);
  8.   $result = mysql_query("SELECT * FROM contactdetails");
  9.   $to = "me@example.com";
  10.   $subject = "Results from query";
  11.   $body = "
  12.   echo "<table border='1'>
  13.   <tr>
  14.     <th>Name:</th>
  15.     <th>Surname:</th>
  16.     <th>Number:</th>
  17.     <th>Email:</th>
  18.     <th>Moving From:</th>
  19.     <th>Moving To:</th>
  20.     <th>Date:</th>
  21.   </tr>";
  22.  
  23.   while($row = mysql_fetch_array($result)){
  24.     echo "<tr>";
  25.     echo "<td>" . $row['Name'] . "</td>";
  26.     echo "<td>" . $row['Surname'] . "</td>";
  27.     echo "<td>" . $row['Number'] . "</td>";
  28.     echo "<td>" . $row['Email'] . "</td>";
  29.     echo "<td>" . $row['MovingFrom'] . "</td>";
  30.     echo "<td>" . $row['MovingTo'] . "</td>";
  31.     echo "<td>" . $row['Date'] . "</td>";
  32.     echo "</tr>";
  33.   }
  34.   echo "</table>";
  35.   mysql_close($con);
  36.   ";
  37.   $headers = "From: someone@example.com";
  38.   mail($to,$subject,$body,$headers);
  39.   echo "Mail sent to $to";
  40. ?>
Apr 20 '10 #3

Atli
Expert 5K+
P: 5,058
The problem there is that you need to construct the HTML for the body, not just wrap all the old echo statements in a string.

That is; instead of doing:
Expand|Select|Wrap|Line Numbers
  1. echo "<table>";
  2. echo "<tr><td>...";
Do:
Expand|Select|Wrap|Line Numbers
  1. $body = "<table>";
  2. $body .= "<tr><td>...";
And note the period before the = in the second line. That tells PHP to add the following string to what the variable is storing. If you leave it out, PHP will replace it, which we obviously do not want.

P.S.
Please use [code] tags when posting code examples.

[code] ... Code goes here ... [/code]

Thanks.
Apr 20 '10 #4

P: 15
Sorry for not putting the code tags. Will do from now on.
Once again thanks for your prompt response - I tried adding as you instructed - see below? Apologies for being such a noob

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $con = mysql_connect("","","");
  3. if (!$con)
  4.   {
  5.   die('Could not connect: ' . mysql_error());
  6.   }
  7.   mysql_select_db("", $con);
  8.   $result = mysql_query("SELECT * FROM contactdetails");
  9.   $to = "";
  10.   $subject = "Results from query";
  11.   $body = "<table border='1'>;
  12.   $body .=<tr>
  13.   $body .=<th>Name:</th>
  14.   $body .=<th>Surname:</th>
  15.   $body .=<th>Number:</th>
  16.   $body .=<th>Email:</th>
  17.   $body .=<th>Moving From:</th>
  18.   $body .=<th>Moving To:</th>
  19.   $body .=<th>Date:</th>
  20.   $body .=</tr>";
  21.  while($row = mysql_fetch_array($result)){
  22.   $body .="<tr>";
  23.   $body .=<td>" . $row['Name'] . "</td>";
  24.   $body .=<td>" . $row['Surname'] . "</td>";
  25.   $body .=<td>" . $row['Number'] . "</td>";
  26.   $body .=<td>" . $row['Email'] . "</td>";
  27.   $body .=<td>" . $row['MovingFrom'] . "</td>";
  28.   $body .=<td>" . $row['MovingTo'] . "</td>";
  29.   $body .=<td>" . $row['Date'] . "</td>";
  30.   $body .=</tr>";
  31.   }
  32.   $body .="</table>";
  33.   mysql_close($con);
  34.   ";
  35.   $headers = "From: someone@example.com";
  36.   mail($to,$subject,$body,$headers);
  37.   echo "Mail sent to $to";
  38. ?>
  39.  
it gives me an error on line 23 which is the following line:
Expand|Select|Wrap|Line Numbers
  1.   $body .=<td>" . $row['Name'] . "</td>";
  2.  
Apr 20 '10 #5

Atli
Expert 5K+
P: 5,058
You left out the starting quote-mark. - All strings must be quoted, or PHP will try to parse them as a PHP command, which will fail with a Parse error.

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // This:
  3. $body .=<td>" . $row['Name'] . "</td>";
  4.  
  5. // Needs to be:
  6. $body .= "<td>" . $row['Name'] . "</td>";
  7.  
  8. // Or, even:
  9. $body .= "<td>{$row['Name']}</td>";
  10. ?>
Apr 20 '10 #6

P: 15
Thank you - That has now sent the email.
The format that it came out in is another story completely though.
I guess I need to change the headers so that it sends html mail .... let me see if i can find out how to do that. Thank you for your help
Apr 20 '10 #7

P: 15
Figured it out - thank you!
Ok last one - how do I make this form only retrieve NEW data from the database?
Apr 20 '10 #8

Atli
Expert 5K+
P: 5,058
Define: "new data".

Do you mean data within a certain range from the current date, or do you mean data that has not been displayed yet?
Apr 20 '10 #9

P: 15
I only want the script to pull through data that is new to the table.
The end result is that I would like a cron to run this script 3 times a day and automatically send the NEW data to a specified email address.
Apr 20 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.