How can I use a stored procedure in a MySql database with Zend Framework?

It's not too hard. Here's an example of a MySQL stored procedure with an IN parameter, an OUT parameter, and a result set:

CREATEPROCEDURE MyProc(IN i INTEGER, OUT o INTEGER)BEGINSELECT i+10INTO o;SELECT i, o;END

You can call this with the query() method, and pass a parameter:

$stmt =$db->query("CALL MyProc(?, @output)", array(25));
print_r($stmt->fetchAll());

The trick is that MySQL stored procs might return multiple result sets (if the proc had multiple SELECT queries for instance). So the API must advance through all result sets before you can execute another SQL query. Or else you get the "Commands out of sync" error.

If you use the PDO_MySQL adapter:

while($stmt->nextRowset()){}

If you use the MySQLi adapter, you'll find that Zend_Db_Statement_Mysqli doesn't implement nextRowset(), so you have to call the internal mysqli connection object:

while($db->getConnection()->next_result()){}

Once you clear the result sets, you can run subsequent SQL queries, for example to fetch the value of the procedure's OUT parameter:

$stmt =$db->query("SELECT @output");
print_r($stmt->fetchAll());