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