Update query with where in and pdo

PHP
1

Can anyone explain why the first query using a bind value for the WHERE IN values only updates one of the client ids? But when I use the actual variable value, like in the second query, it updates all of the client ids.

Thanks.

UPDATES ONLY ONE ID:


	$query =
	"
		UPDATE client SET loginfosent = 1 WHERE clientID IN (:clientid)
	";
	$sth = $dbh->prepare($query);
	$sth->execute
	(
		array
		(
			':clientid' => $cinfo['clientids']
		)
	);

UPDATES ALL IDS:


	$query =
	"
		UPDATE client SET loginfosent = 1 WHERE clientID IN (".$cinfo['clientids'].")
	";
	$sth = $dbh->prepare($query);
	$sth->execute();
2

The second get interpreted as you would expect, like


WHERE clientID IN (1, 5, 6)

The first like


WHERE clientID IN ('1, 5, 6')

Each placeholder in a prepared statement is considered a single value. This is the desired behavior because otherwise you would still have ambiguity with what is a value, and what is sql syntax.

You could create the appropriate amount of placeholders, dynamically. Then, of course pass an array to execute().

3

Thanks crmalibu.

Since the number of placeholders / ids will vary, here’s what I ended up with.


	$clientidsarray = array();
	$clientidsarray = explode(',', $cinfo['clientids']);
	$execarray = array();
	for ($i = 0; $i < count($clientidsarray); $i++)
	{
		$execarray[':id'.$i] = $clientidsarray[$i];
	}
	$wherein = implode(',', array_keys($execarray));
	$query =
	"
		UPDATE client SET loginfosent = 1 WHERE clientID IN (".$wherein.")
	";
	$sth = $dbh->prepare($query);
	$sth->execute($execarray);