automatic php email

[ianhaney50]

Hi

 

I am looking to see if anyone knows of any php scripts that sends out a automatic email 14 days before a expiry date stored in a mysql database

 

I know it can be done with cron job which is ok, I have looked all over Google for a php script that does a php automatic email, I found some but were really old

 

Thank you in advance

 

Ian

[fastsol]

A cron job is the mechanism that will run the script and do whatever you have in the script coded to do, just like visiting the page in the browser basically.  There won't be any premade scripts that will totally suit your needs, it would be best to just build it yourself.  Depending on how many emails you plan on sending a one time, you may need to que them in a db or something.  There is a lot that goes in to what you are asking.  Many things have to be considered.  If you are not familiar with sending proper emails in php or are currently not using a php library like phpmailer, I highly suggest playing with that before you go any further.  Mass mailing can be a hard thing to do well and not get marked as spam for it.  Once you're marked as a spammer, it is very hard to get back on the nice list with almost any email provider.

 

Here is a tutorial series that shows the basics.  I haven't watched it in a long time so I can't speak to it's best coding practices of today, but it's a start. https://www.developphp.com/video/PHP#Mass-Email-Newsletter-Bulk-Batch-Send

[ianhaney50]

Hi fastsol

 

I have been working on the following php script, the first bit I have commented out was a test to make sure I received the email ok and I did in my inbox so that is all good then started working on the php script underneath but I am just getting a blank white page, I have php error reporting coding in it but is not displaying no errors?

<?php 
    /*ini_set( 'display_errors', 1 );
    error_reporting( E_ALL );
    $from = "noreply@irhwebsites.co.uk";
    $to = "ianhaney@irhwebsites.co.uk";
    $subject = "PHP Mail Test script";
    $message = "This is a test to check the PHP Mail functionality";
    $headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Test email sent";*/
?>

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['visitor_id']) && is_numeric($_GET['visitor_id']) && $_GET['visitor_id'] > 0)
 {
 // query db
 $visitor_id = $_GET['visitor_id'];
 $result = mysql_query("SELECT * FROM visitors WHERE visitor_id=$visitor_id")
 or die(mysql_error()); 
 $row = mysql_fetch_array($result);
 
 // check that the 'id' matches up with a row in the databse
 if($row)
 {

$visitor_email = $row['visitor_email'];
 
$sql = "SELECT * FROM visitors WHERE (visitor_tax AND visitor_mot AND visitor_insurance > DATE_SUB(CURDATE(), INTERVAL 2 WEEK)";
 
if($sql){
$to = $_POST ['visitor_email'];
$subject = "Expiry Tax Date Approaching";
$message = "Hello! Please be informed that your tax is due to expire on $visitor_tax";
$from = "noreply@irhwebsites.co.uk";
$headers = "From: $from";
$sent = mail($to,$subject,$message,$headers);

if ($sent){
    $statusMessage = "Mail Successfully Sent.";
}else{
    $statusMessage = "Mail Unsuccessfully Sent";
}
}
 }
 }
?>

[fastsol]

Well syntax wise, your page is fine.  Your logic is the problem.  I revised the main if() code below the connection.  Comments are added too.

// This is a simpler way to achieve the same thing and set the $visitor_id in the same line.
if (isset($_GET['visitor_id']) && ($visitor_id = (int)$_GET['visitor_id']) > 0)
{
	// query db
	$result = mysql_query("SELECT * FROM visitors WHERE visitor_id = ".$visitor_id) or die(mysql_error());
	
	// Using assoc is better in my opinion.  Yes array gives you the same info, but it also gives double the info in numeric indexes, which typically are not needed. 
	$row = mysql_fetch_assoc($result);
	
	// check that the 'id' matches up with a row in the databse
	if($row)
	{
		// Why is this here? you're not even running a query with it but you're checking it's existance below.
		$sql = "SELECT * FROM visitors WHERE (visitor_tax AND visitor_mot AND visitor_insurance > DATE_SUB(CURDATE(), INTERVAL 2 WEEK)";
		
		// This will always be true cause you're setting the var just above to a string value, but never run a query for some reason????
		if($sql)
		{
			// You had $to set to $_POST['visitor_email'] which isn't even on here in any manner.
			$to = $row['visitor_email'];
			$subject = "Expiry Tax Date Approaching";
			$message = "Hello! Please be informed that your tax is due to expire on $visitor_tax";
			$from = "noreply@irhwebsites.co.uk";
			$headers = "From: $from";
			$sent = mail($to,$subject,$message,$headers);
			
			if ($sent){
				$statusMessage = "Mail Successfully Sent.";
			}else{
				$statusMessage = "Mail Unsuccessfully Sent";
			}
		}
	}
}

