PDO not executing statement

Question

I have a simple method that is not working as it should - would like to use prepared statements but somehow it is not executed; instead the raw query works just fine.

What could be the problem? Should I pass some extra args to pdo methods?

$_POST['sequence'] = [
    0 => 2,
    1 => 1
];

if (!empty($_POST['sequence'])) {
    $query = '
        UPDATE '.$this->db->backtick($this->controller->table).'
        SET `sequence` = CASE `id`'
        ;

    foreach ($_POST['sequence'] as $sequence => $id) {
        $values[':id'.$id] = $id;
        $values[':sequence'.$sequence] = $sequence;
        $query .= ' WHEN :id'.$id.' THEN :sequence'.$sequence;
    }

    $values[':ids'] = implode(',', array_values($_POST['sequence']));

    $query .= ' END WHERE `id` IN (:ids)';

    $statement = $this->db->handle->prepare($query);
    $statement->execute($values); //doesn't work

    //$query2 = str_replace(array_keys($values), array_values($values), $query);
    //$this->db->handle->query($query2); works
}

See: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition/28067015#28067015 — Progrock, Jan 13 '16 at 10:47
@Progrock did not get it. How could that be implemented here? — etilge, Jan 13 '16 at 11:08
I think @Progrock has cracked it. Can you show the value of $query (for any interaction in the loop). I'll think you'll find the IN statement within the WHERE Clause is invalid. — David Rushton, Jan 13 '16 at 12:11
@RyanVincent what? The question was about pdo internals. Solved it already. — etilge, Jan 13 '16 at 16:21

score 0 · Answer 1 · answered Jan 13 '16 at 14:16

0

Don't bind the param inside IN

$query .= ' END WHERE `id` IN ('.implode(',', array_values($_POST['sequence'])).')';

answered Jan 13 '16 at 14:16

etilge

85
1
9

PDO not executing statement

1 Answers1