Just a quick note to try and stop other peoples misery while using the newer MySQL client (mySQLi) and executing multiple stored procedures.
My ENVs: PHP5 / MySQL 5 / Window 2003 Server / mySQLi
If you are failing to understand why you can’t execute a stored procedure twice, here’s the answer:
The Stored Procedure:
DELIMITER $$
CREATE
PROCEDURE `testdb`.`getCategoryInfo`(thisCatid INT)
BEGIN
SELECT catid, parentcatid, catname, img
FROM cats
WHERE catid = thisCatId;
END$$
DELIMITER ;
The stored procedure we are trying to call (getCategoryInfo()) accepts one input as an integer and will return one row only.
The Table
The table we are running the procedure on has four columns: catid INT Primary Key, parentcatid INT, catname VARCHAR, img VARCHAR.
Code that will not work to call a Stored Procedure multiple times:
This should be roughly how your query code looks right now (provided your using the object oriantated ‘style’):
//Create our mySQLi object
$mySQLi =new mysqli($host,$username,$password);
//Select Our Database
$mySQLi->select_db($SQLConnectionInfo['database']);
//Execute our first query
$result = $mySQLi->query(“CALL getCategoryInfo(1)”);
//Catch our first error (if any)
if($result == false)
{
die(“mySQLi Error: “.$this->_mySQLi->error);
}
//Fetch our first record as and associative array
$finfo = $result->fetch_assoc();
//Close first result
$result->close();
//Print our first array
print_r($finfo);
//Execute our second query
$result = $mySQLi->query(“CALL getCategoryInfo(2)”);
//Catch our second error (if any)
if($result == false)
{
die(“mySQLi Error: “.$this->_mySQLi->error);
}
//Fetch our second record as and associative array
$finfo = $result->fetch_assoc();
//Close second result
$result->close();
//Print our second array
print_r($finfo);
This code will produce:
Array ( [catid] => 3 [parentcatid] => 1 [catname] => Mountain Bikes [img] => noimage.png ) mySQLi Error: Commands out of sync; you can’t run this command now
The code that will work:
The code highlighted bold is all that has been changed between the first and second examples:
//Create our mySQLi object
$mySQLi =new mysqli($host,$username,$password);
//Select Our Database
$mySQLi->select_db($SQLConnectionInfo['database']);
//Execute our first query
$result = $mySQLi->query(“CALL getCategoryInfo(1)”);
//Catch our first error (if any)
if($result == false)
{
die(“mySQLi Error: “.$this->_mySQLi->error);
}
//Fetch our first record as and�associative array
$finfo = $result->fetch_assoc();
//Close first result
$result->close();
//Clear the remainder of the result set out
$mySQLi->next_result();
//Print our first array
print_r($finfo);
//Execute our second query
$result = $mySQLi->query(“CALL getCategoryInfo(2)”);
//Catch our second error (if any)
if($result == false)
{
die(“mySQLi Error: “.$this->_mySQLi->error);
}
//Fetch our second record as and associative array
$finfo = $result->fetch_assoc();
//Close second result
$result->close();
//Print our second array
print_r($finfo);
This code will produce (as anticipated):
Array ( [catid] => 1 [parentcatid] => 0 [catname] => Bikes [img] => noimage.png ) Array ( [catid] => 2 [parentcatid] => 0 [catname] => Bike Accessories [img] => noimage.png )
The Reason
In the first example (the code that won’t work) we are sending the second query before dealing with the entire contents of the first! The one line highlighted in the second example reads the remainder of the rowset thus allowing us to perform another query.
Goto the MySQL support forums for more info, this is where I found the solution!
MySQL Closed Bug on this: http://bugs.mysql.com/bug.php?id=14993
