Bind several columns to one variable in PDO

Question

I moved my website to new server and found strange PDO behaviour:

$q = $db->prepare("INSERT INTO tabel SET info = :info, time = :date, active = :date");
$q->bindParam(':info', $info);
$q->bindParam(':date', $date);
$q->execute();

Only time column gets $date, and active gets nothing.
If I change them active = :date, time = :date then it became vice versa. Only first column receives variable. Others receive nothing.

What can be done, to make it send binded one :date to both columns? Without duplicating :date1 :date2.

Answer 1

You'll have to name and match all parameters to their values, so you'll have to go with something like what you said: :date2 and make sure to bind them, so you'll end up with three bindParam statements.

See here: https://bugs.php.net/bug.php?id=33886

:: it was a question of portability (just to share)

[edit/additional] :: I'd like to suggest a better practice, though at the beginning it might seem like a lot of work: Why don't you use stored procedures? Check this: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

What this can help you do is to accept just the two values as parameters but use them in three column updates. Here's a sample:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertIntoTabel`
    (IN paramINFO VARCHAR(50), IN paramDATE DATETIME)
BEGIN
    INSERT INTO tabel 
    SET info = paramINFO
    , time = paramDATE
    , active = paramDATE;
END

And then change your code to this:

$q = $db->prepare("CALL InsertIntoTabel (?,?)");
$q->bindParam(1, $info);
$q->bindParam(2, $date);
$q->execute();

It's only a suggestion, though. :)