Calling NOW() function in MySQL

Question

I'm using php for making queries for mysql. Here is one:

UPDATE `subscribers` SET `curDate` = NOW() WHERE `e_mail` = "$resEmail"

curDate - DateTime type. The problem is that after this query curDate of given email is

0000-00-00 00:00:00

What's wrong?

show your CREATE TABLE statement so we can see datatypes. Also, it's very bad practice to name fields with reserved words (like curDate- remember sql isn't case sensitive), — dnagirl
– dnagirl, Commented Apr 27, 2010 at 19:26

Mark Byers · Accepted Answer · 2010-04-27 19:31:01Z

5

Your PHP probably looks like this now:

$sql = 'UPDATE `subscribers` SET `curDate` = NOW() WHERE `e_mail` = "$resEmail"';

The single quotes prevent the variable's value from being substituted into the string. You will need to change it to this:

$sql = "UPDATE `subscribers` SET `curDate` = NOW() WHERE `e_mail` = '$resEmail'";

You should also be aware that you may have an SQL injection vulnerability here. Consider using mysql_real_escape_string to escape the email address.

$sql = "UPDATE `subscribers` SET `curDate` = NOW() WHERE `e_mail` = '" . mysql_real_escape_string($resEmail) . "'";

edited Apr 27, 2010 at 19:31

answered Apr 27, 2010 at 19:25

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Max Frai Over a year ago

Can I insert php-variables in single quotes?

Mark Byers Over a year ago

@Ockonal: If the entire string is in double quotes, the single quotes inside that string don't prevent the php-variable from being interpreted.

VolkerK Over a year ago

"In MySQL strings must be surrounded by single quotes, not double quotes." - only true if the server mode ANSI_QUOTES is set, see dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Mark Byers Over a year ago

@VolkerK: Sorry... you are right: +1, I removed that incorrect line. But the change I suggested should still work because the single quotes that I assume are there (but not included in the question) will prevent the variable from being substituted into the string in PHP. Swapping the single and double quotes should fix this.

Mikulas Dite · Accepted Answer · 2010-04-27 19:35:12Z

Since you're using timedate function which is based on proper timestamp, try setting it with Timestamp(Now()).

Collectives™ on Stack Overflow

Calling NOW() function in MySQL

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related