Page 1 of 1

Form which updates MySQL DB, then sends email notification

Posted: Wed Sep 19, 2007 10:59 am
by ivdezine
Hello,
I have a form which allows members of my team to update a particular table, upon updating I have php set to send an email to a certain address. What I'd like to include in this email is the table after it's been updated.

Here's what I'm currently using which isn't working:

Code: Select all

<?php
    $to = "user@domain.com";
		$bcc = "user@domain.com";
    $from = "user@domain.com";
    $subject = "Subject";
	$mail_body =
	
	// DB Connection
$link = mysql_connect('localhost', 'username', 'password')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('dbname') or die('Could not select database');

// SQL Query
$query = 'SELECT * FROM dbtable';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $mail_body = "<table>";
$res = mysql_query("SELECT service, CurrentStatus FROM status");
while($res2 = mysql_fetch_array($res) )
{
   $mail_body .= "<tr>";
   foreach($res2 as $value)
   {
      $mail_body .= "<td> $value </tr>";
   }
  $mail_body .= "</tr>";
}
$mail_body .= "</table>";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
	
	

    $headers  = "From: $from\r\n";
    $headers .= "Content-type: text/html\r\n";
    $to = "$to"; 
    mail($to, $bcc, $subject, $message, $headers);
	
	header("Refresh: 5; url=");
?>
Any ideas on how this can be done??

Thanks!

Posted: Wed Sep 19, 2007 3:52 pm
by Begby
How is it not working? Are you getting an error? Is the email blank? What have you already tried?

Posted: Wed Sep 19, 2007 7:55 pm
by califdon
You need to be aware that just sending html tags in the body of an email doesn't assure that it will be rendered as html in every email client. At the very least, you'll need to insert a MIME header.

Posted: Thu Sep 20, 2007 7:49 am
by ivdezine
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


It's not working because the email body has no data whatsoever, it's just a blank email.  I'm not concerned about the email client, everyone in my team uses MS Outlook and the email is meant only for internal delivery.  

After a bit of searching I found a post which described a similar problem, but the poster wasn't able to resolve it either.  Upon seeing his method of doing this, I made some changes to my script and here is what I'm using now:

Code: Select all

<?php

    $to = "user@domain.com";
    $from = "user@domain.com";
    $subject = "subject";
    //
    $message = 
$connection = mysql_pconnect("localhost","username","pw") or die('Could not connect: ' . mysql_error());
mysql_select_db("status1_123TStatus", $connection) or die('Could not select database' . mysql_error());

// no problems so query the db
$SQL = "SELECT * FROM tbname;";
$result = mysql_query($SQL) or die('Query failed: ' . mysql_error());
$message = "$output";
$output = "<table border=\"1\">\n" .
"<tr>\n" .
"<td>Id</td>\n" .
"<td>service</td>\n" .
"<td>CurrentStatus</td>\n" .
"</tr>\n";

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$id = $row[0];
$service = $row[1];
$CurrentStatus = $row[2];
$output .= "\t<tr>\n" .
"\t\t<td>$id</td>\n" .
"\t\t<td>$service</td>\n" .
"\t\t<td>$CurrentStatus</td>\n" .
"\t</tr>\n";
}

$output .= "</table>\n";
mysql_free_result($result);

   //
   
    $headers  = "From: $from\r\n";
    $headers .= "Content-type: text/html\r\n";
    $to = "$to"; 
    mail($to, $subject, $message, $headers);
	
	header("Refresh: 5; url=");

?>
Now, I get the email with the table headers but not the actual data from the DB table. Thoughts???


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Sep 20, 2007 9:21 am
by feyd
Have you looked into using a mailing library such as Swift?

Posted: Thu Sep 20, 2007 12:12 pm
by ivdezine
Actually I have not. Would swift allow me to include DB data in the body of the email?

Posted: Thu Sep 20, 2007 12:19 pm
by feyd
Unless something has vastly changed, yes, as far as I am aware.

Posted: Thu Sep 20, 2007 2:32 pm
by ivdezine
Do you know where I can find it? A search on google just comes back with various companies by that name but nothing to do with email & mysql scripts. Thanks!

Posted: Thu Sep 20, 2007 4:44 pm
by feyd
swiftmailer.org

It's author is our very own moderator d11wtq. It has a dedicated forum if you look at the bottom of the index.

Posted: Fri Sep 21, 2007 8:33 am
by ivdezine
Awesome, thanks a ton Feyd!! :D