Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
MySQL Syntax Issue when updating expiresDate

 
 


gkornbluth
Veteran

Dec 18, 2011, 7:15 AM

Post #1 of 5 (905 views)
Shortcut
MySQL Syntax Issue when updating expiresDate Can't Post
I’ve been using the following to add one year to a member’s account expiration date.
Code
// 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:

Code
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.
Code
 // 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


Jason
Staff / Moderator


Dec 19, 2011, 7:50 AM

Post #2 of 5 (873 views)
Shortcut
Re: [gkornbluth] MySQL Syntax Issue when updating expiresDate [In reply to] Can't Post
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:

Code
// 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 - Programmer 
interactivetools.com

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


gkornbluth
Veteran

Dec 19, 2011, 7:46 PM

Post #3 of 5 (871 views)
Shortcut
Re: [Jason] MySQL Syntax Issue when updating expiresDate [In reply to] Can't Post
Hi Jason,

Thanks for this.

2 minor changes to get it to work.

I removed
1) the single quotes around INTERVAL 1 YEAR and
2) the comma after it since it's the last in the series (like you taught me).

Is removing the single quotes OK, or did I cause other issues that I haven't found yet?

Best,

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
http://www.thecmsbcookbook.com


Dave
Staff / Moderator


Dec 19, 2011, 10:37 PM

Post #4 of 5 (863 views)
Shortcut
Re: [gkornbluth] MySQL Syntax Issue when updating expiresDate [In reply to] Can't Post
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
 


gkornbluth
Veteran

Dec 20, 2011, 4:41 AM

Post #5 of 5 (857 views)
Shortcut
Re: [Dave] MySQL Syntax Issue when updating expiresDate [In reply to] Can't Post
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!
http://www.thecmsbcookbook.com