Hey Peter,
I'm not necessarily saying that this is the "best method" because my
interpretation of the ZF's manner of handling DB access is rather dynamic :)
For the stored procedure method, it would be as simple as:
[SQL]
CHARSET utf8;
DELIMITER $$
/**
* Gets users with the same first name.
*/
DROP PROCEDURE IF EXISTS `getUsers`$$
CREATE PROCEDURE `getUsers` (in name INT)
BEGIN
SELECT `user_id` FROM `users` `u` WHERE `u`.`first_name` = `name`;
END$$
[/SQL]
Then, in your code, you would:
[PHP]
class myTable extends Zend_Db_Table_Abstract
{
protected $_name = 'myTable';
public function getUsers($name)
{
return $this->_db->fetchAll("CALL myProcedure(?)", array($name));
}
}
$table = new myTable;
var_dump($table->getUsers('bob'));
?>
Outputs:
array
1,
5,
9
[/PHP]
Some caveats:
- Don't use the same input parameter as a column name in your database. MySQL
will get confused as to which is the right one and return all rows, no matter
your input (learned this the hard way :P)
- If you are working in an environment where multiple people are on the same
project and using the same DB, things can get a little hairy when you update
your stored procedure for your purposes and don't keep good communication with
the team. Others working on the project that deal with that query can end up
working with a b0rked system if the stored procedure fails.
More info on stored procs can be found here:
http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Hope this helps,
-Kizano
//-----
Information Security
eMail: [email protected]
http://www.markizano.net/
On 03/10/2011 11:05 PM, Peter Sharp wrote:
I've been working with ZF for a while now, but for some odd reason, I have
never really come across too many situations where I couldn't get a query to
way the way I liked right out of the box, probably because most of the
complexity was in the selects.
Anyhow, I have a query along the lines of this:
UPDATE
tbl_unit_size AS size_1, tbl_unit_size AS size_2
SET
size_1.sort_order = size_2.sort_order
WHERE
( size_1.ID IN (21,22) )
AND ( size_2.ID IN (21,22) )
AND ( size_1.ID<> size_2.ID)
;
Basically, it just swaps the sort order of two rows, in this case rows with
the ID 21 and 22.
Anyhow, I cant see a way to make the dbTable->update() do the trick, so that
leaves me with either just making a zend_db_statement, something like this:
$sql = "UPDATE
tbl_vendor_size AS size_1, tbl_vendor_size AS size_2
SET
size_1.sort_order = size_2.sort_order
WHERE
( size_1.VS_PK IN (:orig,:dest) )
AND ( size_2.VS_PK IN (:orig,:dest) )
AND ( size_1.VS_PK<> size_2.VS_PK );";
$data = array(':orig' => 21, ':dest' => 22);
$query = $this->getDbTable()->getAdapter()->query($sql, $data);
$query->execute();
Or would this be better in a stored procedure, in which case I don't know
how that would be accessed, or how you would know it worked afterwards.
How would you go about this, from a best practices perspective?
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/Complex-UPDATE-statement-Best-method-tp3347426p3347426.html
Sent from the Zend Framework mailing list archive at Nabble.com.
