OK... let me post a complete example and the results... maybe could be good for someone else
First... Here is a simple stored procedure:
Code: Select all
DELIMITER $$
DROP PROCEDURE IF EXISTS `p_test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`()
BEGIN
SELECT "First-Select" FROM dual;
SELECT "Second-Select", "Other-Result" FROM dual;
END $$
DELIMITER ;
Notice how this procedure has 2 selects returning 1 and 2 values respectively.
Here is the msqli Multi-Query implementation to get those values correctly
Code: Select all
<?php
/*** Connection Parameters ***/
$hostname = 'localhost';
$username = 'you-username';
$password = 'your-password';
$dbname = 'your-database';
/*** create a new mysqli object with defined database***/
$link = @new mysqli($hostname, $username, $password, $dbname);
if (mysqli_connect_errno())
{
printf("Connection failed: %s\n", mysqli_connect_error());
exit();
}
// Here I define the Query... notice how its is calling the SP and in addition add other new SELECT sentence
// So basically I need to manage the results of 3 SQL sentences
$query = "CALL p_test();";
$query .= "SELECT 123 FROM dual";
$i = 0;
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* This line shows the iteration... only to see the effect... can be deleted */
echo "<br />" . "DO While Iteration = " . $i++ . "<br />";
/* store result set */
if ($result = mysqli_store_result($link)) {
// This Shows the ResultSet... can be delete also
echo "<pre>";
print_r($result);
echo "</pre>";
// Process ResultSet
while ($row = mysqli_fetch_row($result)) {
for ( $counter = 0; $counter <= sizeof($row); $counter++) {
printf("%s %s %s\n", "Inner while counter ---> ", $counter, $row[$counter]);
echo "<br />";
}
}
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>
HERE are the results:
[text]Value i = 0
mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> First-Select
Inner while --->
Value i = 1
mysqli_result Object
(
[current_field] => 0
[field_count] => 2
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> Second-Select
Inner while ---> Other-Result
Inner while --->
Value i = 2
Value i = 3
mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] =>
[num_rows] => 1
[type] => 0
)
Inner while ---> 123
Inner while --->
................FINISHED ...............[/text]
Analyzing the code and the results you sure will have a better understanding about how it works.
Hope this help
Miko