PDO：多次使用相同的绑定变量

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.

我遇到PDO Prepared语句的问题,如果你需要多次使用相同的绑定变量,查询将无法验证。

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 )

编辑:$ params的值是: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.

编辑2:我注意到,而不是原始值,而不是userid,插入0,而不是状态插入空字符串。

4 个解决方案

#1

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

问题是围绕:状态的引用。删除了引号,一切都很好。

#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.

您的数组键不需要包含冒号。冒号纯粹是为了让PDO知道接下来是命名参数。

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

should be

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

#3

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

在我的机器上

#4

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

你不是在任何地方调用bindParam方法,为什么?在调用execute之前,尝试添加

$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.

然后只需调用$ stmt-> execute();看看它对你有什么用。同时将您的错误消息转为完整,并在实例化PDO实例后,添加此$ db-> setAttribute(PDO :: ATTR_ERRMODE,PDO :: ERRMODE_EXCEPTION);确保始终抛出错误。

the docs are handy things

文档是方便的东西

#1