Update mysql, no affection

Question

I'm trying to update a number of rows in MySql with a prepared PDO statement. It doesn't emit any error but the rows remains untouched. What am I doing wrong?

I replaced username, password and database name with xxx for the sake of security when posting on Stack Overflow.

<?php
header('Access-Control-Allow-Origin: *');
$jsonp = false;

error_reporting(E_ALL); 
ini_set("display_errors", 1);

$db = new PDO('mysql:host=localhost;dbname=xxx;charset=utf8', 'xxx', 'xxx');

$party = ($_POST['party']);
$id = ($_POST['id']); /* String with ids. Ex. "1, 2, 3" */
$state = ($_POST['state']);
$code = ($_POST['fetchCode']);

$stmt = $db->prepare("UPDATE wishes SET state = :state WHERE fetchCode = :code AND partyID = :party AND id IN (:id)");

$stmt->bindParam(':party', $party);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':state', $state);
$stmt->bindParam(':code', $code);

$stmt->execute();

echo json_encode("Done");
?>

You can't bind values for `IN` clause like that. Every value should be binded separately, i.e: `IN (:id1, :id2, :id3)` — u_mulder, May 14 '16 at 11:28
Possible duplicate of [PDO binding values for MySQL IN statement](http://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement) — mitkosoft, May 14 '16 at 11:30
Ok, but since I don't know the number of ids, how can I do it instead? — Axel, May 14 '16 at 11:30
I did, and found this http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/. But I'm not sure how it works even after reading it. — Axel, May 14 '16 at 11:35
placeholder binding is _not text substitution_. So, `mysql` does not see: `... in ( 1, 2, 3 )`. i.e. three separate parameters. Instead, it 'sees': `... in ( '1, 2, 3')`. i.e. one text string containing 'some string of characters', which is useless for what you are wanting to do. — Ryan Vincent, May 14 '16 at 14:05

score 0 · Answer 1 · answered May 14 '16 at 11:34

In your $_POST['id'] there is space after comma. Please try to avoid spaces before and after comma.

Secondly you can add third parameter PARAM_STR to $stmt->bindParam(':id', $id,PARAM_STR); so that it could be treated as a string while preparing query.

Also add error handler to see the error like :

if (!$stmt) { 
    echo "\nPDO::errorInfo():\n"; 
    print_r($db->errorInfo()); 
}

One last thing which can help in debugging is you can see what is your final query getting prepared by echo $stmt->queryString

Update mysql, no affection

1 Answers1