MySQL Syntax Issue when updating expiresDate

5 posts by 3 authors in: Forums > CMS Builder
Last Post: December 20, 2011   (RSS)

I’ve been using the following to add one year to a member’s account expiration date.
// UPDATE ONE YEAR FROM NOW
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = NOW() + INTERVAL 1 YEAR
WHERE password = '" . $_REQUEST['password'] . "'
AND username = '" . $_REQUEST['username'] . "'")
or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

I’d really rather add the year to their existing expiration date. When I use the code below I get the error:

Notice: Undefined index: expiresDate in /hsphere/local/home/apbcweb/artistsofpalmbeachcounty.org/renewal_thanks.php on line 41
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 'WHERE password = .... etc.

// UPDATE ONE YEAR FROM EXISTING EXPIRATION DATE
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = '".mysql_escape( $_REQUEST['expiresDate'] + 'INTERVAL 1 YEAR' )."',
WHERE password = '" . $_REQUEST['password'] . "'
AND username = '" . $_REQUEST['username'] . "'")
or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();

Aside from any actual syntax issues, I think I this is occurring because there is no one logged in when the form is submitted, but I don’t quite know how to resolve the issue. The member is asked for their username and password in order to accomplish the update.

I’ve attached the complete file and hope someone has a solution.

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

Re: [gkornbluth] MySQL Syntax Issue when updating expiresDate

By Jason - December 19, 2011

Hi Jerry,

The problem you're having here is that there is no value in $_REQUEST['expiresDate'], since you probably don't have an "expiresDate" field in the form your submitting. If they are logged in successfully, you could use $CURRENT_USER to get the expires date.

Another approach is to just use the field name and increment whatever that value is by one year.

For example:

// UPDATE ONE YEAR FROM EXISTING EXPIRATION DATE
mysql_query("UPDATE `{$TABLE_PREFIX}accounts`
SET expiresDate = expiresDate + 'INTERVAL 1 YEAR' ,
WHERE password = '" . $_REQUEST['password'] . "'
AND username = '" . $_REQUEST['username'] . "'")
or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();


Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [gkornbluth] MySQL Syntax Issue when updating expiresDate

By Dave - December 20, 2011

Removing the single quotes is the way to do it.

Also, don't forget to escape your inputs:

WHERE password = '" . mysql_escape($_REQUEST['password']) . "'
AND username = '" . mysql_escape($_REQUEST['username']) . "'")

And if you only want to edit the current user I'd use $CURRENT_USER, but if you need to check the form input you'll need $_REQUEST.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] MySQL Syntax Issue when updating expiresDate

Oops, thanks for the reminder, Dave.

I do need to check the form in this case.

Best,

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