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

Question

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 :)

Answer 1

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() );