variable in mysql query, NOW() + INTERVAL $variable

Question

$someDays=2;

how to put this variable into this mysql query?

these do not work:

mysql_query("UPDATE users (validity) VALUES (NOW() + INTERVAL '$someDays' DAY) WHERE id='$id'");
mysql_query("UPDATE users (validity) VALUES (NOW() + INTERVAL $someDays DAY) WHERE id='$id'");
mysql_query("UPDATE users (validity) VALUES (NOW() + INTERVAL '".$someDays."' DAY) WHERE id='$id'");
mysql_query("UPDATE users (validity) VALUES (NOW() + INTERVAL ".$someDays." DAY) WHERE id='$id'");

UPDATE doesn't have VALUES http://dev.mysql.com/doc/refman/5.0/en/update.html - `UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]` - You may have meant to use INSERT INTO. Or use `UPDATE table SET...` — Funk Forty Niner, Nov 04 '14 at 19:24
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Nov 04 '14 at 19:25

score 2 · Answer 1 · edited May 23 '17 at 12:05

2

The most readable way to do this would be like this -

"UPDATE `users` SET `validity` = DATE_ADD(NOW(), INTERVAL '".$someDays."' DAY) WHERE `id`='".$id."' "

You're trying to use syntax from an INSERT rather than an UPDATE. In addition, if you're going to use variables in a certain way in the query you should do that for all variables in the query to maintain consistency and readability.

Please, don't use mysql_* functions, They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi. This article will help you decide.

edited May 23 '17 at 12:05

Community

1
1

answered Nov 04 '14 at 19:28

Jay Blanchard

34,243
16
77
119

1

Only the fish & birds like worms; not me. But I did +1 though ;) – Funk Forty Niner Nov 04 '14 at 19:38
1

What do you mean by "this do not work"? Did you get a syntax error or some other issue? If so we're going to need to see more of your code and any errors that you're getting. – Jay Blanchard Nov 04 '14 at 19:40
Do you have any error checking in your script? Add error reporting to the top of your file(s) right after your opening ` – Jay Blanchard Nov 04 '14 at 19:51
The OP's on something. Just accepted the other answer where OP stated it also didn't work 18 mins. prior. DUH. Some just aren't cut out for coding. *Doing a burger flip*. – Funk Forty Niner Nov 04 '14 at 19:55
The syntax for `DATE_ADD()` in this answer is wrong which is probably why the OP said it doesn't work. – cOle2 Nov 04 '14 at 19:59
Roger that @cOle2 - I skipped over that in my head. It's fixed. – Jay Blanchard Nov 04 '14 at 20:01
@cOle2 I have no problem with the OP accepting your answer, I'm all for it. What I don't get is that the OP posted (and deleted) the comment left under your answer stating that it did not work, which is why I gave my head a shake as to why he/she said that to start with. I even +1 your also. *Sharing the wealth* ;) – Funk Forty Niner Nov 04 '14 at 20:05

cOle2 · Accepted Answer · 2014-11-04T21:42:59.677

2

Per the comments you are using INSERT syntax but attempting to do an UPDATE. Something like the following should work:

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

EDIT:

To add $someDays days to the existing validity value you can do

… SET validity = DATE_ADD(validity, INTERVAL $someDays DAY) …

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

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

edited Nov 04 '14 at 21:42

answered Nov 04 '14 at 19:28

cOle2

4,725
1
24
26

@GrzegorzNowak 18 mins. ago, you said this didn't work. So, why did you accept it just now? – Funk Forty Niner Nov 04 '14 at 19:54
I am so sorry, it works perfectly I just answered wrong comment o.O – Verh Nov 04 '14 at 20:20
@GrzegorzNowak I understand. Thanks for clearing that up, it was confusing. Glad this matter was resolved, *cheers*. – Funk Forty Niner Nov 04 '14 at 20:37
@Fred-ii- Could you even tell you how to use variable with data (like this: '2014-11-06 20:52:05') instead of NOW(), please? – Verh Nov 04 '14 at 20:46
Use `DATETIME` as your column type, it will enter the time/date using `NOW()` as you shown `2014-11-06 20:52:05`, which is better to use. Otherwise, you will have more code to add and it's much easier doing it that way, believe me. @GrzegorzNowak – Funk Forty Niner Nov 04 '14 at 20:52
@Fred-ii- Yea I know that I am using it :), but I wanted to add variable with date instead of NOW(), 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 Nov 04 '14 at 21:31
@GrzegorzNowak You're best asking a new question. – Funk Forty Niner Nov 04 '14 at 21:39
@cOle2 Quite nice of you, yet it should have been a new question. Oh well, I'm not going to argue lol – Funk Forty Niner Nov 04 '14 at 21:45

score 0 · Answer 3 · answered Nov 04 '14 at 19:45

0

Try this one to add days in current date. you can't perform two operation at one time so try to update or try to insert.

mysql_query("insert into  users (validity) VALUES (select DATE_ADD(NOW(), INTERVAL ".$someDays." DAY) );

answered Nov 04 '14 at 19:45

Altmish-E-Azam

1,561
1
13
24

variable in mysql query, NOW() + INTERVAL $variable

3 Answers3