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
– Leri, Commented Aug 20, 2012 at 10:28
It passes the query with no error, but inserts NULL for :status and :userid. — Kao
– Kao, Commented Aug 20, 2012 at 10:28
@Kao Make sure that $status and $_SESSION['userid'] are not nulls — Leri
– Leri, Commented Aug 20, 2012 at 10:30
Does your $params array hold the values you think it holds? var_dump ($params); — GordonM
– GordonM, Commented Aug 20, 2012 at 10:35

Kao · Accepted Answer · 2012-08-20 11:34:39Z

9

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

answered Aug 20, 2012 at 11:34

Kao

2,2723 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mahdi Rafatjah Over a year ago

Also be aware of case sensitivity.

zbrown Over a year ago

Still doesn't seem to work when PDO::ATTR_EMULATE_PREPARES is false, can't find any docs on that

GordonM · Accepted Answer · 2012-08-20 10:36:36Z

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, 2012 at 10:36

GordonM

31.9k17 gold badges94 silver badges134 bronze badges

4 Comments

GordonM Over a year ago

Yeah, I just noticed that after posting my initial comment. Funny how little stuff like that is so easy to miss.

Kao Over a year ago

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.

GordonM Over a year ago

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?

Kao Over a year ago

Edited the $params values into the question.

Elias Van Ootegem · Accepted Answer · 2012-08-20 11:09:37Z

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, 2012 at 11:09

answered Aug 20, 2012 at 10:38

Elias Van Ootegem

76.7k10 gold badges123 silver badges160 bronze badges

2 Comments

GordonM Over a year ago

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.

Elias Van Ootegem Over a year ago

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

VolkerK · Accepted Answer · 2012-08-20 11:36:30Z

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

Collectives™ on Stack Overflow

PDO: Using same bind variable multiple times

4 Answers 4

2 Comments

4 Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

2 Comments

1 Comment

Linked

Related