-1

I wanted to add variable with date, to add to my date some days. But seems it not working at all. 

$userVal='2014-11-06 20:52:05'; 
mysql_query("UPDATE users SET validity = DATE_ADD($userVal, INTERVAL $someDays DAY) WHERE id = $id");
Verh
  • 71
  • 1
  • 8
  • 2
    possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Matteo Tassinari Nov 04 '14 at 21:43
  • -1: this is, in its own way, a duplicate of http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php, please learn to escape the input. – Matteo Tassinari Nov 04 '14 at 21:44
  • 1
    you need to add `'` single quotes around your string variables in your query, and yes as stated above you need to look out for SQL-injections – cmorrissey Nov 04 '14 at 21:44
  • I do not want prevent SQL-Injection, this code just do not work. And I have no idea why. – Verh Nov 04 '14 at 21:46
  • This is why you should ALWAYS have error handling in your code. So that you can log out your database errors and self-correct them. This would likely show up as a simple syntax error. – Mike Brant Nov 04 '14 at 21:47
  • @MikeBrant there is no any PHP errors – Verh Nov 04 '14 at 21:53
  • `mysql_query` is an obsolete interface and should not be used, it's being removed from PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. Also don't ever say you do not "want to prevent SQL injection". If you can't be bothered to do that, you're a serious liability on any team developing code. Do it right. Don't be a jerk. – tadman Nov 04 '14 at 21:55
  • @GrzegorzNowak I am talking about capturing mysql errors in this case. With something as simple as `mysql_error()`. – Mike Brant Nov 04 '14 at 21:56

3 Answers3

1

Do you have a problem with this?

UPDATE users
    SET validity = DATE_ADD(NOW(), INTERVAL $someDays DAY)
    WHERE id = $id;

You really don't need to pass in the current date/time. You should be using parameterized queries and mysqli or PDO, but those are other matters.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Its not current date, its datestamp from other source than my db, I edited topic it was confusing. – Verh Nov 04 '14 at 21:49
0

If you need to set it to a specific time you can use PHP to do the date addition:

$someDays = 2;
$userVal='2014-11-06 20:52:05'; 

$newUserVal = date("Y-m-d H:i:s", strtotime('+ '.$someDays.' days', strtotime($userVal)));

mysql_query("UPDATE users SET validity = '$newUserVal' WHERE id = $id");

Or if you need to update the existing validity value:

mysql_query("UPDATE users SET validity = DATE_ADD(validity, INTERVAL $someDays DAY) WHERE id = $id");
cOle2
  • 4,725
  • 1
  • 24
  • 26
0

There can be very good reasons not to use NOW(). The query-result using NOW() will never be cached by mysql, since NOW() is different all the time.

The problem with your query would be visible if you would debug it, but printing it to the screen:

$query = "UPDATE users SET validity = DATE_ADD($userVal, INTERVAL $someDays DAY) WHERE id = $id";
die($query);

Which would display, for example:

UPDATE users SET validity = DATE_ADD(2014-11-06 20:52:05, INTERVAL 1 DAY) WHERE id = 123

The problem is that the date you insert belongs between quotes, so change it to:

$query = "UPDATE users SET validity = DATE_ADD('$userVal', INTERVAL $someDays DAY) WHERE id = $id";

Note that the guys are right about SQL-injection though.

vrijdenker
  • 1,371
  • 1
  • 12
  • 25