PDO 'UPDATE' Statement with parameters does not work

Question

I have this PDO statement that doesn't update my database and doesn't return any error:

// $database = initialized PDO instance
$status = 'processing';
$submission_id = 213;

$sql  = "UPDATE `submission` 
  SET `status`=':status' 
  WHERE `id`=':submission_id'";

$query = $database->prepare($sql);
$result = $query->execute(array(
  ':status' => $status, 
  ':submission_id' => $submission_id
));

var_dump($result);            // true
var_dump($query->rowCount()); // 0
$database->errorCode();       // 0000
$database->errorInfo();       // [0]=> string(5) "00000" [1]=> NULL [2]=> NULL

If I remove parameters from $sql it works:

$sql2 = "UPDATE `submission` SET `status`='processing' WHERE `id`='214'";

all results are the same except $query->rowCount() which returns 1 this time and the database is updated. I use the same PDO object for other insert and select operations and it works well.

Why the first parametized query doesn't work?

Thanks all! At first I had no quotes but experienced some other error, so then I've added them as an attempt to fix it, removed the other error and that's how I got here. — Alan, Dec 11 '15 at 11:54

score 2 · Accepted Answer · answered Dec 11 '15 at 11:41

2

Do not quote parameters:

$sql  = "UPDATE `submission` 
  SET `status`=:status 
  WHERE `id`=:submission_id";

answered Dec 11 '15 at 11:41

Lukasz Szozda

162,964
23
234
275

score 1 · Answer 2 · answered Dec 11 '15 at 11:44

Stop using double quotes

$status = 'processing';
$submission_id = 213;

$sql  = "UPDATE `submission` 
  SET `status`=:status 
  WHERE `id`=:submission_id";

$query = $database->prepare($sql);
$result = $query->execute(array(
  ':status' => $status, 
  ':submission_id' => $submission_id
));

score 1 · Answer 3 · answered Dec 11 '15 at 11:45

As per PDO statement

The parameters to prepared statements don't need to be quoted; the driver automatically handles this.

SO wrap off quotes from placeholder in your query

$sql  = "UPDATE `submission` 
SET `status`=:status
WHERE `id`=:submission_id";

PDO 'UPDATE' Statement with parameters does not work

3 Answers3