Scheduled Renewal Notifications using PHP

Hi Folks,
I am a reseller for web hosting and I’d like to know if there’s a way PHP could send out a basic email notification from my email address (So it’s not classified as spam) to notify subscribers of their upcoming renewal dates.
Any help appreciated.

Where do you store the renewal data at the moment then?

I found this PHP script which works a treat!: http://email.about.com/od/emailprogrammingtips/qt/How_to_Send_Email_from_a_PHP_Script.htm

I think this is how my code might look, incorporating the above mail() function:

<?php

//MySql query goes here and results get assigned to the following variables:
           $remindDate = 'MySql query result 1';
           $dueDomain = 'MySql query result 2';
           $clientEmail = 'MySql query result 3'

       if ($remindDate == 'MySql query result 1' and $dueDomain == 'MySql query result 2' and $clientEmail == 'MySql query result 3') //Can you have three 'and' arguments?
            {
                include 'renewalAdvice.html.php'; //contains mail() function with friendly reminder and hosting expiry date.
            }

Would this mean that the database connection would have to remain open full time to account for all the varying hosting expiry dates throughout the year?

Am I in the ball park?

Any help appreciated.

I plan to create a db for this and manually populate it.

I think you have the basic idea.

Keep the dates in a database.

Have a PHP script which :

  • - connects to the database
  • - checks the database for matching data
  • - if it finds matching dates, loop through them sending an email

Generally, the PHP script is evoked once a day using [google]cron job[/google] on a *nix box, or your OS’s equivalent AT command.

This will work fine as long as you do not a lot of emails to send in any given day, else your script may timeout without some intervention.

Generally, the PHP script is evoked once a day using [google]cron job[/google] on a *nix box, or your OS’s equivalent AT command.

This will work fine as long as you do not a lot of emails to send in any given day, else your script may timeout without some intervention.

Cheers Cups!

Great advice.

Wondered for many years what Cron Jobs were.

OK I have advanced a little more on this project!

The attached screenshot shows my table columns:

The goal here is to use a Cron Job which runs a daily script that checks if ‘remindDate’ data matches the current server date and, if true, the condition selects the other related records to the matched ‘remindDate’/s i.e. domain, expireDate, package and ID and then the mail() function sends email reminders to these particular domain owners.

I forgot one thing, I’ll need to add email address to the database.

If anyone can help me with the code to do this I would be very grateful.

Hmmm, it appears the ‘Insert image’ button in the tool bar above doesn’t work (Sorry, no screenshot)

Argent, you will have to post some code explaining where things are going wrong, and indications such as error messages before anyone can help you with this.

<?php
//Make new database connection/////////////////////////////////////////////////////////////////////
try
{
  $pdo = new PDO('mysql:host=localhost;dbname=xxxxxxxxxxxxxdb', 'xxxxxxx', 'xxxxxxxxxxxxx');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
  $error = 'Unable to connect to the database server.';
  include 'error.html.php';
  exit();
}
//Make sql query///////////////////////////////////////////////////////////////////////////////////
try
{
  $sql = 'SELECT domain, remindDate FROM renewals';
  $result = $pdo->query($sql);

}
catch (PDOException $e)
{
  $error = 'Error fetching Reminder Dates: ' . $e->getMessage();
  include 'error.html.php';
  exit();
}

while ($row = $result->fetch())
{
  $reminderDates[] = $row['remindDate'];
}

if ($reminderDates['2012-06-23'] == CURDATE()) //I have one record in db assigned to this date: '2012-06-23' (today's date - used for testing purposes)
{
	$remindNow = $_GET['domain''expireDate']; //line 36: Throws Parse Error, See Parse Error in main reply body.
}


include 'remindNow.html.php';<?php

Error received when running this script:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ‘]’ in /Applications/MAMP/htdocs/email-test/index.php on line 36

Output Template:
<!DOCTYPE html>
<html lang=“en”>
<head>
<meta charset=“utf-8”>
<title>Your Web Hosting Account is due for Renewal</title>
</head>
<body>
<p>Dear $hostingCustomer, your website hosting subscription is due on: $expireDate</p>
<p>
<?php echo htmlspecialchars($remindNow, ENT_QUOTES, ‘UTF-8’); ?>
</p>
<?php endforeach; ?>

</body>
</html>

No, I think you have got totally the wrong idea here. CURDATE() is a Mysql function, not a php function.


 $sql = "SELECT domain, email, remindDate from renewals where remindDate=CURDATE()";

If this brings back anything, it brings back only those domains with a matching date.

In the LAMP stack, you have Mysql do this filtering work.

The error you are getting is because your syntax is illegal and wrong.


$remindNow = $_GET['domain''expireDate'];

You cannot access to GET variables like that.

As I am not sure what they do contain, but if my guess is correct you want to join them together (concatenate them) then you’d do this:


$remindNow = $_GET['domain'] . $_GET['expireDate'];

Just where are those variables coming from?

Thanks for the heads up with the date function being an SQL query.
I can retrieve array data from all columns mentioned in the SQL query:

$sql = “SELECT id, domain, remindDate, expireDate FROM renewals WHERE remindDate=CURDATE()”;

But despite having two identical dates (current date) in the db assigned to two different domains, only one reference is output (the second instance with an ID of 2)

If I use the following code:

while ($row = $result->fetch())
{
  $remindDate = $row['remindDate'];
}

and substitute in turn any of the column names i.e. ‘remindDate’, ‘domain’ etc, I can output their respective values no problem. The output which is now based on current server date matching ‘remindDate’ is big step forward, but I just can’t get all the array data stored in $result to parse.


<?php
//Make new database connection/////////////////////////////////////////////////////////////////////
try
{
  $pdo = new PDO('mysql:host=localhost;dbname=xxxxxxxxxxxxxxdb', 'xxxxxxxxx', 'xxxxxxxxxxxxxxxxx');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
  $error = 'Unable to connect to the database server.';
  include 'error.html.php';
  exit();
}
//Make sql query///////////////////////////////////////////////////////////////////////////////////
try
{

 $sql = "SELECT id, domain, remindDate, expireDate  FROM renewals WHERE remindDate=CURDATE()";
 $result = $pdo->query($sql);

}
catch (PDOException $e)
{
  $error = 'Error fetching Reminder Dates: ' . $e->getMessage();
  include 'error.html.php';
  exit();
}

while ($row = $result->fetch())
{
  $remindDate = $row['remindDate'];
}

include 'remindNow.html.php';

remindNow.html.php


<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Your Web Hosting Account is due for Renewal</title>
  </head>
  <body>
    <p>Dear $hostingCustomer, your website hosting subscription is due on: $expireDate</p>
        <p>
          <?php echo htmlspecialchars($remindDate, ENT_QUOTES, 'UTF-8');
		   ?>
        </p>
  </body>
</html>

Any further help you can offer would be greatly appreciated.

while ($row = $result->fetch[B]All/B) :wink:

Read and print off and stick on a wall near you the basic PDO instructions. (I did, years ago, they are still there)

Instead of having a hard coded remindDate in your database you could be using something like

" WHERE expireDate = DATEADD(CURDATE(), INTERVAL - 1 MONTH)" see here too

Read all about Mysqls Date/time functions get the database doing some more of the work for you, it’ll prove a real eye opener.

It sounds and though about now is the time for you to buy a decent book on Mysql or Sql, I did, years ago and that’s where I found that solution.

There’s no shortcuts you’ll have to get serious and buy books and read them at some point. :wink: