Could you please assist me with PHP 5.3 and MySQL 5.5 stored procedures and mysqli library + persistent connection

Helo,

I have a stored procedure that has 7 IN parameters and 3 OUT parameters. I need to pass 7 parameters IN from PHP, execute the query with procedure, and retrieve the 3 OUT parameters.

I am using mysqli with persistent connection mode enabled. (For load considerations) What is the most efficient way to execute the query and get results?

I need something that doesn't affect other concurrently running PHP scripts, and that cleans the result itself, and that is straightforward.

This is what my application is (simplified) (not a working example, just how i wish it was)

$inParam1 = 'wer';
$inParam2 = 'fewf';
$inParam3 = 'dsf';
$inParam4 = 'vccv';
$inParam5 = '34t3'; 
$inParam6 = 'ter';
$inParam7 = 'ert';
$query = "CALL my_procedure('$inParam1', '$inParam2', '$inParam3', '$inParam4', '$inParam5', '$inParam6', '$inParam7', @outParam8, @outParam9, @outParam10); SELECT @outParam8, @outParam9, @outParam10;";
$result = $mysql_query($query);
list($outParam1, $outParam2, $outParam3) = mysql_fetch_array($result);
echo $outParam1; // String param #1 that my procedure returned as an OUT variable
echo $outParam2; // String param #2 that my procedure returned as an OUT variable
echo $outParam3; // String param #3 that my procedure returned as an OUT variable

If somebody could show how this code could look in reality, please please would be great!

I am obviously using mysqli with proper connection, and such, but the examples I have found in internet are really confusing and seem to be inefficient, I 开发者_开发知识库am also worried if it will conflict with other clients, because it works like "nextResult" and other some strange functions.

Many thanks!

PHP 5.3, MySQL 5.5

---

Try looking here. As im not overly familiar with this. :)

http://www.mysqltutorial.org/stored-procedures-parameters.aspx

You need to create a query first. This query will then be stored in the database as a callable procedure. Then later using whatever language you can call the procedure.

DELIMITER //  
    CREATE PROCEDURE GetUserByCountry(IN countryName VARCHAR(255))  
        BEGIN 
            SELECT name, email   
            FROM users  
            WHERE country = countryName;  
        END //  
DELIMITER ;

Then calling it.

CALL GetUserByCountry('mexico')

Returns all users names and emails who live in mexico.

---

I believe if you want to create a dynamic query string such as yours, you need to put {} around your variables in the string.

$query = "CALL my_procedure('{$inParam1}', '{$inParam2'}, '{$inParam3}', '{$inParam4}', '{$inParam5}', '{$inParam6}', '{$inParam7}', @outParam8, @outParam9, @outParam10); SELECT @outParam8, @outParam9, @outParam10;";