Jump to content

mysql to php mailer


stlwellj

Recommended Posts

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>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

 

  1. $sql = mysql_query("SELECT * FROM alert WHERE date < $today || time < $now || sent= NULL") or die(mysql_error());
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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';
}
}

?> 
Link to comment
Share on other sites

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) . ")";

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 by stlwellj
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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().

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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);
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by CroNiX
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.