Jump to content

Automatically Send Follow Up Email?


Kristoff1875

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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?

Link to comment
Share on other sites

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

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

?>

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.