How to pass MySQL functions as binding parameters in a prepared statement?

Question

I am trying to do this:

$sth = $dbi->prepare('INSERT INTO table VALUES (?, ?, ?)'); $sth->execute( $var1, $var2 || 'NOW()', $var3 ); 

no luck. Any ideas?

Answer 1

 $sth = $dbi->prepare('INSERT INTO table VALUES (?, COALESCE(?, NOW()), ?)'); $sth->execute( $var1, $var2, $var3 ); 

Answer 2

Functions cannot be related parameters. MySQL will enclose them in quotation marks, which is not a valid syntax.

Your options:

• DEFAULT CURRENT_TIMESTAMP. If the field is a TIMESTAMP field, you can declare that it has a default value for the current time like this. This does not work for DATETIME fields.
• Use perl - $now = time2str('%Y-%m-%d %T', time);

Answer 3

You can also use the following encoding.

 $sth = $dbi->prepare('INSERT INTO table VALUES (?, COALESCE(?, NOW()), ?)'); $sth->bind_param($var1,$var2,$var3); $sth1=$sth->execute;