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.

You can rewrite your query like `INSERT INTO tableName ( userId, status ) VALUES ( ?, "?" ) ON DUPLICATE KEY UPDATE status = "?"` and your `$params` will be like: `array($_SESSION['userid'], $status, $status)`. And it's interesting what's the output of `errorInfo`. — Leri, Aug 20 '12 at 10:28
It passes the query with no error, but inserts NULL for :status and :userid. — Kao, Aug 20 '12 at 10:28
@Kao Make sure that `$status` and `$_SESSION['userid']` are not `null`s — Leri, Aug 20 '12 at 10:30
Does your $params array hold the values you think it holds? `var_dump ($params);` — GordonM, Aug 20 '12 at 10:35
Possible duplicate of [Use bound parameter multiple times](https://stackoverflow.com/questions/18511645/use-bound-parameter-multiple-times) — JBH, Aug 16 '17 at 00:23

score 8 · Accepted Answer · answered Aug 20 '12 at 11:34

8

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

answered Aug 20 '12 at 11:34

Kao

2,242
3
22
31

Also be aware of case sensitivity. – M at Dec 14 '17 at 18:54
Still doesn't seem to work when `PDO::ATTR_EMULATE_PREPARES` is false, can't find any docs on that – Zander Brown Jan 19 '19 at 23:27

score 3 · Answer 2 · answered Aug 20 '12 at 10:36

3

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']
);

answered Aug 20 '12 at 10:36

GordonM

31,179
15
87
129

Yeah, I just noticed that after posting my initial comment. Funny how little stuff like that is so easy to miss. – GordonM Aug 20 '12 at 10:41
2

I've used it with the ':status' before, with great success. This is the only times it's not working. Also, according to the Docs on PDO, they use it with the :. Also by doing this it won't be able to differ from a table name or a variable. – Kao Aug 20 '12 at 10:51
That's weird, never noticed that in the docs before. I've always just used it without colons in the array keys and it works fine. I guess in that case it's back to the original question, does the array you're passing to execute actually hold the values you think it holds? – GordonM Aug 20 '12 at 10:55
Edited the $params values into the question. – Kao Aug 20 '12 at 11:00

Elias Van Ootegem · Answer 3 · 2012-08-20T11:09:37.907

2

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

edited Aug 20 '12 at 11:09

answered Aug 20 '12 at 10:38

Elias Van Ootegem

74,482
9
111
149

5

Passing an array to execute is equally valid, not using bindParam is not inherently wrong. The bindParam approach is fine, but it's not the root cause of the problem in this case. – GordonM Aug 20 '12 at 10:40
I know, but when you encounter difficulties, my first reaction is to go take the safest, admittedly: in the case of php often counter-intuitive, route and see if that fails, then gradually introduce your code that caused the problem, as a bonus: `bindParam` does allow you to use the `PDO::*` type constants – Elias Van Ootegem Aug 20 '12 at 11:07

score 2 · Answer 4 · answered Aug 20 '12 at 11:36

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

Just realized it was a few quotes causing the problem. – Kao Aug 20 '12 at 11:37 — Kao, Aug 20 '12 at 11:37

PDO: Using same bind variable multiple times

4 Answers4