date math with prepared statements

[bostboy]

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

[Cups]

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.

[bostboy]

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.

[aamonkey]

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

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

[bostboy]

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?

[bostboy]

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?

[aamonkey]

...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?

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?

[bostboy]

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.

[Cups]

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

[Immerse]

$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...