473,554 Members | 2,153 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Sending results of an SQL query via email

15 New Member
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
9 14920
Atli
5,058 Recognized Expert Expert
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
CalvinSmith
15 New Member
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
5,058 Recognized Expert Expert
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
CalvinSmith
15 New Member
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
5,058 Recognized Expert Expert
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
CalvinSmith
15 New Member
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
CalvinSmith
15 New Member
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
5,058 Recognized Expert Expert
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
CalvinSmith
15 New Member
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

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

Similar topics

4
9428
by: Sylvie Stone | last post by:
Hi All - I have created a survey and I would like the results of the survey to be emailed the boss in *plain text*. Can someone PLEASE take a look and tell me why carriage returns are not being inserted after each $body ? The email comes out as one big line. THANK YOU! Sylvie. include('Mail.php');
2
1251
by: James | last post by:
Is there any simple way of doing this? I want to redirect my end users to a "results" page that lists the details of their order, but I also want to e-mail them the exact same think (not a link). All of my end users have HTML e-mail enabled, and I know that I could just create an e-mail manually. But, I was curious whether I could do this in...
2
3310
by: serendipity | last post by:
Hi, I'm not sure if this is possible as i've googled everywhere, but i have a select query that returns a customer record with their associated sales orders. I would like to automate a process which sends an email reminder to each customer in the database, that has outstanding orders. This email reminder should have the results of the query...
5
2611
by: BaWork | last post by:
I have a web form where a client can select which site members to send an email to. This form is populated from the contents of the member table, so the form can have 0-x names listed on it depending on member expiration dates. When the form is submitted, the code loops through the form contents and sends an email to those members that...
1
1165
by: Ken Barz | last post by:
Hi, I'm working on a program that will periodically run a query on a SQL database. What I would like it to do is to automatically take the results of that query (should be only a couple of lines at a time,) add that information to the body of an email and automatically send it. Any idea of how to do the email piece of this? I'm...
0
5105
by: The Programmer | last post by:
Assistance needed urgently! I use a Sybase Database with SQL Anywhere 9 and need to send the SQL query result as the body of the e-mail from within SQL. Here's what I have thusfar: ======================================================= bEGIN SET NOCOUNT ON select distinct
0
1889
by: The Programmer | last post by:
Assistance needed urgently! I use a Sybase Database with SQL Anywhere 9 and need to send the SQL query result as the body of the e-mail from within SQL. Here's what I have thusfar: ================================================== ===== bEGIN SET NOCOUNT ON select distinct
0
2306
by: krouxsa | last post by:
Hi There people... Please help me with this query. I want to send 1 email to multiple email addresses stored in a database. Will it be possible? I'm using SQL Server 2005 and already set up the Database mail. I also tested the mail and it works fine. My problem is sending mail to multiple emails recipients stored in a Database
13
1296
by: allan6874 | last post by:
Hello, Can someone please provide me with the code that will allow me to send the results of formmail fields to web page as oppose to sending the results to an e-mail address. Can this be done? Thanks Allan Jones
0
7530
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7808
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8047
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7570
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5162
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3570
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2022
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
845
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.