Send a reminder a month prior to a date

[cr125jb]

Quick overview, I work for a Real Estate Company.  I created a PHP website using PHP Runner to organize all of our current listings.  This site is where my agents login and add new listings.  Upon adding a new record, e-mails are sent to Marketing and Administration to begin their marketing prcoess to create flyers and add the listings to websties.

 

So what I'm trying to figure out is how to send a reminder e-mail 1 month before the listing is expired.  I have a field the agents fill out called "LIS_DATE_EXP" or expiration date.  My schema is very basic.  I have a V_LIS table that is for the listing information.  I have an V_EMP table that contains the data of the users that will access the site.  Name, E-mail, Password, etc.

 

I need to create a php file that will call the LIS_DATE_EXP of each record of the V_LIS table, check to see if it's 1 month before that date.  IF it is, then send an e-mail to the agent using the e-mail in the V_EMP table and send an e-mail to me so I can follow up.  I'm not sure what the best way to go about this is.  I could create a view/join table that contains only a few fields specifically for this function but I don't know.

 

I've read that I need to create a cron job that will run a script every day, but I'm a novice at web coding and I couldn't get what I needed accomplished.

 

I'm a network admin and I'm trying to move away from microsoft and join the open source world.  I'm currently setting up Ubuntu servers to house websites and a mapping system we use for our company.  This web stuff is a new experience for me =]  Thanks for the help in advance

[fastsol]

A cron job is the way to go on this.  You can google it for the type of server configuration you have and find out how to set it up.  Then in the php file you have the cron run, just use some simple queries to gather the in info and send out mail() in the format you want.  You would use a WHERE clause in the query to only gather the records that are within the expire time frame you want.  How the WHERE clause will look depends on the how you are storing the expire date in the db and what format it is.

[Muddy_Funster]

use a query with DATE_SUB() to get the relivant emails, and then build an array with what comes back, implode() the array into a list of addresses, set your address as the to address and BCC the list of other relevant people.  You can then send yourself a second message if you need to that contains the information about who else got the message.

[priyankagound]

set up a cron job to run a php script that will fetch entries in this way e.g.

/* send those 2 months before exp date*/

$sql='select * FROM users WHERE exp_date_unix>='.(time()+86400*60);

or if you use the datetime/timestamp field

the condition would be smth like

DATE_DIFF(exp_date_unix, CURRENT_TIMESTAMP) >=60;

[Barand]

Why "60" when the requirement is for 1 month?

[vinny42]

In SQL, and even in MySQL, you can add and substract intervals from dates and timestamps, so you don't have to mess about with date_sub, date_add or unix_timestamp.

 

The trick is that you don't want to send the email only when the expiration is exactly one month away, because a month can be 28-31 days so moving along 31 days from febraury 1st will move you straight to march 3rd, possibly skipping the 1st and 2nd.

 

Instead, send a message for all records that will expire in 30 days, and for which no message has been sent yet. That way you will also send a message to records thaat will expire in 29 days but did not get a message for some reason. You should ofcourse probably limit it to a minimum of ten days from now, telling someone that his record is about to expire tomorrow is not very usefull.

 

You can select those records using:

SELECT *

FROM table

WHERE date_field BETWEEN CURRENT_TIMESTAMP + INTERVAL 10 DAYS AND CURRENT_TIMESTAMP + INTERVAL 1 MONTH;

[jcbones]

All of this is, of course, based on the notion that your database tables are set up so that the date is held in a proper `date`, `datetime`, or `timestamp` field.

[cr125jb]

My database is setup in 'date' format (YYYY-MM-DD).  I'm working on the query but I'm having a little trouble understanding grabbing information from two tables in the same databases.  For instances I have my table that houses listing information which is where the expiration date is.  I then have another table where my agent information is which houses the agents e-mail addresses.  How do I write a script/query to access some information from one table, and some information from another?  Then send an e-mail using the e-mail address in the agent table?

 

I was considering just create a view table that houses only the information I want in one place, but I would like to know how to get information from two tables in one query.

Edited September 18, 2013 by cr125jb

[vinny42]

There are several ways to do this, of course.

 

To keep it simple and understandable, you could do something like:

 

SELECT agents.*, houses.*

FROM agents INNER JOIN houses ON agents.agent_id = houses.agent_id

