I'm trying to do this:
$sth = $dbi->prepare('INSERT INTO table VALUES (?, ?, ?)');
$sth->execute(
$var1,
$var2 || 'NOW()',
$var3
);
without any luck. Any ideas?
views:
203answers:
3
+3
A:
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);
hobodave
2010-02-09 16:57:58
You've got to be kidding... Working out the date string for yourself doesn't require you to change languages. The first thing to mind is `use Date::Format; $now = time2str('%Y-%m-%d %T', time);`, but there's probably a quicker/easier way to do it out there that I've forgotten about.
Dave Sherohman
2010-02-10 11:49:38
@Dave: Ah, you know I somehow missed completely that this was a Perl question, and not a PHP question. The syntax is so similar. I'll update my question to match your suggestion. Thanks.
hobodave
2010-02-10 18:38:53
+11
A:
$sth = $dbi->prepare('INSERT INTO table VALUES (?, COALESCE(?, NOW()), ?)');
$sth->execute(
$var1,
$var2,
$var3
);
Quassnoi
2010-02-09 17:00:14
+1: clever. I always forget about coalesce. I didn't even know it would work in this context.
hobodave
2010-02-09 17:02:09
Note that `coalesce` is really equivalent to `//`, not `||`. However, it may be good enough for what you're doing, if `$var2` never contains the empty string or 0.
cjm
2010-02-09 20:29:35
+1
A:
Dear Friend,
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;
muruga
2010-03-02 05:00:26