Automatically Send Follow Up Email?

[Kristoff1875]

Hi, I currently have a page in an admin section, with members details, and 2 buttons. When you set a value in a field, then click the first button, it navigates to a php page which sends a HTML email to the member's email address with the value from the field and then directs back to the previous page. The second button when clicked, does the same, but with a different email. This is a follow up email, a reminder, to the first.

 

My dilemma is, how would I get the follow up email to send automatically 7 days after the first email is sent? Is there a way?

 

Many thanks in advance!

[Jessica]

Using Cron jobs.

[JLT]

Well, when you send the first email, you can log this in the database. You could also have a daily Cron Job that runs at 12:01am, which would check all the emails sent and if they are 7 days ago, send the follow up.

 

I'm not sure if there is any other automated way to do this... but hope what I've suggested gives you some tips.

[Kristoff1875]

I send the NOW time and date to the database when the first email is sent. I've used Cron Jobs before but never set something up in a script that i've done myself.

 

If I set up a cron job for it, would I just tell it to run the page that sends the email for each member that it needs to?

 

Thanks for the advice, I thought it'd be cron jobs, but never used it on my own script!

Edited September 27, 2012 by Kristoff1875

[maxudaskin]

I would create a cron job running once a day, calling a php script.

 

The php script would get the rows from the table from x days ago and would loop through and send a follow up email.

 

Have each email template in it's own file.

 

Let's say, initial_email.php and followup_email.php.

 

<?php
/**
* initial_email.php
*/
$message = <<<EOD
Hi {$name},
Thank you for submitting your information.
You will receive an email in about seven days.
---
This is an automated email. Replies will not be received.
EOD;

<?php
/**
* followup_email.php
*/
$message = <<<EOD
Hi {$name},
About a week ago, you entered your information into our system.
This is the follow up email.
---
This is an automated email. Replies will not be received.
EOD;

[Kristoff1875]

Awesome thanks. I pretty much have the emails in their own files, just that there is the code telling the email to send etc. I'll look in to cron jobs tomorrow. Can I still use a button on the admin side to send the initial email?

[Kristoff1875]

Hi i'm having a go at this, but getting no output from the emails. Currently including the email file in to the file that processes the request that will be called in the CRON JOB, but I think there must be a better way of doing what i'm doing.

 

Is there a way to say foreach row, send the email? Must be a better way of doing what i'm doing as it seems so long way round!

 

See below for what i'm currently doing:

 

<?
// Connects to your Database
mysql_connect("localhost", "****", "****") or die(mysql_error());
mysql_select_db("****") or die(mysql_error());
$data = mysql_query("SELECT * FROM completed WHERE followupsent='0000-00-00 00:00:00' AND valuesent = DATE_SUB(NOW(), INTERVAL 7 DAY)")
or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{
include 'followupemail.php?id='.$data['id'].'';
}
?>

 

followupsent is by default 0000-00-00 00:00:00 so i'm checking to make sure the follow up has not been sent yet, and then checking valuesent to see if it's 7 days since the first email was sent.

[Kristoff1875]

I'm an idiot, just spotted info/data doesn't match up!

 

Edit: Right, I think the code i'm using for week old dates is wrong. Going to look for the correct way.

Edited October 1, 2012 by Kristoff1875

[Kristoff1875]

Right, got the php to run correctly and find the requested rows that are older than a week and only if the initial email has already been sent. Tested in browser it works properly and sends the email.

 

Tried to put it in to cron jobs in cpanel using the following:

 

php -q /home/***/followup.php

 

where *** = full path

 

but i'm getting an email coming through saying "No input file specified." Any ideas? It's the correct file name and i'm fairly sure the directory is correct as I was getting "Not a directory" before?

[Kristoff1875]

Out of ideas regarding the cron jobs thing so any help appreciated. The closest I think i've got is:

 

PHP: Error parsing /home/****/followup.php on line 4

 

Which I guess was reading the file but not properly? Line 4 is:

 

mysql_connect("localhost", "***", "***") or die(mysql_error());

[Christian F.]

Post the entire error message, as it'll tell you (us) exactly what's wrong.

[Kristoff1875]

That was the entire error message. I've spoken to my hosting who have given me a new line, which isn't emailing any error messages, but is not running the script as it does when I open the page manually. Stumped!!

[Christian F.]

Hmm... That's odd. Normally it would tell you exactly why it failed to parse the file, like this:

Parse error: syntax error, unexpected T_ECHO in /media/raid/Dropbox/htdocs/getuser.php on line 4

 

Turn on error reporting for your PHP CLI executable, and then run the code manually. Also, without the code we cannot even start to tell you where the problem is. Quite similar to showing up at the mechanic, without bringing your car.

[Kristoff1875]

As shown above, i'm using this to call the page with the email:

 

<?
// Connects to your Database
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());
$data = mysql_query("SELECT * FROM completed WHERE followupsent='0000-00-00 00:00:00' AND DATE(valuesent) < DATE_SUB( NOW(), INTERVAL 7 DAY )")
or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{
$id = $info['id'];
header ("Location: followupemailauto.php?id=$id");
}
?>

 

Then on "followupemailauto.php" :

 