WHERE houses.expiration BETWEEN foo AND bar

ORDER BY agents.id;

 

This will return one record per house, containing all the data for the house and the agent.

You can loop through these records and send emails directly, or, to be more polite to the agents, add the houses for each agent to an array for that agent,

and send only one email notifying the agent of all houses about to expire.

[Barand]

or you could GROUP BY agent and GROUP_CONCAT the house details into a single field ready for emailing. Let the database query do the work.

Edited September 18, 2013 by Barand

[vinny42]

 

or you could GROUP BY agent and GROUP_CONCAT the house details into a single field ready for emailing. Let the database query do the work.

 

I'm all for letting the database do the work but in this case the work you do is making the PHP part much more complex.

 

Using group_conact would mean that the house data would come to PHP as a CSV string, possibly, even an nested CSV string and that requires that you have to mess with explode() in order to get the dat back out. Normal group_concat only appends the values of the fields so you must have a bizarly strict rule about editing queries so the order of the filed cannot change, ever, or you'd have to modify the values at selection so they also contain the fieldnames. That would be the only way that PHP can know which data is in which CSV column.

 

It is simpler, cleaner and more reliable to build the arrays in PHP; all field references remain in tact no matter what the query does and if the query is modified to remove a field, the routine will fail with a clear error message. The extra workload of looping through the array doesn't compare to the bugs you could introduce using group_concat.

 

And just to p*ss you off I'm going to say that in PostgreSQL you could use it's JSON features to convert the house records to JSON and aggregate them around the agent to give one single JSON object, which you could decode in PHP to get an array indexed by agent, containing a subarray of houses, with fieldnames and values. Best of both worlds.

[cr125jb]

Thanks for all of the replies.  I'm almost there and just need a little help.  Here's what I have so far...

 

My Crontab file has:

0 0 * * * username /path/email.php

 

My PHP file contains:

<?php
// database connection mysqli_connect is the mysql command then followed by ('IP', 'user', 'pass', 'database')
$dbc = mysqli_connect('IP', 'user', 'pass', 'database')
or die('Error connecting to MySQL server.');
// MySQL code to grab the fields I need and only grab the dates that are 30 days from expiration.  Tested in navicat and this code does exactly what is expected
$sql = 'SELECT TABLE1.EMP_EMAIL, TABLE1.EMP_FULLNAME, TABLE2.LIS_DATE_EXP, TABLE2.CL_FNAME, TABLE2.CL_LNAME, TABLE2.LIS_NAME FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.EMP_FULLNAME = TABLE2.EMP_ID WHERE LIS_DATE_EXP = DATE_ADD(CURDATE(),INTERVAL 30 DAY)';
$result = mysqli_query ($dbc, $sql);
// Define the variables and columns (loop the array)
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    $email = $row["TABLE1.EMP_EMAIL"];
    $fullname = $row["TABLE1.EMP_FULLNAME"];
    $name = $row["TABLE2.LIS_NAME"];
    $date_exp = $row["TABLE2.LIS_DATE_EXP"];
    $cl_fname = $row["TABLE2.CL_FNAME"];
    $cl_lname = $row["TABLE2.CL_LNAME"];
// Mail code to e-mail based on the records pulled from above code.  Hoping that the syntax is correct.
$to = "email@yahoo.com, " . $email . " ";
$subject = "Listing " . $name . " is about to expire!";
$message = "The Listing " . $name . " is going to expire on " . $date_exp . ".  Please contact " . $cl_fname . " " . $cl_lname . " and extend the listing agreement.";
$from = "email@yahoo.com";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
}
echo "Mail Sent.";
?>

 

The problem with my syntax is that I get an e-mail for each listing that falls in the 30 days away category, but I'm not getting the field information.  So I don't get any of the information from the database.  For instance below is the e-mail I get. 

 

The Listing  is going to expire on .  Please contact   and extend the listing agreement.

[Barand]

 $email = $row["TABLE1.EMP_EMAIL"];

Remove the table name and just use the column name.

 $email = $row["EMP_EMAIL"];

Same goes for the other fields

[cr125jb]

Thanks Barand!  And thanks everyone for helping me with this script.  Works like a charm!

[cr125jb]

for my cron job, i had to put /usr/bin/php

0 0 * * * /usr/bin/php /path/email.php