PDO: Using same bind variable multiple times

Question

I'm having an issue with PDO Prepared statements, where if you need to use the same bind variable multiple times, the query won't validate.

Example:

$params = array (
    ':status' => $status,
    ':userid' => $_SESSION['userid']
);

$stmt = $pdo->prepare ('
    INSERT INTO 
        tableName
        ( userId, status )
        VALUES
        ( :userid, ":status" )
        ON DUPLICATE KEY UPDATE
            status = ":status"
');

if ( ! $stmt->execute ( $params ))
{
    print_r( $stmt->errorInfo ());
}

EDIT: The values of the $params are:
Array ( [:status] => PAID [:userid] => 111 )

EDIT 2:
I've noticed that instead of the original values, instead of userid, 0 is inserted, and instead of status an empty string is inserted.

Answer 1

Problem was the quotes around the :status. Removed quotes and all is good.

Answer 2

Your array keys don't need to contain a colon. The colon is purely for PDO to know that what follows is a named parameter.

$params = array (
    ':status' => $status,
    ':userid' => $_SESSION['userid']
);

should be

$params = array (
    'status' => $status,
    'userid' => $_SESSION['userid']
);

Answer 3

You're not calling the bindParam method anywhere, why? Just before you invoke execute, try adding

$stmt->bindParam(':userid', $_SESSION['userid'], PDO::PARAM_INT);
$stmt->bindParam(':status', $status, PDO::PARAM_STR);

And then just call $stmt->execute(); see how that works for you. Also turn your error messages up to full, and after instantiating your PDO instance, add this $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); to ensure errors are always thrown.

the docs are handy things

Answer 4

Works fine for me. E.g.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
setup($pdo);

echo 'client version: ', $pdo->getAttribute(PDO::ATTR_CLIENT_VERSION), "\n";
echo 'server version: ', $pdo->getAttribute(PDO::ATTR_SERVER_VERSION), "\n";
echo "before:\n";
foreach( $pdo->query('SELECT * FROM tmpTableName', PDO::FETCH_ASSOC) as $row ) {
    echo join(', ', $row), "\n";
}

$status = 1;
$_SESSION['userid'] = 'foo';

$params = array (
    'status' => $status,
    'userid' => $_SESSION['userid'],
);

$stmt = $pdo->prepare ('
    INSERT INTO 
        tmpTableName
        (userId, status)
    VALUES
        (:userid, :status)
    ON DUPLICATE KEY UPDATE
        status = :status
');

if ( ! $stmt->execute ( $params ))
{
    print_r( $stmt->errorInfo ());
}

echo "after:\n";
foreach( $pdo->query('SELECT * FROM tmpTableName', PDO::FETCH_ASSOC) as $row ) {
    echo join(', ', $row), "\n";
}

function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE tmpTableName (
            userId varchar(32),
            status int,
            unique key(userId)
        )
    ');
    $pdo->exec("INSERT INTO tmpTableName (userId,status) VALUES ('foo', 0)");
    $pdo->exec("INSERT INTO tmpTableName (userId,status) VALUES ('bar', 0)");
}

prints

client version: mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $
server version: 5.5.25a
before:
foo, 0
bar, 0
after:
foo, 1
bar, 0

on my machine