The CMSB Cookbook

MORE EXPIRATION DATE CODING OPTIONS - Oct 6th, 2016


Automatically updating an annual membership can get complicated because some organizations still prefer to use a
specific renewal date, and others allow the actual date that a member has signed up as the base date and annually update
from there.

The situation becomes more complex when a member has skipped a year and then decides to renew.

Here’s some really simple MySQL update code as well as some more sophisticated coding examples thanks to Dave Edis,
Senior developer at interactive Tools.

Example 1
This will simply add one year to the current expiration date:

<?php
mysqlStrictMode(false);
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = expiresDate + INTERVAL 1 YEAR
WHERE num = '".mysql_escape( $CURRENT_USER['num'] )."'")
or die("MySQL Error:\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

?>

Example 2
This will simply update the expiration date to one year from the actual date the membership is renewed:

<?php
mysqlStrictMode(false);
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = (NOW() + INTERVAL 1 YEAR)
WHERE num = '".mysql_escape( $CURRENT_USER['num'] )."'")
or die("MySQL Error:\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

?>

Example 3 (thanks to Dave Edis)
This will update a member’s expiration date to 1 year from the current expiration date.
But, if the expiration date does not contain the previous year, then the new expiration date will
be the current year +1 (so that the record for a member who has skipped a renewal year does not
get updated to last year).

<?php
mysqlStrictMode(false);

$lastYear = date('Y', strtotime("-1 year"));
$expiryYear = date('Y', strtotime($CURRENT_USER['expiresDate']));
$currentYear = date('Y');
if ($expiryYear != $lastYear) { $newExpiryYear = $currentYear + 1; }
else { $newExpiryYear = $expiryYear + 1; }

// Update expiry to greater of: 1 year from now, 1 year from old expires date
$updateSet = "expiresDate=DATE_FORMAT(expiresDate,'$newExpiryYear-%m-%d %T')";
$updateWhere = "num = '".mysql_escape( $CURRENT_USER['num'] )."'";
$updateQuery = "UPDATE `{$TABLE_PREFIX}accounts` SET $updateSet WHERE $updateWhere";
mysql_query($updateQuery) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
?>

Example 4 (thanks to Dave Edis)
This will update the expiration date to the greater of: 1 year from now, or 1 year from the old expiration date:

<?php
mysqlStrictMode(false);

$updateSet = "expiresDate = GREATEST(NOW(), expiresDate) + INTERVAL 1 YEAR";
$updateWhere = "num = '".mysql_escape( $CURRENT_USER['num'] )."'";
$updateQuery = "UPDATE `{$TABLE_PREFIX}accounts` SET $updateSet WHERE $updateWhere";
mysql_query($updateQuery) or die("MySQL Error: ". htmlspecialchars(mysql_error()) . "\n");
?>

The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.