I hope that you are actually indenting your code properly and it only looked the way it did here cause of how you copied it to the forum.  If that is not the case, you seriously need to learn how to indent your code, it will make life so much easier for you or anyone else that is trying to help you.  As I said before, you NEED to use a php mail library.  You may have received a test email to your inbox, but I guarantee that most people will not receive the emails if you send them the way you are.  Email is a very very tricky thing to get right and ensure it reaches an inbox.  Simply using the mail() will not get you very far, it may appear to you that it works but it won't for most of your visitor_email people.

[ianhaney50]

Hi

 

Sorry for my late reply

 

I have just switched to using phpmailer but am not receiving any email and the page is still blank with no errors

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['visitor_id']) && ($visitor_id = (int)$_GET['visitor_id']) > 0)
 {
 // query db
 $result = mysql_query("SELECT * FROM visitors WHERE visitor_id = ".$visitor_id) or die(mysql_error());
 
 // Using assoc is better in my opinion.  Yes array gives you the same info, but it also gives double the info in numeric indexes, which typically are not needed. 
	$row = mysql_fetch_assoc($result);
 
 	// check that the 'id' matches up with a row in the databse
	if($row)
	{
 
$sql = "SELECT * FROM visitors WHERE (visitor_tax AND visitor_mot AND visitor_insurance > DATE_SUB(CURDATE(), INTERVAL 2 WEEK)";

{
	
if($sql){
$to = $row['visitor_email'];

require("class.phpmailer.php");

$mail = new PHPMailer();

$mail->IsMail();  // telling the class to use SMTP
$mail->Host     = "mail.broadwaymediadesigns.co.uk"; // SMTP server

$mail->From     = "noreply@broadwaymediadesigns.co.uk";
$mail->AddAddress("ianhaney@broadwaymediadesigns.co.uk");

$mail->Subject  = "TEST EMAIL";
$mail->Body     = "Hi! \n\n This is my test e-mail sent through PHPMailer.";
$mail->WordWrap = 50;

if(!$mail->Send()) {
  echo 'Message was not sent.';
  echo 'Mailer error: ' . $mail->ErrorInfo;
} else {
  echo 'Message has been sent.';
}

}
}
 }
 }
?>
</body>
</html>

[fastsol]

You might be confusing it with these 2 lines.

$mail->IsMail();  // telling the class to use SMTP
$mail->Host     = "mail.broadwaymediadesigns.co.uk"; // SMTP server

The IsMail() is not needed at all and it does not tell it to use smtp, it tells it to use the native php mail().

By setting Host, it might think you are wanting to use smtp, but you haven't given it any of the needed params to do so.

Are you running this from a localhost machine or on a live hosting environment?  If you're on localhost, you should likely be using smtp through like gmail.  Localhost doesn't come pre-setup with a mail server installed.  The phpmailer site has examples of how to use smtp for gmail.

[ianhaney50]

Ahh ok so shall I set that back to SMTP instead of IsMail?

 

no is on a live hosting environment

[fastsol]

Don't set ismail. Don't set issmtp unless you actually want to send it through a smtp server like gmail.  If you do want to use smtp, then you need to look at the examples on the phpmailer website for doing so.

[ianhaney50]

Ahh ok

 

I have taken that line out or do I need the line but just remove IsSMTP part

[ianhaney50]

I got the test email script and put that in and worked perfect so I put the database mysqli coding back in and the email don't work, is just a blank white page

 

<?php

ini_set('display_startup_errors',1);

ini_set('display_errors',1);

error_reporting(-1);

?>

 

<html>

<title>Automatic Email</title>

<body>

 

<?php 

 

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 

// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  

mysqli_select_db($db,"") or die(mysqli_error($db));  

 

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";  

$result = mysqli_query($db, $query) or die(mysqli_error($db));  

 

//fetch tha data from the database 

while ($row = mysqli_fetch_array($result)) {

 

    ini_set( 'display_errors', 1 );

    error_reporting( E_ALL );

    $from = "noreply@irhwebsites.co.uk";

    $to = "ianhaney@irhwebsites.co.uk";

    $subject = "PHP Mail Test script";

    $message = "This is a test to check the PHP Mail functionality";

    $headers = "From:" . $from;

    mail($to,$subject,$message, $headers);

    echo "Test email sent";

}

//close the connection

mysqli_close($db);

?>

 

</body>

</html>

 

I was just thinking if the php script stops running before it gets to the email script part?

[ianhaney50]

Hi

 

