Php email reminder script

Hi

Can anyone help me please, I need to put together a php email reminder script that pings out a email for domain name expiry dates that are in 7 days and it also updates a date notified column in mysql db so the admin knows a email has been sent on the date notified date, hope that makes sense

Thank you in advance

Hi there @ianhaney1 welcome to the forums. How far have you got with this? What PHP experience do you have?

Hi @Gandalf

Thank you for the welcome and the reply, I do have some PHP knowledge, I did put the following script together but is pretty rubbish and don’t work, a couple of issues with it are below

  1. it don’t seem to loop through the expiry dates and notify me of each date coming up that is due to expire
  2. it don’t update just the records that the email has been sent for, it updates all the records which is no good

below is the coding I put together

<?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("localhost" , "", "") 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 {
	/*SUCCESS MSG*/
	echo '';
}

$sqlCommand = "SELECT 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
        ";

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

$current_visitor=0;
$current_email = '';
$headers = "From: noreply@domain.co.uk\r\n";    
$subject = "Domain Name Expiry Date(s)";
$message = '';

//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
	
	// has visitor_id changed
	if ($row['id'] != $current_visitor) {
		// send email to current visitor
		if ($current_visitor != 0) {
			$to = $current_email;
			$sendmail = mail($to, $subject, $message, $headers);
			if ($sendmail) {
				echo nl2br($message);
			    echo "<b>Email Successfully Sent</b><br><br>";
			} else { 
			    echo "<b>Error in Sending of Email to $to</b><br><br>";
			}
		}
		$current_visitor = $row['id'];
		$current_email = $row['domain_owner_email'];
		$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";
		
	}
	$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";

}
// send email to final visitor
if ($current_visitor != 0) {
	$to = $current_email;
	$sendmail = mail($to, $subject, $message, $headers);
	if ($sendmail) {
		echo nl2br($message);
		
		echo "<b>Email Successfully Sent</b><br><br>";
	} else { 
		echo "<b>Error in Sending of Email to $to</b><br><br>";
	}
	
	$id = 'id';
	
	$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id = $id";	
	print $sql;
	
}

//$db->query($sql);
$db->query($sql) or die(mysqli_error($db));

// Free the results  
mysqli_free_result($query);

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

</body>
</html>

I think a cron job script may be suitable for this, in which case it gets run by the server, not a user, so does not need to output a html page, just get the data from the db and send the mail. Though the mail may be html.

3 Likes

That’s the plan is to have a cron job script but need to get the script working first

Hi, I have a update, I have been playing around with the script and have now managed to get it to send the email and update just the records that have dates that are going to expire in 7 days but it don’t seem to be looping through to check all dates and include multiple dates in the email, can someone check my coding please as my email only includes one date where as it should include two dates

my code is below

<?php

$db = mysqli_connect("localhost" , "", "") 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 {
	/*SUCCESS MSG*/
	echo '';
}

$sqlCommand = "SELECT 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
        ";

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

$current_visitor=0;
$current_email = '';
$headers = "From: noreply@domain.co.uk\r\n";    
$subject = "Domain Name Expiry Date(s)";
$message = '';

