Zend Database Adapter - Complex MySQL Query
- by Sonny
I have defined a function in my Navigation model that executes a query, and I was wondering if there's a more "Zendy" way of generating/executing the query. The query I'm using was proposed by Bill Karwin on another thread here for setting arbitrary record order. I tried using a prepared statement, but the values in the SIGN() function got quoted.
I'm using the PDO adapter for MySQL.
/**
*
*/
public function setPosition($parentId, $oldPosition, $newPosition)
{
$parentId = intval($parentId);
$oldPosition = intval($oldPosition);
$newPosition = intval($newPosition);
$this->getAdapter()->query("
UPDATE `navigation`
SET `position` = CASE `position`
WHEN $oldPosition THEN $newPosition
ELSE `position` + SIGN($oldPosition - $newPosition)
END
WHERE `parent_id` = $parentId
AND `position` BETWEEN LEAST($oldPosition, $newPosition)
AND GREATEST($oldPosition, $newPosition)
");
return $this;
}
