Hi I am building a small app that enables me to store client signup dates for a service. The app will then send me an email reminder one month before the yearly subscription for that client is up for renewal. In order to enter the signup date I am using a Jquery picker to do this manually, and the format is for example October 28, 2010. What MySQL data type should I use and how do I calculate when there is only one month left till October 28, for every year?
I just spent 20 minutes wondering why that didn’t work… we’ve both been missing something obvious!
There’s a mix of $renewaldate and $renewal_date in there 
that’s interesting, I’ve cleaned up my previous code which works fine, and I’ve also made another version with your proposal, but it’s not working, unless I’m missing something, can you give it a look please?
Original code:
<?php
$date_joined = ‘2007/11/05’;
echo “<p>Date joined: $date_joined</p>”;
$current_year = date('Y');
echo "<p>Current year: $current_year</p>";
$comparison_date = substr_replace($date_joined, $current_year, 0, 4);
echo "<p>Comparison date: $comparison_date</p>";
echo 'Current date: '.$current_date = date('Y/m/d');
function daysDifference( $beginDate, $endDate )
{
echo '<p>' . $beginDate = strtotime($beginDate) . '</p>';
echo '<p>' . $endDate = strtotime($endDate) . '</p>';
$diff = $endDate - $beginDate;
$diff = ceil( $diff / (60*60*24) );
echo $diff;
return $diff;
}
if ( daysDifference( $current_date, $comparison_date ) == 30 ) { echo 'Due in 1 month'; }
?>
Incorporating Jay’s proposal:
<?php
$date_joined = ‘2007/11/05’;
echo “<p>Date joined: $date_joined</p>”;
$current_year = date('Y');
echo "<p>Current year: $current_year</p>";
$renewal_date = substr_replace($date_joined, $current_year, 0, 4);
echo "<p>Renewal date: $renewal_date</p>";
echo 'Current date: '.$current_date = date('Y/m/d');
if($renewaldate == date("Y/m/d", strtotime("+1 month")))
{
echo "one month until renewaldate!";
}
?>
At the moment I managed to come up with this code for detecting whether there is one month to go till the next renewal, is it ok or can it be improved further?
<?php
//echo $timestamp = strtotime("Friday");
//echo date('d/m/Y', $timestamp);
$date_joined = '2007/11/01';
echo $date_joined;
echo '<br />';
$current_year = date('Y');
echo $comparison_date = substr_replace($date_joined, $current_year, 0, 4);
echo '<br />';
echo $current_date = date('Y/m/d');
function daysDifference($beginDate, $endDate)
{
echo $beginDate = strtotime($beginDate) . '<br />';
echo $endDate = strtotime($endDate);
$diff = $endDate - $beginDate;
$diff = ceil($diff / (60*60*24)) ;
return $diff;
}
echo '<br />'. daysDifference($current_date, $comparison_date);
if ( daysDifference($current_date, $comparison_date) == 30 ) { echo 'Due in 1 month'; }
?>
First you’d take the date input and change it into a timestamp using strtotime and insert that into the database.
Then you’d have a query to select all the records where the difference between the current time and the signup date is >= to 11 months (ie 1 month left) to get those up for renewal.
I think you should try the snippet below
Just populate the first two variables from your database.
Remember to set a flag in the database to indicate that the user (or you) received a notification to avoid multiple renewal notifications, reset it on renewal.
/********** set some variables ***********/
// I assume you'll get these from a database
$date_joined = "2010-12-09 12:47";
$Reminder = '30';
$renewalDate = strtotime($date_joined); // convert date to timestamp
$Reminder = $Reminder * 8640; // convert reminder to seconds (i.e 259200 seconds in 30 days)
$today = time(); // Today is today
/**************************************************/
$difference = $renewalDate - $today;
if($difference < $Reminder){
// do reminder action here
}else{
echo "Expiration date will be ".$date_joined;
}
I hope it helps
You’re gonna kick yourself when you realise how much you’ve overcomplicated this!
$renewaldate = "October 28";
if($renewaldate == date("F j", strtotime("+1 month")))
{
echo "one month until renewaldate!";
}
and “F j” should be “F d” if the numeric part of the date will have leading zeros! Of course, you can just choose whatever date format suits how you’re storing it.
This is what I ended up using, if anyone wants to suggest any optimisations you are very welcome 
<?php
include_once "ez_sql/ez_sql.php";
// Function to calculate difference between current date and renewal date
function daysDifference( $beginDate, $endDate )
{
$beginDate = strtotime($beginDate);
$endDate = strtotime($endDate);
$diff = $endDate - $beginDate;
$diff = ceil( $diff / (60*60*24) );
return $diff;
}
// Get application settings
$settings = $db->get_row("SELECT admin_email, reminder1, reminder2 FROM settings");
$email = $settings->admin_email;
$reminder1 = $settings->reminder1;
$reminder2 = $settings->reminder2;
// Get all client info
$clients = $db->get_results("SELECT business_name, name, surname, comments, email, date_joined FROM clients WHERE active = 1");
foreach ( $clients as $client ) {
$date_joined = $client->date_joined;
/*echo "<p>Date joined: $date_joined</p>";*/
$current_year = date('Y');
/*echo "<p>Current year: $current_year</p>";*/
$comparison_date = substr_replace($date_joined, $current_year, 0, 4);
/*echo "<p>Comparison date: $comparison_date</p>";*/
/*echo 'Current date: '.*/$current_date = date('Y/m/d');
if ( daysDifference( $current_date, $comparison_date ) == $reminder1 ) { $renewal_msg = 1; }
else if ( daysDifference( $current_date, $comparison_date ) == $reminder2 ) { $renewal_msg = 2; }
if ( isset($renewal_msg) ) {
$message = "$client->business_name hosting account is up for renewal in " . ( $renewal_msg == 1 ? $reminder1 : $reminder2 ) . " days.";
/*echo $message;*/
// Send out Reminder mail
$mailheader = "From: Hosting Manager <billing@myhost.com>\
X-Mailer: Reminder\
Content-Type: text/plain";
mail($email, "Reminder", $message, $mailheader);
}
}
?>
I think I’ll be going with my original code as it enables me to set up an options panel to let the user choose the number of days for the reminder, and not tie it to one month specifically. I am still unsure about the bug mentioned in the previous post though.
True. Just create a ‘renewal’ column and increment that by 1 year each time they renew.
yes but then what if they signed up in 2007 that won’t work as it will be more than 11 months.