Date math with prepared statements

PHP
1

I want to add a record to a table with an expire date that is set at runtime. If I do a normal update statement, the following code works fine


update t1 set expiredate = now() + INTERVAL 120 DAY

But when I use this with a prepared statement in a bind_param, I get an “Incorrect datetime value”.

How should this be formatted for use with a prepared statement if the ‘120’ is assigned at runtime? Also, I would prefer to use the MySQL server date as the base. Thanks

2

Are you trying to bind just the ‘120’, or the entire string? ‘now() + INTERVAL 120 DAY’

Prepared statements (in PDO anyway) can only be bound to values you are inserting and not fields - so I would guess the same goes this part of your statement because it will be escaped as a string ‘“now() + INTERVAL 120 DAY”’ and become invalid.

I could be wrong though - be interesting to hear if there is a way round this.

3

What I am doing now, and it works fine, is to not do the date field update as a ? in the prepared statement. So I create the sql string with

"$sql = "..., ?, ?, now() + INTERVAL " . $days . " DAY, ?, ?..."

I would just prefer to be consistent in my application and do it with the use of the ? and the bound param value. So yes, if anyone knows if this can be done and how to do it, I would like to hear it also. Thanks for the input.

4

Binding “120” as a parameter works fine with PDO - are you using something else?

5

I just tried it and it didn’t work. I am using MySQLi. So you just bind “120” as the date value and it automatically adds 120 days to the current date?

6

To be clear, this works


$days = 120;
insert into t1 (t1name, t1desc, dateexpires) values (?, ?, now() + INTERVAL " . $days . " DAY)"
$link->prepare($qt_sql);
$link->bind_param("ss", $name, $desc);
$link->execute();

But this doesn’t work


$days = 120;
insert into t1 (t1name, t1desc, dateexpires) values (?, ?, ?)"
$link->prepare($qt_sql);
$link->bind_param("sss", $name, $desc, $days);
$link->execute();

Not sure if that’s what you were recommending?

7

You can do this in PDO:

$sql = "insert into t1 (t1name, t1desc, dateexpires) values (?, ?, now() + INTERVAL ? day)";

I noticed that you’re attempting to bind $days as a string - if you do it as an integer does it work?

8

No, tried that and it makes no difference. The date shows up in the db as ‘2000-1-20…’ if I set the days at 120.

9

The bind parameters work slightly differently in mysqli to those in PDO if I understand correctly.

10 

$link->bind_param("sss", $name, $desc, $days);

Here you’re saying that $days should be bound as a string (that’s what the 's’s are for).
What if you try this?


$link->bind_param("ssi", $name, $desc, $days);

Edit>>
Must learn to thoroughly read entire topic before posting!
Sorry for basically saying what others have already said…