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