Sorry I trying to debug my PHP bit by bit and have put in a connection success or fail message as well as echo $query and all it says is success so I know it connecting to the database ok as when I change the database info it comes up with fail check parameters message so am thinking is there something wrong with my $query line, I have commented out the email part for now and do one bit at a time, below is the coding I currently have

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
	echo 'success';
}

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";  
$result = mysqli_query($db, $query) or die(mysqli_error($db)); 

//fetch tha data from the database 
while ($row = mysqli_fetch_array($result)) {

	echo $query;

    /*ini_set( 'display_errors', 1 );
    error_reporting( E_ALL );
    $from = "noreply@irhwebsites.co.uk";
    $to = "ianhaney@irhwebsites.co.uk";
    $subject = "PHP Mail Test script";
    $message = "This is a test to check the PHP Mail functionality";
    $headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Test email sent";*/
}
//close the connection
mysqli_close($db);
?>

</body>
</html>

[ianhaney50]

Hi, sorry got a update

 

I am getting there I think, I put in echo $sqlcommand and echoed that out all ok so then put in the echo $row results so it displayed the results from the mysql database table and has done that ok as I put WHERE clause in and defined what WHERE clause values and outputted all ok so think the issue was with the following line

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";

So what would be the correct coding to be where the results are displayed if the date from visitor_mot is tomorrow for example so want it to display if the date is 1 day away, hope that makes sense

 

My current coding is below

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
	echo 'success';
}

$sqlCommand = "SELECT * FROM visitors WHERE visitor_county='Essex' AND visitor_id='3' "; // no limit

$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); 

/*$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";  
$result = mysqli_query($db, $query) or die(mysqli_error($db));*/

//fetch tha data from the database 
while ($row = mysqli_fetch_array($query)) {

echo "<br><br>";
echo $row['visitor_id'];
echo "<br><br>";
echo $row ['visitor_name'];
  echo "<br />";

	/*echo $sqlCommand;*/

    /*ini_set( 'display_errors', 1 );
    error_reporting( E_ALL );
    $from = "noreply@irhwebsites.co.uk";
    $to = "ianhaney@irhwebsites.co.uk";
    $subject = "PHP Mail Test script";
    $message = "This is a test to check the PHP Mail functionality";
    $headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Test email sent";*/
}

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
?>

</body>
</html>

Feel like I am getting closer with it slowly

[Barand]

If visitor_mot is a datetime then you need to ignore the time portion.

SELECT * FROM visitors WHERE DATE(visitor_mot) = DATE_ADD(curdate(), INTERVAL 1 DAY)

or

SELECT * FROM visitors WHERE DATE(visitor_mot) = CURDATE() + INTERVAL 1 DAY

[ianhaney50]

Hi Barand

 

Thank you for the reply and coding, I got the following and it seems to be working I think, it is outputting the insurance date that expires on the 28th June 2015

$sqlCommand = "SELECT * FROM visitors WHERE visitor_insurance > DATE_ADD(NOW(), INTERVAL -1 DAY)"; // no limit

Is that right?

 

The other little thing I am trying to work out is the email sends all ok which is good but the date format in the email is 2015-06-28 where as I am trying to get it in UK date format, I have got it displaying in UK format on the page when it is being outputte, below is my whole code so far

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
	echo 'success';
}

$sqlCommand = "SELECT * FROM visitors WHERE visitor_insurance > DATE_ADD(NOW(), INTERVAL -1 DAY)"; // no limit

$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); 

//fetch tha data from the database 
while ($row = mysqli_fetch_array($query)) {

echo "<br><br>";
echo $row['visitor_id'];
echo "<br><br>";
echo $row ['visitor_name'];
echo "<br><br>";
echo date("d F Y",strtotime($row['visitor_insurance']));
  echo "<br />";

	/*echo $sqlCommand;*/

    ini_set( 'display_errors', 1 );
    error_reporting( E_ALL );
    $from = "noreply@irhwebsites.co.uk";
    $to = "ianhaney@irhwebsites.co.uk";
    $subject = "PHP Mail Test script";
    /*$message = "This is a test to check the PHP Mail functionality";*/
	$message = "Some text {$row['visitor_name']}";
	$message = "Some text {$row['visitor_insurance']}";
    $headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Test email sent";
}

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
?>

</body>
</html>

In my database I got the date columns as the type DATE, is that ok as that?

[Barand]

If your queries are only concerned with DATE types then you should use CURDATE() and not NOW(). The NOW() function also contains the time of day.

 

Alternatively you can format the date in the query

SELECT DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate

Don't use SELECT * unless you really do want every column retrieved, it's inefficient and obscures the purpose of the query. Specify what you need.

SELECT visitor_id
    , visitor_name
    , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate
FROM visitors 
WHERE visitor_insurance > DATE_ADD(CURDATE(), INTERVAL -1 DAY)

