How can I pass MySQL functions as bind parameters in prepared statement?

Question

I'm trying to do this:

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

without any luck. Any ideas?

Answer 1

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

Answer 2

Functions cannot be bound parameters. MySQL will enclose them in quotes which is not valid syntax.

Your options are:

• DEFAULT CURRENT_TIMESTAMP - If the field is a TIMESTAMP field you can declare it to have a default of 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 use the following coding also.

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