CODEWITHSUNDEEP
zend-framework2
Hiren Dave
Hiren Dave

Reputation: 1

how to execute MYSQL stored procedure in zend framework 2 with multiple result set

How to execute MYSQL stored procedure in zend framework 2 with multiple result set means if sp have multiple select query then how can i get all the result in array and how i pass dynamic value in sp for insert and update data in table.

thanks..

Upvotes: 0

Views: 3559

Answers (1)

ba0708
ba0708

Reputation: 10599

I recently wrote a small article about this. The solution I found is not a generic one and assumes that you are using PDO. I am not sure whether it works with other databases than MySQL. It is possible that there is a better and more generic way of doing this that I am not aware of.

$driver = $this->dbAdapter->getDriver();
$connection = $driver->getConnection();

$result = $connection->execute('CALL sp_get_profile_for_display (123)');
$statement = $result->getResource();

// Result set 1
$resultSet1 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet1 as $row) {
    $something = $row->some_column;
}

// Result set 2
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet2 as $row) {
    /* Do something */
}

// Result set 3
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet3 as $row) {
    /* Do something */
}

Replace the 123 with the data you wish to pass to the stored procedure as a parameter. If using user supplied data, remember to escape it to prevent SQL injection!

Upvotes: 1

Related Questions