Note: The code below will not work on all environments. I’m using php 5.2.6 with mysql driver 5.0.18.
In a previous post:
http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/
I explained how to use MySQL stored procedures with different db adapters such as mysql, mysqli, and pdo in PHP. In those examples, I demonstrated calling a sp with in/out variables and calling a stored procedure that returns a recordset. One of the comments I received was a person asking how to call a stored procedure that uses in/out parameters as well as returns a recordset. It’s not much different and here’s how.
The trick is to combine both methods in one. Here’s an example of what the stored procedure looks like:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users` $$
CREATE PROCEDURE `get_users`(
IN firstName VARCHAR(100),
OUT totalUsers INT
)
BEGIN
SELECT COUNT(*)
INTO totalUsers
FROM users
WHERE first_name = firstName;
SELECT *
FROM users
WHERE first_name = firstName;
END $$
DELIMITER ;
Continue reading “Using MySQL Stored Procedure IN/OUT and Recordset w/ PHP”