Address: http://www.pczpg.com/a/2010/0507/7815.html
Call the MySQL stored procedure in the same thing in PHP and then execute another or multiple commands (or execute multiple stored procedures in the same thing) again ), if you use the query method of mysqli to obtain the result, an error is returned: commands out of sync; you can't run this command now SSS first.Code:
Stored Procedure:
Create procedure test1 ()
Begin
Drop table if exists tb1;
Create Table tb1
(
Val int not null
) Engine = InnoDB;
Insert into tb1 (VAL) values (1), (2), (3 );
Select * From tb1;
End
PHP code:
<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");
If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
$ Result = NULL;
$ Mysqli-> autocommit (false );
If (! ($ Result = $ mysqli-> query ("Call test1 ();")))
{
Echo mysqli_error ($ link );
$ Mysqli-> rollback ();
}
$ Mysqli-> commit ();
Print 'result1 :';
While ($ ROW = $ result-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result-> close ();
Mysqli_free_result ($ result );
Echo 'result2: <br/> ';
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
Mysqli_free_result ($ result2 );
Mysqli_close ($ link );
?>
After the above code is executed, the above error will occur. The message indicates that the MySQL database considers the command execution order as this error. The reason is that after the execution of the MySQL stored procedure, in addition to returning the actual result set, it also returns the transition state of the stored procedure execution.
The above Code only processes the first result set, and the second result set is not released.
When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the select call inside the stored procedure. 2 ndfor the call of the stored procedure itself
(2nd usually is only an OK or err packet ).
To solve this problem, we need to use the mysqli multi_query method to traverse all the result sets and release them. The Code is as follows:
<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");
If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
Echo 'result1: <br/> ';
$ Mysqli-> autocommit (false );
If ($ mysqli-> multi_query ("Call test1 ();"))
{
Do {
If ($ result = $ mysqli-> store_result ()){
While ($ ROW = $ result-> fetch_row ()){
Printf ("% s \ n", $ row [0]);
}
$ Result-> close ();
}
} While ($ mysqli-> next_result ());
}
$ Mysqli-> commit ();
Echo "<br/> ";
Echo "result2: <br/> ";
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ()){
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
$ Mysqli-> close ();
?>
Call the MySQL stored procedure in the same thing in PHP and then execute another or multiple commands (or execute multiple stored procedures in the same thing) again ), if you use the query method of mysqli to obtain the result, you will get an error: commands out of sync; you can't run this command now SSS first to provide the Code:
Stored Procedure:
Create procedure test1 ()
Begin
Drop table if exists tb1;
Create Table tb1
(
Val int not null
) Engine = InnoDB;
Insert into tb1 (VAL) values (1), (2), (3 );
Select * From tb1;
End
PHP code:
Code1
<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");
If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
$ Result = NULL;
$ Mysqli-> autocommit (false );
If (! ($ Result = $ mysqli-> query ("Call test1 ();")))
{
Echo mysqli_error ($ link );
$ Mysqli-> rollback ();
}
$ Mysqli-> commit ();
Print 'result1 :';
While ($ ROW = $ result-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result-> close ();
Mysqli_free_result ($ result );
Echo 'result2: <br/> ';
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ())
{
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
Mysqli_free_result ($ result2 );
Mysqli_close ($ link );
?>
After the above code is executed, the above error will occur. The message indicates that the MySQL database considers the command execution order as this error. The reason is that after the execution of the MySQL stored procedure, in addition to returning the actual result set, it also returns the transition state of the stored procedure execution.
The above Code only processes the first result set, and the second result set is not released.
When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the select call inside the stored procedure. 2 ndfor the call of the stored procedure itself
(2nd usually is only an OK or err packet ).
To solve this problem, we need to use the mysqli multi_query method to traverse all the result sets and release them. The Code is as follows:
<? PHP
$ Mysqli = new mysqli ("localhost", "root", "sbqcel", "test ");
If (mysqli_connect_errno ())
{
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
Echo 'result1: <br/> ';
$ Mysqli-> autocommit (false );
If ($ mysqli-> multi_query ("Call test1 ();"))
{
Do {
If ($ result = $ mysqli-> store_result ()){
While ($ ROW = $ result-> fetch_row ()){
Printf ("% s \ n", $ row [0]);
}
$ Result-> close ();
}
} While ($ mysqli-> next_result ());
}
$ Mysqli-> commit ();
Echo "<br/> ";
Echo "result2: <br/> ";
If ($ result2 = $ mysqli-> query ("select Val from tb1 ;"))
{
While ($ ROW = $ result2-> fetch_row ()){
Printf ("% S <br/>", $ row [0]);
}
$ Result2-> close ();
}
Else
{
Echo $ mysqli-> error;
}
$ Mysqli-> close ();
?>
