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?
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.
my_table structure, id is PK
id, member_id, deleted
1 1 0
2 1 0
3 1 0
4 2 1
5 2 0This point is, when i use raw sql is fine, but placeholder fail
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 :)
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);Thks~ but just a little inconvenience.