Incorrect `update statement` using IN operator with Zend

Question

I have a function which is wanted to execute a statement like below:

UPDATE coupon_users SET status = status | '1' WHERE id IN ('3','4')

And in coupon_users model, I wrote a method like below do to:

/**
* @param array $ids        #array(3,4)
* @param array $status     #1
*/
public function updateStatus(array $ids, $status)
{
    $result = $this->_db->query(
        "UPDATE {$this->_name} SET status = status | ? WHERE id IN (?)",
        array(
            $status,
            $ids
        )
    )->execute();
    return $result;
}

But the query is always:

UPDATE coupon_users SET status = status | '1' WHERE id IN ('Array')

I don't know what am I wrong here, please help me, many thanks.

Answer 1

According to the PDO documentation (Zend_Db uses PDO as its DB access backend):

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

So, you'll probably need to prepare a bit further your query, so that it contains as many markers as elements in the array. A possible solution could be the following:

// Compose the query
$queryToExecute = "UPDATE {$this->_name} SET status = status | ? WHERE id IN (";
$questionMarks = array();
for ($id in $ids) {
    $questionMarks[] = '?';
}
$queryToExecute .= implode(',', $questionMarks);
$queryToExecute .= ')';

// $queryToExecute should have the format "UPDATE ... WHERE id IN (?,?,?,...?)"

// Execute it
$result = $this->_db->query(
    $queryToExecute,
    array($status, $ids)
)->execute();

Hope that helps,