PDO updates only one row when used with placeholders

Question

I have an array which holds multiple ids's, and i need to to update multiple columns. i am using MySQL IN() for that. the problem is when i use the query below it works perfectly fine.

public function available($value, $propertyId = array()) {
    $sth = $this->dbh->prepare('UPDATE properties p SET p.status = :status WHERE p.id IN('.implode(',',$propertyId).')');
    $sth->bindParam(':status',$value);
    return $sth->execute();
}

as the above query is not using any placeholders for second argument $propertyId i assume it is wrong way to do so. but when i use either named or unnamed place holder in the query it will update only 1 row. for example the below codes update only one row.

//This will update only one row.
//Using Named Place Holder
public function available($value, $propertyId = array()) {
    $sth = $this->dbh->prepare('UPDATE properties p SET p.status = :status WHERE p.id IN(:propertyId)');
    $sth->bindParam(':status',$value);
    $sth->bindParam(':propertyId', implode(',', $propertyId));
    return $sth->execute();
}

Or

//Using Unnamed Place Holder.
public function available($value, $propertyId = array()) {
    $sth = $this->dbh->prepare('UPDATE properties p SET p.status = ? WHERE p.id IN(?)');
    return $sth->execute(array($value, implode(',', $propertyId)));
}

i tried converting converting the array to a string and assigning it to the variable like below.

public function available($value, $propertyId = array()) {
    $id = implode(',', $propertyId);
    $sth = $this->dbh->prepare('UPDATE properties p SET p.status = ? WHERE p.id IN(?)');
    return $sth->execute(array($value, $id));
}

even that won't work. what is happening? what am i missing?

thank you

AFAIK parameterized queries cannot be used with list `IN (...)`, if you don't specify every single element with a placeholder. `IN (?,?,?,? ...)`. Your first query is the way to go, when propertyId only contains integer values. — Jacob, Aug 23 '11 at 07:53
You can find solution for similar problem in http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — Andrej, Aug 23 '11 at 07:54
Just to support first comment, `Doctrine` ORM uses `PDO` and generates `(?,?,?,....)` for `IN` queries. — J0HN, Aug 23 '11 at 07:58
@culrais, is that first query safe from SQL injections? i don't know but i thought it is unsafe to do it that way. — Ibrahim Azhar Armar, Aug 23 '11 at 08:02
@Ibrahim Azhar Armar, use http://php.net/manual/en/pdo.quote.php to escape all data you pass in that query(each value in array, not imploded string) and you will be as safe as with pdo::prepare/bind_param, but of cource a little bit more traffic will be generated to SQL server - each eascape call should be MySql call. — XzKto, Aug 23 '11 at 08:09

score 0 · Accepted Answer · answered Aug 23 '11 at 08:20

You can do something like this, wish it helps.

public function available($value, $propertyId = array()) {
    $id_placeholders = implode(',', array_fill(0, count($propertyId), '?'));
    $sth = $this->dbh->prepare('UPDATE properties p SET p.status = ? WHERE p.id IN('.$id_placeholders.')');
    return $sth->execute(array_merage(array($value), $propertyId));
}

PDO updates only one row when used with placeholders

1 Answers1