SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Where do you store the renewal data at the moment then?

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found this PHP script which works a treat!: http://email.about.com/od/emailprogr...PHP_Script.htm

    I think this is how my code might look, incorporating the above mail() function:
    PHP Code:
    <?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.

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I plan to create a db for this and manually populate it.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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 cron job 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.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Generally, the PHP script is evoked once a day using cron job 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.

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Outputting domain and expire date, based on CURDATE() match

    PHP Code:
    <?php
    //Make new database connection/////////////////////////////////////////////////////////////////////
    try
    {
      
    $pdo = new PDO('mysql:host=localhost;dbname=xxxxxxxxxxxxxdb''xxxxxxx''xxxxxxxxxxxxx');
      
    $pdo->setAttribute(PDO::ATTR_ERRMODEPDO::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>

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    No, I think you have got totally the wrong idea here. CURDATE() is a Mysql function, not a php function.
    PHP Code:
     $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.
    PHP Code:
    $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:
    PHP Code:
    $remindNow $_GET['domain'] . $_GET['expireDate']; 
    Just where are those variables coming from?

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Perth W.A.
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP 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 Code:
    <?php
    //Make new database connection/////////////////////////////////////////////////////////////////////
    try
    {
      
    $pdo = new PDO('mysql:host=localhost;dbname=xxxxxxxxxxxxxxdb''xxxxxxxxx''xxxxxxxxxxxxxxxxx');
      
    $pdo->setAttribute(PDO::ATTR_ERRMODEPDO::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

    PHP Code:
    <!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($remindDateENT_QUOTES'UTF-8');
               
    ?>
            </p>
      </body>
    </html>
    Any further help you can offer would be greatly appreciated.

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    while ($row = $result->fetchAll())

    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •