mysql query question

12 posts by 3 authors in: Forums > CMS Builder
Last Post: October 12, 2016   (RSS)

Hi All,

I’ve been using:

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

to update a member’s expiration date to 1 year from the current expiration date.

I’d like to modify the mysql_query to insure that:

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).


Any ideas?

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - September 27, 2016 - edited: October 12, 2016

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.

Dave Edis - Senior Developer
interactivetools.com

By gkornbluth - September 27, 2016 - edited: September 27, 2016

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - September 27, 2016

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.

Dave Edis - Senior Developer
interactivetools.com

By gkornbluth - September 27, 2016 - edited: September 27, 2016

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - September 27, 2016 - edited: October 12, 2016

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!

Dave Edis - Senior Developer
interactivetools.com

Really appreciate the time you've spent on this.

Now it's up to them...

Big Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Hi Dave,

I just got approval to implement your year only approach and on implementation it's throwing a MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Not sure why. Here's the code that I'm using:

<?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");

    // redirect after a successful update  
    header("Location:  http://www.ceramicleaguepalmbeaches.org/member_login2.php");  ?>

Hope you have a moment to take a look.

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Daryl - October 6, 2016

Hi Jerry,

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

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

Cheers,

Daryl Maximo
PHP Programmer - interactivetools.com

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php