Edited June 20, 2015 by Barand

[lovephp]

Create a function

 

function Sendemail(){

Query and stuff here

 

}

 

Then include it on ur config file etc

[ianhaney50]

I've got the following Barand but its still making the date yyyy-mm-dd in the email I receive

$sqlCommand = "SELECT * , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_insurance > DATE_ADD(CURDATE(), INTERVAL -1 DAY)"; // no limit

Below is my email coding

$from = "noreply@irhwebsites.co.uk";
    $to = "ianhaney@irhwebsites.co.uk";
    $subject = "PHP Mail Test script";
    /*$message = "This is a test to check the PHP Mail functionality";*/
	$message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['visitor_insurance']};";
	$headers = "From:" . $from;
    mail($to,$subject,$message, $headers);
    echo "Test email sent";

The email comes out like the following

 

Name: Ian Haney

 Insurance Expiry Date: 2015-06-28;

[fastsol]

This

$message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['visitor_insurance']};";

Needs to be this

$message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['vidate']};";

[ianhaney50]

Thank you fastsol

 

I change the line to

$message = "Name: {$row['visitor_name']} \n Insurance Expiry Date: {$row['vidate']}";

but no email is coming through now unless it's delayed but been getting them 15 secs after I reload the page

[ianhaney50]

Sorry is ok now and receiving the emails, so now will this script check for any visitor_insurance dates that is due to expire within 7 days and send me a email with the info

 

that right?

 

I know I need the cron to check the php file every day but just need to know if the script is right?

[ianhaney50]

Sorry am stuck on this bit, I have added in more into the $sql Command query but am getting a error

$sqlCommand = "SELECT * , DATE_FORMAT(visitor_tax, '%e %M %Y') (visitor_mot, '%e %M %Y') (visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_tax, visitor_mot, visitor_insurance > DATE_ADD(CURDATE(), INTERVAL 7 DAY)"; // no limit

I first tried the following

$sqlCommand = "SELECT * , DATE_FORMAT(visitor_tax, visitor_mot, visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_tax, visitor_mot, visitor_insurance > DATE_ADD(CURDATE(), INTERVAL 7 DAY)"; // no limit

but it didn't like that either

 

Sorry the error I am getting with the current script which is the second lot of coding above

 

Incorrect parameter count in the call to native function 'DATE_FORMAT'

Edited June 20, 2015 by ianhaney50

[Barand]

You need to format the dates separately

"SELECT *
  , DATE_FORMAT(visitor_tax, '%e %M %Y') as taxdate
  , DATE_FORMAT(visitor_mot, '%e %M %Y') as motdate
  , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate

SELECT * !!! - ok, don't listen, that's the best way to stop people helping

 

Exactly what date conditions are you wanting here?

 

WHERE visitor_tax, visitor_mot, visitor_insurance > DATE_ADD(CURDATE(), INTERVAL 7 DAY)

[ianhaney50]

Sorry I did take on board the bit you said about the SELECT * but wanted everything in or is it best to not have the * and list what I need one by one

 

The conditions I am wanting is in my database table I have a date for visitor_tax, a different date for visitor_mot and a different date for visitor_insurance and I want the email to be sent out to the email address for the visitor to let them know the expiry date for them 3 things is 7 days away

 

Hope that makes sense

 

I'll def change the SELECT * to listing what I need one by one if is better to do it that way than having select *

[Barand]

Let's take the problems one by one

 

1. Checking for date < today + 7 days will also return records from last week, last month, last year, as those dates all fit the condition. What you need is dates between today and today + 7 days.
2. You need to check the dates individually. I am assuming that if any one of those dates is due then an email is sent (therefore using OR condition). If all three must be due then change to AND.
3. The problem with SELECT * is it selects all columns. If you have 12 columns in the table and only need 4 then you you retrieve 3x more data (approx) and it therefore takes 3x longer to transfer the data from the server. If, in future, someone decides to add,say, a BLOB column to store an image of the insurance document, then your queries suddenly slow down as instead of the 20 bytes you need, you now transfer 50,000 bytes for each record. If you had specified the columns then that wouldn't happen.

SELECT visitor_id
  , visitor_name 
  , visitor_email 
  , DATE_FORMAT(visitor_tax, '%e %M %Y') as taxdate
  , DATE_FORMAT(visitor_mot, '%e %M %Y') as motdate
  , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate 
FROM visitors 
WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
	OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
	OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY

[ianhaney50]

Ahh ok got ya, all makes perfect sense as is pointless having loads of columns that is not needed to show in the email

 

spot on with point 2

 

if ok to copy the coding you provided above but makes sense to have it as being between 2 dates, sorry just learning and understanding it as I go