//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
	
	$arr_ids[] = $row['id'];
	
	// has visitor_id changed
	if ($row['id'] != $current_visitor) {
		// send email to current visitor
		if ($current_visitor != 0) {
			$to = $current_email;
			$sendmail = mail($to, $subject, $message, $headers);
			if ($sendmail) {
				echo nl2br($message);
			    echo "<b>Email Successfully Sent</b><br><br>";
			} else { 
			    echo "<b>Error in Sending of Email to $to</b><br><br>";
			}
		}
		$current_visitor = $row['id'];
		$current_email = $row['domain_owner_email'];
		$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";
		
	}
	$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";

}
// send email to final visitor
if ($current_visitor != 0) {
	$to = $current_email;
	$sendmail = mail($to, $subject, $message, $headers);
	if ($sendmail) {
		echo nl2br($message);
		
		echo "<b>Email Successfully Sent</b><br><br>";
	} else { 
		echo "<b>Error in Sending of Email to $to</b><br><br>";
	}
	
	if (isset($arr_ids)){
$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id IN (";
$sql .= implode("," , $arr_ids);
$sql .= ");";
	print $sql;
	
}

//$db->query($sql);
$db->query($sql) or die(mysqli_error($db));

// Free the results  
mysqli_free_result($query);

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

I’m not clear who or what $current_visitor is. Is it simply a counter or does it somehow relate to website visitors?

To be honest, it is not related to the website visitors and I forgot to take it out as I got it from another php script I put together

I will take it out now and hopefully work still and post a update here in abit

I removed the $current_visitor bits as didn’t need it and luckily the script still works expect the email is still not showing all the dates that are going to expire in 7 days, it is only showing the one record, below is the code I have now and the email I get is under the code

<?php

$db = mysqli_connect("localhost" , "", "") 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 {
	/*SUCCESS MSG*/
	echo '';
}

$sqlCommand = "SELECT 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
        ";

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

$current_email = '';
$headers = "From: noreply@domain.co.uk\r\n";    
$subject = "Domain Name Expiry Date(s)";
$message = '';

//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
	
	$arr_ids[] = $row['id'];
		
			$to = $current_email;
			$sendmail = mail($to, $subject, $message, $headers);
			if ($sendmail) {
				echo nl2br($message);
			    echo "<b>Email Successfully Sent</b><br><br>";
			} else { 
			    echo "<b>Error in Sending of Email to $to</b><br><br>";
			}
		
		$id = $row['id'];
		$current_email = $row['domain_owner_email'];
		$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";
		
	
	$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";

}
// send email to final visitor

	$to = $current_email;
	$sendmail = mail($to, $subject, $message, $headers);
	if ($sendmail) {
		echo nl2br($message);
		
		echo "<b>Email Successfully Sent</b><br><br>";
	} else { 
		echo "<b>Error in Sending of Email to $to</b><br><br>";
	}
	
	if (isset($arr_ids)){
$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id IN (";
$sql .= implode("," , $arr_ids);
$sql .= ");";
	print $sql;
	
}

//$db->query($sql);
$db->query($sql) or die(mysqli_error($db));

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);

?>

EMAIL I RECEIVE
Domain Name Owner: Ian Haney

Your Domain Name www.example.co.uk expiry date is: 24 October 2016

I think the problem is an inside vs. outside the loop thing. eg.

$var = "";
loop{
$var = "something";
}
$var = "has the value of whatever was last assigned to it within the loop";

So depending on what you want to do, you could move code to within the loop, or you could assign the values to an array within the loop and then loop through that array outside of the loop…

2 Likes

There’s a logic issue inside the loop. The first thing you do is try to send an email, even though on the first pass the variables for the email are empty. That method has some merit if you want to group multiple domain notifications to the same owner and only send one email per owner, but the query needs to change to ORDER BY the owner. Then you can build up the list of domains expiring and send the email when the owner changes to a new one.

If you’re not going to do that, the bit after the loop isn’t going to be needed, you can just send one email for each row that comes from the query.

How many emails should you be getting, i.e. what is the test data set?

Thank you for the replies

the email notifications will only be one email sent to the same owner

within the email, I need it to be like the following

Domain Name Owner: Ian Haney

Your Domain Name www.example.co.uk expiry date is: 24 October 2016
Your Domain Name www.example2.co.uk expiry date is: 25 October 2016

and so on

I altered the coding to the following but still only getting one date within the email where as all the dates that are going to expire will show in the one email

<?php

$db = mysqli_connect("localhost" , "", "") 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 {
	/*SUCCESS MSG*/
	echo '';
}

$sqlCommand = "SELECT 
        u.id
        , domain_name_owner
        , url
        , DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiry_date
        , domain_owner_email
        FROM websites u
        WHERE domain_expiry_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
        ";

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

//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
	
	$arr_ids[] = $row['id'];
		
$email = '';
$headers = "From: noreply@domain.co.uk\r\n";    
$subject = "Domain Name Expiry Date(s)";
$message = '';
		
		$id = $row['id'];
		$email = $row['domain_owner_email'];
		$message = "Domain Name Owner: {$row['domain_name_owner']} \n\n";
		
	
	$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiry_date']}\n";
		
			$to = $email;
			$sendmail = mail($to, $subject, $message, $headers);
			if ($sendmail) {
				echo nl2br($message);
			    echo "<b>Email Successfully Sent</b><br><br>";
			} else { 
			    echo "<b>Error in Sending of Email to $to</b><br><br>";
			}

}

	if (isset($arr_ids)){
$sql = "UPDATE websites SET date_notified_of_domain_expiry = NOW() WHERE id IN (";
$sql .= implode("," , $arr_ids);
$sql .= ");";
	print $sql;
	
}

//$db->query($sql);
$db->query($sql) or die(mysqli_error($db));

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);

?>

Maybe there’s a problem with the alias for the formatted domain_expiry_date field being the same name as the field?

Try

DATE_FORMAT(domain_expiry_date, '%e %M %Y') as domain_expiration_date``
.....
$message .= "Your Domain Name {$row['url']} expiry date is: {$row['domain_expiration_date']}\n";

I tried that coding and didn’t work unfortunatly

I’m not sure I understand that - you say that you’re only getting one date within the email, but then you say “all dates that are going to expire will show in the one email” - or is that a typo and you mean “all dates that are going to expire should show in the one email”? But isn’t getting one date within the email an improvement on the first problem, which was that you only receive one email? I am a bit confused.

I am still confused by the statement that you want all domains for each owner to be listed in the same email, but I don’t see any code that would do anything like that, either in the query or in the PHP code. Or is the query just returning the domains for a single owner?

In any case, if I presume that the query will return all domains for all owners that are expiring within the desired timeframe, I think you need to add an ORDER BY clause in the query to sort the results by owner name. Then your code would be like this pseudo-code:

last-owner-id = "";
execute the query
for each result {
  if result-owner-id <> last-owner-id {
    if last-owner-id is not blank {
      send the email
      }
    clear the list of domains
    set the email headers
    }
  add the result-domain details to the list of domains
  last-owner-id = result-owner-id
  }
if the list of domains isn't blank, send the email

That’s a bit closer in structure to what you had before, but you basically build up an array of domain names while the domain owner doesn’t change, and send the email (and clear the domain name array) as soon as it does. That’s why the results need to be sorted by domain owner.

sorry lost me a bit with the coding

I need one email sent that contains all the domains that are going to expire within 7 days but all I am getting is one domain in the email and not all of them

sorry hope that makes more sense, sorry

That wasn’t coding, that was just pseudo-code to show what steps need to happen and in what order. I was thinking you’d translate that into actual PHP. I think I understood what you want to do, but there’s no code there to actually achieve the grouping side of it. That’s what I was trying to show in the pseudo-code.

oh right ok got it

Hang on a second, do you want a single email going to a single recipient listing all the domains that are expiring within seven days, or do you want a single email per domain owner going to each domain owner showing each of that owners domains expiring within seven days? The latter is what I thought you meant, if it’s the former then it’s much easier.

sorry yeah that’s it, it will be a single email going to a single recipient listing all the domains that are going to expire within seven days