<?

$id = $_GET['id'];
$pagefrom = $_SERVER['HTTP_REFERER'];
// . (!empty($info['contactother']) ?

// Connects to your Database
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());
$data = mysql_query("SELECT * FROM completed WHERE id='$id'")
or die(mysql_error());


if($info = mysql_fetch_array( $data ))
if ($info['valuation'] != ""){
$body='***email content***';

$subject = 'Subject';
$message = $body;

$headers = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: **** <noreply@****.com>' . "\r\n";
mail( $info['email'],$subject,$message,$headers );
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());
$update = mysql_query("UPDATE completed SET followupsent = NOW() WHERE id = '$id'") or die(mysql_error());

}
?>

 

Works perfect, no errors when you go to the page that is being called in the cron job, but the cron job doesn't seem to be processing it. As I said before, i'm new to using Cron Jobs, could it be because i'm setting a header where the actual process takes place?

Edited October 5, 2012 by Kristoff1875

[Christian F.]

Try using the full PHP tags, instead of short tags.

[Kristoff1875]

Still nothing, not sure why. No error either.

[berridgeab]

The problem is that you are trying to redirect to your next script using a header, this wont work for the cronjob, because its running from the commandline not a url.

 

You need to combine the script into one script or include the second script with an include / require statement.

 

I would really combine the two scripts I see no reason why they should be split into two.

[Kristoff1875]

So it's as I guessed at earlier. it's because of the header? Many thanks berridgeab, will give it a try.

[berridgeab]

You solved it yourself, I just confirmed it

 

Also something to bare in mind is that some $_SERVER variables are simply not available from the commandline, I forget which ones, but a print_r($_SERVER) will tell you.

[Kristoff1875]

Going to give it a go now. Cheers

[Kristoff1875]

Right, getting there, but another problem with the 7 day code:

 

$data = mysql_query("
SELECT *
FROM completed
WHERE
id='724682'
AND followupsent='0000-00-00 00:00:00'
AND DATE(valuesent) < DATE_SUB( NOW(), INTERVAL 7 DAY
")

 

Throwing up:

 

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 '' at line 6

 

Removing the date line and using:

 

 

$data = mysql_query("
SELECT *
FROM completed
WHERE
id='724682'
AND followupsent='0000-00-00 00:00:00'
")

 

Works perfectly, but obviously doesn't check for items a week old...

 

The ID field is going to be removed, but I need it now so it only sends to the test account and not all of the other members.

Edited October 6, 2012 by Kristoff1875

[Kristoff1875]

Have used:

 

AND valuesent + INTERVAL 7 DAY <= NOW()

 

And it's working I think!

[Kristoff1875]

Right the page is all working now, but I have one more question about this.

 

The code at the moment is like this:

 

<?php
mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());
$data = mysql_query("
SELECT *
FROM completed
WHERE
followupsent='0000-00-00 00:00:00'
AND valuesent + INTERVAL 7 DAY <= NOW()
")
or die(mysql_error());


if($info = mysql_fetch_array( $data ))
$id=$info['id'];
if ($info['valuation'] != ""){

$body='email body';

$subject = '***';
$message = $body;

$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: *** <noreply@***.com>' . "\r\n";
mail( $info['email'],$subject,$message,$headers );

echo $info['email'].' done.';

}

mysql_connect("localhost", "***", "***") or die(mysql_error());
mysql_select_db("***") or die(mysql_error());
$update = mysql_query("UPDATE completed SET followupsent = NOW() WHERE id = '$id'") or die(mysql_error());
?>

 

Will that loop through all the applicable fields and send the email?

[berridgeab]

Yes but the way your updating is wrong.

 

When it goes through the loop $id will get overwritten everytime. When you come to update it, it will only update the last id chucked out from the loop, not all the records.

 

Also your connecting twice to the database, no need to do that your already connected.

 

You have two options, move the update within the loop (but this will mean many update querys which can be slow if you have 200+ updates) or you could save all the ids into an array and update them in one database call (more efficient).

 

Something like

 

if($info = mysql_fetch_array( $data ))
$id=$info['id'];
$arrayIds[] = $info['id'];
}
$updateQuery = "UPDATE completed SET followupsent = NOW() WHERE id IN (";
foreach($arrayIds as $id)
{
$updateQuery .= "$id, ";
}
$updateQuery = rtrim($updateQuery, ", ") . ")";
$update = mysql_query($updateQuery) or die(mysql_error());

 

Basically use a PHP loop to build up the MySQL statement.

Edited October 6, 2012 by berridgeab

[Kristoff1875]

Have I added this in the wrong place?

 

$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: *** <noreply@***.com>' . "\r\n";
mail( $info['email'],$subject,$message,$headers );

echo $info['email'].' done.';

if($info = mysql_fetch_array( $data ))
$id=$info['id'];
$arrayIds[] = $info['id'];
}
$updateQuery = "UPDATE completed SET followupsent = NOW() WHERE id IN (";
foreach($arrayIds as $id)
{
$updateQuery .= "$id, ";
}
$updateQuery = rtrim($updateQuery, ", ") . ")";
$update = mysql_query($updateQuery) or die(mysql_error());

?>