Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Only one row effected when using placeholder in sql in condition

As title, following is my code

    $phql =
        "UPDATE \Model\MyTable
        SET deleted =1
        WHERE
            member_id =:member_id:
            AND id IN (:ids:)";
    return \Phalcon\DI::getDefault()['modelsManager']->executeQuery($phql, array(
        'member_id' => 1,
        'ids' => implode(', ', array(1, 2, 3));
    ));

It will return success, but only one row effected, use "\Phalcon\Db\Adapter\Pdo\Mysql::execute" too. I don't know why?



19.6k

Your WHERE says Set the deleted field of a certain row to 1, been that row's id equal to 1 and inside a range (IN). To modify more than one row at a time, you'll have to remove member_id = :member_id:.

Your code will work for rows with an id between 1 and 3 (inclusive), but no others, no matter which member_id you specify.



634
edited Mar '15

my_table structure, id is PK

id, member_id, deleted
1    1             0
2    1             0
3    1             0
4    2             1
5    2             0

This point is, when i use raw sql is fine, but placeholder fail



19.6k

Try this (but I don't think it'll work):

$ids = implode(', ', [1, 2, 3]);

return \Phalcon\DI::getDefault()['modelsManager']->executeQuery($phql, ['member_id' => 1, 'ids' => $ids]);

The only way I've found that works and is still convenient is imploding the array and adding it to the query string unescaped:

$ids = implode(', ', array(1, 2, 3));
$phql =
        "UPDATE \Model\MyTable
        SET deleted =1
        WHERE
            member_id =:member_id:
            AND id IN ($ids)";
    return \Phalcon\DI::getDefault()['modelsManager']->executeQuery($phql, array(
        'member_id' => 1
    ));

It's not very elegant I know and you definitely need to validate the values in that array before doing this but it works :)



631
Accepted
answer

You can add bind parameters in IN clausele like this:

$ids = array(1, 2, 3);

// prepare in params and in bind in separated foreach
$in = array();
$inParams = array();
$index = 0;
foreach ($ids as $value) {
    $in[] = ":in" . $index . ':';
    $inParams['in' . $index] = $value;
    $index++;
}
$sqlParams = $inParams;

// append other sql parameters if you need
$sqlParams['member_id'] = 1;
$phql ="UPDATE \Model\MyTable
        SET deleted =1
        WHERE
            member_id =:member_id:
            AND id IN (" . implode(',',$in) . ")";

return \Phalcon\DI::getDefault()['modelsManager']->executeQuery($phql, $sqlParams);


634

Thks~ but just a little inconvenience.