Sending results of an SQL query via email

[CalvinSmith]

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:

<?php
$con = mysql_connect("*****","*****","*****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("*****", $con);
  $result = mysql_query("SELECT * FROM contactdetails");
  echo "<table border='1'>
  <tr>
    <th>Name:</th>
    <th>Surname:</th>
    <th>Number:</th>
    <th>Email:</th>
    <th>Moving From:</th>
    <th>Moving To:</th>
    <th>Date:</th>
  </tr>";

  while($row = mysql_fetch_array($result)){
    echo "<tr>";
    echo "<td>" . $row['Name'] . "</td>";
    echo "<td>" . $row['Surname'] . "</td>";
    echo "<td>" . $row['Number'] . "</td>";
    echo "<td>" . $row['Email'] . "</td>";
    echo "<td>" . $row['MovingFrom'] . "</td>";
    echo "<td>" . $row['MovingTo'] . "</td>";
    echo "<td>" . $row['Date'] . "</td>";
    echo "</tr>";
  }
  echo "</table>";

  mysql_close($con);
?>

Thanks in anticipation!

[Atli]

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.

[CalvinSmith]

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:

<?php
$con = mysql_connect("*****","*****","*****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("*****", $con);
  $result = mysql_query("SELECT * FROM contactdetails");
  $to = "me@example.com";
  $subject = "Results from query";
  $body = "
  echo "<table border='1'>
  <tr>
    <th>Name:</th>
    <th>Surname:</th>
    <th>Number:</th>
    <th>Email:</th>
    <th>Moving From:</th>
    <th>Moving To:</th>
    <th>Date:</th>
  </tr>";

  while($row = mysql_fetch_array($result)){
    echo "<tr>";
    echo "<td>" . $row['Name'] . "</td>";
    echo "<td>" . $row['Surname'] . "</td>";
    echo "<td>" . $row['Number'] . "</td>";
    echo "<td>" . $row['Email'] . "</td>";
    echo "<td>" . $row['MovingFrom'] . "</td>";
    echo "<td>" . $row['MovingTo'] . "</td>";
    echo "<td>" . $row['Date'] . "</td>";
    echo "</tr>";
  }
  echo "</table>";
  mysql_close($con);
  ";
  $headers = "From: someone@example.com";
  mail($to,$subject,$body,$headers);
  echo "Mail sent to $to";
?>

[Atli]

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:
[code=php]echo "<table>";
echo "<tr><td>.. .";[/code]

Do:
[code=php]$body = "<table>";
$body .= "<tr><td>.. .";[/code]

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.

[CalvinSmith]

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

<?php
$con = mysql_connect("","","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("", $con);
  $result = mysql_query("SELECT * FROM contactdetails");
  $to = "";
  $subject = "Results from query";
  $body = "<table border='1'>;
  $body .=<tr>
  $body .=<th>Name:</th>
  $body .=<th>Surname:</th>
  $body .=<th>Number:</th>
  $body .=<th>Email:</th>
  $body .=<th>Moving From:</th>
  $body .=<th>Moving To:</th>
  $body .=<th>Date:</th>
  $body .=</tr>";
 while($row = mysql_fetch_array($result)){
  $body .="<tr>";
  $body .=<td>" . $row['Name'] . "</td>";
  $body .=<td>" . $row['Surname'] . "</td>";
  $body .=<td>" . $row['Number'] . "</td>";
  $body .=<td>" . $row['Email'] . "</td>";
  $body .=<td>" . $row['MovingFrom'] . "</td>";
  $body .=<td>" . $row['MovingTo'] . "</td>";
  $body .=<td>" . $row['Date'] . "</td>";
  $body .=</tr>";
  }
  $body .="</table>";
  mysql_close($con);
  ";
  $headers = "From: someone@example.com";
  mail($to,$subject,$body,$headers);
  echo "Mail sent to $to";
?>

it gives me an error on line 23 which is the following line:

  $body .=<td>" . $row['Name'] . "</td>";

[Atli]

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.

[code=php]<?php
// This:
$body .=<td>" . $row['Name'] . "</td>";

// Needs to be:
$body .= "<td>" . $row['Name'] . "</td>";

// Or, even:
$body .= "<td>{$row['Name']}</td>";
?>[/code]

[CalvinSmith]

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

[CalvinSmith]

Figured it out - thank you!
Ok last one - how do I make this form only retrieve NEW data from the database?

[Atli]

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?

[CalvinSmith]

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.