mysql to php mailer

stlwellj · May 8, 2015

I am trying to send an email to an email address in my mysql table with the information on that row. My code is stopping at the date, time and sent check. I am unsure about this line. Please see my code below. It is a PHP only file as this will become a cron job.

My date and time are entered by the person entering the data.

Date Format: 2015-05-07

Time Format: 14:32

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

<?php

require ('class.phpmailer.php');
require ('login.php');

$link = mysql_connect($REMINDER_DB_HOST, $REMINDER_DB_USER, $REMINDER_DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($REMINDER_DB_NAME, $link);

if (!db_selected) {
	die('Can\'t use ' . DB_NAME . ' : ' . msql_error());
}
echo 'here';

$today = date(now);
$now = time(now);

echo $today;
echo $now;

$sql = mysql_query("SELECT * FROM alert WHERE date < $today || time < $now || sent == NULL") or die(mysql_error());

while($row = mysql_fetch_assoc($sql)) {


$name = $row['name'];
$to = $row['email'];
$date = $row['date'];
$time = $row['time'];
$eslticket = $row['eslticket'];
$notes = $row['notes'];


$mail = new PHPMailer;

$g_smtp_connection_mode   = 'ssl';
$g_phpMailer_method 	   = 2; 
$g_smtp_host 			  = 'smtp.gmail.com';
$g_smtp_port 			  = 465;
$g_smtp_username		  = $mailuser;
$g_smtp_password		  = $mailpassword;

echo 'here1';

$subject = 'ESL Ticket Reminder: ' .$eslticket;
$msg = 'Good Day, '.$name.'\n'.'You have requested an alert email to be sent to you about '.$eslticket.' on '.$date.' at '.$time.'. Please see below to review your notes on this ticket.'.'\n'.'\n'.$notes;

if(mail($to, $subject, $msg)) {
$sql = "INSERT INTO alert (sent) VALUES ('1')";
} else {

}

}

?>

</head>

<body>
</body>
</html>

requinix · May 8, 2015

You aren't using date or time properly, probably because you don't know what they do. Read the documentation.

When you get the date and time into the right form, the next problem is your query. The date and time will be strings, and strings in queries need quotes. In fact this seems to be another place where you don't quite know what you're working with. After you fix the quotes and the other syntax error, you may find that it's returning far more "alerts" than you wanted it to - your condition says that the date is in the past or the time of day is earlier in the day (which says nothing about the date itself) or that the alert hasn't been sent yet.

stlwellj · May 8, 2015

requinix

Once I fix the date and time will my code will update the sent column? I have the cron job set for every 30 seconds and I would like the code to bypass any of the lines that there is something in the sent column. Am I on the right track?

$sql = mysql_query("SELECT * FROM alert WHERE date < $today || time < $now || sent= NULL") or die(mysql_error());

stlwellj · May 8, 2015

I made the following updates:

$today = date("Y-m-d");

$now = date("h:i");

and I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':56 || sent = NULL' at line 1

stlwellj · May 8, 2015

I updated my code and it now it will only send the first row. I would like it to loop and place a 1 in the sent column. Also I wanted all three statements to match to pull the record but I can only get the 'OR' option to work.

<?php

require ('class.phpmailer.php');
require ('login.php');

$link = mysql_connect($REMINDER_DB_HOST, $REMINDER_DB_USER, $REMINDER_DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($REMINDER_DB_NAME, $link);

if (!db_selected) {
	die('Can\'t use ' . $REMINDER_DB_NAME . ' : ' . msql_error());
}
$today = date("Y-m-d");
$now = date("H:i");

echo $today;
echo "<br/>";
echo $now;
echo "<br/>";

$sql = mysql_query("SELECT * FROM alert WHERE date < '$today' OR time < '$now' OR sent = NULL") or die(mysql_error());

while($row = mysql_fetch_assoc($sql)) {
var_dump($row);
$name = $row['name'];
$to = $row['email'];
$date = $row['date'];
$time = $row['time'];
$eslticket = $row['eslticket'];
$notes = $row['notes'];

$mail = new PHPMailer;

$g_smtp_connection_mode   = 'ssl';
$g_phpMailer_method 	   = 2;
$g_smtp_host 			  = 'smtp.gmail.com';
$g_smtp_port 			  = 465;
$g_smtp_username		  = $mailuser;
$g_smtp_password		  = $mailpassword;


$mail->addAddress($to);
$mail->Subject = 'ESL Ticket Reminder: ' .$eslticket;
$mail->Body    = 'Good Day, '.$name."\r\n\r\n".'You have requested an alert email to be sent to you about '.$eslticket.' on '.$date.' at '.$time.'.'."\r\n\r\n" .'Please see below to review your notes on this ticket.'."\r\n\r\n".$notes."\r\n\r\n".'Thank you for using the reminder email system.';

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
    $sql = "UPDATE $REMINDER_DB_NAME SET sent='1' WHERE idalert=10";
	echo 'Message has been sent';
}
}

?>

jcbones · May 8, 2015

You need to store ALL of the ID's that you send mail to, then you need to update those rows.

	 
	while($row = mysql_fetch_assoc($sql)) {
	$ids[] = $row['idalert'];
	.
	.
	.
	} else {
	$sql = "UPDATE alert SET sent='1' WHERE idalert IN (" . implode(',',$ids) . ")";

stlwellj · May 8, 2015

I have updated the file and am now trying to delete the row. I still cannot get it to loop.

http://pastebin.com/rmsgjun9

stlwellj · May 8, 2015

I am trying to have rows from my sql database convert into an email and then delete the row. Right now it only works on the first row as an OR statement but I need an AND statement for date and time. I need it to loop so if there are multiple rows that fall into the send option.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


<?php

require ('class.phpmailer.php');
require ('login.php');

$link = mysql_connect($REMINDER_DB_HOST, $REMINDER_DB_USER, $REMINDER_DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($REMINDER_DB_NAME, $link);

if (!db_selected) {
	die('Can\'t use ' . $REMINDER_DB_NAME . ' : ' . msql_error());
}
$today = date("Y-m-d");
$now = date("H:i");

echo $today;
echo "<br/>";
echo $now;
echo "<br/>";

$sql = mysql_query("SELECT * FROM alert WHERE date < '$today' OR time < '$now'") or die(mysql_error());

while($row = mysql_fetch_assoc($sql)) {
$ids[] = $row['idalert'];
var_dump($row);
$name = $row['name'];
$to = $row['email'];
$date = $row['date'];
$time = $row['time'];
$eslticket = $row['eslticket'];
$notes = $row['notes'];

$mail = new PHPMailer;

$g_smtp_connection_mode   = 'ssl';
$g_phpMailer_method 	   = 2;
$g_smtp_host 			  = 'smtp.gmail.com';
$g_smtp_port 			  = 465;
$g_smtp_username		  = $mailuser;
$g_smtp_password		  = $mailpassword;


$mail->addAddress($to);
$mail->Subject = 'ESL Ticket Reminder: ' .$eslticket;
$mail->Body    = 'Good Day, '.$name."\r\n\r\n".'You have requested an alert email to be sent to you about '.$eslticket.' on '.$date.' at '.$time.'.'."\r\n\r\n" .'Please see below to review your notes on this ticket.'."\r\n\r\n".$notes."\r\n\r\n".'Thank you for using the reminder email system.';

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	$sql = "DELETE FROM alert WHERE idalert IN (" . implode(',',$ids) . ")";
	echo 'Message has been sent';
}

}
?>

</head>
<body>
</body>
</html>

fastsol · May 8, 2015

Why is the date and time stored separately in the db? Usually they are stored together as they tend to be the same thing. Maybe it would be helpful for us to see an example of a few rows from the db and how/why the rows should be "SELECT" together in your mind.

stlwellj · May 8, 2015

Below is my var_dump

array(7) { ["idalert"]=> string(2) "10" ["name"]=> string(14) "John Stilwell " ["email"]=> string(23) "stilwellj1983@gmail.com" ["date"]=> string(10) "2015-05-08" ["time"]=> string(5) "08:26" ["eslticket"]=> string(5) "ESL01" ["notes"]=> string(6) "Test 1" }

email that end user gets

From: No Reply <noreply@floridaecr.org>
Date: Fri, May 8, 2015 at 5:39 PM
Subject: ESL Ticket Reminder: ESL01
To: stilwellj1983@gmail.com

Good Day, John Stilwell

You have requested an alert email to be sent to you about ESL01 on 2015-05-08 at 08:26.

Please see below to review your notes on this ticket.

Test 1

Thank you for using the reminder email system.

fastsol · May 8, 2015

What are the data types of the date and time columns in your DB. If they are normal varchar, then you can't compare dates accurately or time accurately. The column types would need to be in the DATE and TIME format. Also, although they are not mysql reserved words, using date and time for column names isn't a great idea in my opinion. Because they have specific values and/or funcitons in php it can get messy trying to debug when you're calling things the same names as reserved words/thing in the programming language. Assigning such things to variables is different, but using them as static names is bad. My 2 cents worth.

stlwellj · May 8, 2015

I made the updates that you suggested and it works great.How do I loop for multiple emails and delete the row once it is sent?

Thank you so much for the help

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


<?php

require ('class.phpmailer.php');
require ('login.php');
date_default_timezone_set('America/New_York');

$link = mysql_connect($REMINDER_DB_HOST, $REMINDER_DB_USER, $REMINDER_DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($REMINDER_DB_NAME, $link);

if (!db_selected) {
	die('Can\'t use ' . $REMINDER_DB_NAME . ' : ' . msql_error());
}
$today = date("Y-m-d H:i:s");

echo $today;
echo "<br/>";

$sql = mysql_query("SELECT * FROM alert WHERE rdate < '$today'") or die(mysql_error());

while($row = mysql_fetch_assoc($sql)) {
$ids[] = $row['idalert'];
var_dump($row);
$name = $row['name'];
$to = $row['email'];
$rdate = $row['rdate'];
$newDate = date("l, F j, Y", strtotime($rdate));
$newTime = date("g:i A", strtotime($rdate));
$eslticket = $row['eslticket'];
$notes = $row['notes'];

$mail = new PHPMailer;

$g_smtp_connection_mode   = 'ssl';
$g_phpMailer_method 	   = 2;
$g_smtp_host 			  = 'smtp.gmail.com';
$g_smtp_port 			  = 465;
$g_smtp_username		  = $mailuser;
$g_smtp_password		  = $mailpassword;


$mail->addAddress($to);
$mail->Subject = 'ESL Ticket Reminder: ' .$eslticket;
$mail->Body    = 'Good Day, '.$name."\r\n\r\n".'You have requested an alert email to be sent to you about '.$eslticket.' on '.$newDate.' at '.$newTime.'.'."\r\n\r\n" .'Please see below to review your notes on this ticket.'."\r\n\r\n".$notes."\r\n\r\n".'Thank you for using the reminder email system.';

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	$sql = "DELETE FROM alert WHERE idalert IN (" . implode(',',$ids) . ")";
	echo 'Message has been sent';
}

}
?>

</head>
<body>
</body>
</html>

Echo Dump

2015-05-08 18:36:25
array(6) { ["idalert"]=> string(2) "15" ["name"]=> string(14) "John Stilwell " ["email"]=> string(23) "stilwellj1983@gmail.com" ["rdate"]=> string(19) "2015-05-08 11:15:00" ["eslticket"]=> string(13) "ESL1111111111" ["notes"]=> string(6) "Test 1" } Message has been sent

Edited May 8, 2015 by stlwellj

fastsol · May 8, 2015

You forgot to change the row var on this line to rdate

$rdate = $row['date'];

stlwellj · May 8, 2015

You forgot to change the row var on this line to rdate
$rdate = $row['date'];

I caught that when I read though my code again. I made the correction above. Would you help with why it won't loop or delete the row once it sends.

Thank you for all the help you have been

fastsol · May 8, 2015

Are you sure you are actually getting more than a single row? If you do a print_r($ids) at the bottom outside the while(), does it show more than a single id? As for the delete, you're not running a mysql_query, you only made the sql string. Plus if the intention is to run the delete only once using the IN() in the query, then you need to move it outside the while() also. The placement now has it run with every loop of the while(), which is fine to do also but pointless then to use the IN().

stlwellj · May 9, 2015

That worked!!!! It now sends all the emails from the past. I cannot get the delete to work though.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


<?php

require ('class.phpmailer.php');
require ('login.php');
date_default_timezone_set('America/New_York');

$link = mysql_connect($REMINDER_DB_HOST, $REMINDER_DB_USER, $REMINDER_DB_PASSWORD);

if (!link) {
	die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($REMINDER_DB_NAME, $link);

if (!db_selected) {
	die('Can\'t use ' . $REMINDER_DB_NAME . ' : ' . msql_error());
}
$today = date("Y-m-d H:i:s");

echo $today;
echo "<br/>";

$sql = mysql_query("SELECT * FROM alert WHERE rdate < '$today'") or die(mysql_error());

while($row = mysql_fetch_assoc($sql)) {
$ids[] = $row['idalert'];
var_dump($row);
$name = $row['name'];
$to = $row['email'];
$rdate = $row['rdate'];
$newDate = date("l, F j, Y", strtotime($rdate));
$newTime = date("g:i A", strtotime($rdate));
$eslticket = $row['eslticket'];
$notes = $row['notes'];

$mail = new PHPMailer;

$g_smtp_connection_mode   = 'ssl';
$g_phpMailer_method 	   = 2;
$g_smtp_host 			  = 'smtp.gmail.com';
$g_smtp_port 			  = 465;
$g_smtp_username		  = $mailuser;
$g_smtp_password		  = $mailpassword;


$mail->addAddress($to);
$mail->Subject = 'ESL Ticket Reminder: ' .$eslticket;
$mail->Body    = 'Good Day, '.$name."\r\n\r\n".'You have requested an alert email to be sent to you about '.$eslticket.' on '.$newDate.' at '.$newTime.'.'."\r\n\r\n" .'Please see below to review your notes on this ticket.'."\r\n\r\n".$notes."\r\n\r\n".'Thank you for using the reminder email system.';

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	echo 'Message has been sent';
}

}
$sql = mysql_query("SELECT * FROM alert");
$sql = ("DELETE FROM alert WHERE idalert=$ids");
print_r($ids);
?>

</head>
<body>
</body>
</html>

fastsol · May 9, 2015

Well now that you moved the delete outside the while(), you need the IN() and implode to build the query properly. You don't need the IN() if the delete is INSIDE the while() cause you would just delete the row directly by the $row['idalert'], understand?

stlwellj · May 9, 2015

kinda I think

when I have it like this it loops and sends all the emails

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	echo 'Message has been sent';
}

}
print_r($ids);
$sql = "DELETE FROM alert WHERE idalert IN (" . implode(',',$ids) . ")";
?>

When I have it like this it stops after the first row

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	$sql = "DELETE FROM alert";
	echo 'Message has been sent';
}

}
print_r($ids);
?>

fastsol · May 9, 2015

No, like this

if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	echo 'Message has been sent';
}
 
}

mysql_query("DELETE FROM alert WHERE idalert IN(".implode(",", $ids).")");
print_r($ids);

In you last post, neither example you showed was even running the query. All you did was, again, define the sql string.

stlwellj · May 9, 2015

Thank you so VERY MUCH that work great!!!!!!!!

CroNiX · May 12, 2015

No, like this
if(!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
	echo 'Message has been sent';
}
 
}

mysql_query("DELETE FROM alert WHERE idalert IN(".implode(",", $ids).")");
print_r($ids);
In you last post, neither example you showed was even running the query. All you did was, again, define the sql string.

I think actually you might only want to track the IDs that the email was successfully sent to, and delete only those. So build a new array of IDs to be deleted within the ELSE. Otherwise you won't be able to resend to people who the email wasn't successfully sent to the first time as they are now deleted from the db.

Another thing to check before running the MySQL delete is whether the $IDs array contains any values, or there will be a mysql error if the array is empty.

Edited May 12, 2015 by CroNiX

Sign In

mysql to php mailer

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information