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

stored-procedures, zend-framework

Solution

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:

CREATE PROCEDURE MyProc(IN i INTEGER, OUT o INTEGER)
BEGIN
  SELECT i+10 INTO 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() );

Problem

I'm recently have learned to use Zend Framework. I did a simple CRUD application. But now I want to use a existing database for a more complex application and I want to know how I call a stored procedure in the Model, how to send parameters, how to read the results and store them in an array in PHP. Please. I appreciate any kind of help :)

Original source