Form which updates MySQL DB, then sends email notification

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ivdezine
Forum Newbie
Posts: 5
Joined: Wed Sep 19, 2007 10:49 am

Form which updates MySQL DB, then sends email notification

Post 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!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

How is it not working? Are you getting an error? Is the email blank? What have you already tried?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
ivdezine
Forum Newbie
Posts: 5
Joined: Wed Sep 19, 2007 10:49 am

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you looked into using a mailing library such as Swift?
ivdezine
Forum Newbie
Posts: 5
Joined: Wed Sep 19, 2007 10:49 am

Post by ivdezine »

Actually I have not. Would swift allow me to include DB data in the body of the email?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Unless something has vastly changed, yes, as far as I am aware.
ivdezine
Forum Newbie
Posts: 5
Joined: Wed Sep 19, 2007 10:49 am

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
ivdezine
Forum Newbie
Posts: 5
Joined: Wed Sep 19, 2007 10:49 am

Post by ivdezine »

Awesome, thanks a ton Feyd!! :D
Post Reply