PDO: using the same bind variable multiple times

Question

I have a problem with the PDO Prepared instructions, where if you need to use the same bind variable multiple times, the request will not be checked.

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 . Values ​​of $params :
Array ( [:status] => PAID [:userid] => 111 )

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

Answer 1

The problem was quotes around status :. Deleted quotes and all is well.

Answer 2

Array keys must not contain a colon. The colon is intended exclusively for PDO to know that the named parameter is next.

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

it should be

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

Answer 3

Works great for me. For instance.

 <?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 car

Answer 4

You are not calling the bindParam method anywhere, why? Before calling execute try adding

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

And then just call $stmt->execute(); to find out how it works for you. Also return your error messages until complete and after creating the PDO instance add this $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); so that errors always occur.

documents are convenient things