mysql query question

Hi Jerry, 

How about this: 

// Update expiry to greater of: 1 year from now, 1 year from old expires date
$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");


If their subscription is expired, they get one year from now.  Or if it expires in the future they get a year added on.

Let me know if that works for you.

Thanks for looking at this Dave,

It looks like if the account is expired then they get one year added from the date of renewal.

The client wants to keep the month and day the same and only deal with the year.

So, if an account expired on Sept 1 2015 and it's now Sept 27, 2016 They want to update the expiration date to Sept 1, 2017

And, if the account expired on Sept 1, 2016 and it's now Sept 27, 2016 They still want to update the expiration date to Sept 1, 2017

If the account is not expired, Say it's due to expire on Sept 1, 2017 and a member wants to pay for an additional year on Sept 27, 2016, they can do that and the expiration date will update to Sept 1, 2018

Hope that makes sense.

Jerry

So if my account expires on Jan 2th, 2016 and I renew on Dec 30th, 2016 I get 2-3 days of subscription and then I need to renew again?  

Are you sure that's what they want and they understand the implications?  A standard subscription renew process (and what customers would expect) would be one year from the greater of the expiry or the current date.  I don't know the website but typically otherwise you get people just creating new accounts and silly things like that to get more out of their renewed subscriptions.

In any case, let me know either way and I'll give you a hand.  Thanks.

Good point,

It would be much easier if they went with your appoach

Unfortunately, all accounts in this organization actually expire on October 1, and there are some members that let their accounts expire a few years ago and  who decide to renew now, (or next month, or in 6 months). They were just looking for an elegant (automatic) way of dealing with (most) of these folks.

I see that it can get really complicated and maybe there's no realistic solution.

Anyway, while you're thinking about this, how would I modify your code to deal only with the year if that's what they end up wanting.

In the meantime I've made a proposal to them based on your original idea.

Jerry

Hi Jerry,

>how would I modify your code to deal only with the year if that's what they end up wanting.

Try this: 

// Update expiryDate - 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).
$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");

Let me know if that works for you!

Really appreciate the time you've spent on this.

Now it's up to them...

Big Thanks,

Jerry Kornbluth

Hi Jerry,

You're missing a "`" on this line:

$updateQuery = "UPDATE `{$TABLE_PREFIX}accounts` SET $updateSet WHERE $updateWhere";

Cheers,

Hi Daryl,

Thanks for catching this. Works like a charm now.

Could you update both of Dave's posts to reflect the change?

Best,

Jerry