Php method set for calling MySQL stored procedure (recommended)

This article collects and summarizes the methods for php to call the MySQL stored procedure. For more information, see Type 1: Call methods with input and output parameters

The code is as follows:

$ ReturnValue = '';
Try {
Mysql_query ("set @ Return ");
$ Spname = 'p _ Test_GetInfo1 ';
Mysql_query ("call $ spname (@ Return, '{$ userId}', '{$ pwd}')") or die ("[$ spname] Query failed :". mysql_error ());
$ Result_return = mysql_query ("select @ Return ");
$ Row_return = mysql_fetch_row ($ result_return );
$ ReturnValue = $ row_return [0];
} Catch (Exception $ e ){
Echo $ e;
}
Echo $ returnValue; // output the variables output from the stored procedure

Type 2: Call a method with multiple output types and multiple input type parameters

The code is as follows:

$ UserId = 0;
Try {
Mysql_query ("set @ Message ");
Mysql_query ("set @ Id ");
Mysql_query ("call P _ Test_Login (@ Message, @ Id, '{$ userId}', '{$ pwd}')", $ conn) or die ("Query failed :". mysql_error ());
$ Result_mess = mysql_query ("select @ Message ");
$ Result_uid = mysql_query ("select @ Id ");
$ Row_mess = mysql_fetch_row ($ result_mess );
$ Row_uid = mysql_fetch_row ($ result_uid );
$ Proc_Error = $ row_mess [0];
$ UId = $ row_uid [0];
}
Catch (Exception $ e)
{
Echo $ e;
}
Echo 'proc return message: '$ Proc_Error .'
'; // Output the variables output from the stored procedure
Echo 'user id: '. $ uId; // obtain the User id

Type 3: Call a method with a returned result set

The code is as follows:

Try {
$ Spname = 'p _ Test_GetData ';
$ Query = mysql_query ("call $ spname ()", $ conn) or die ("[$ spname] Query failed:". mysql_error ());
While ($ row = mysql_fetch_array ($ query )){
Echo $ row ['provinceid']. ':'. $ row ['provincename']; // output the dataset
}

} Catch (Exception $ e ){
Echo $ e;
}

Type 4: Call a method with multiple returned result sets (currently only implemented through mysqli ~~)

The code is as follows:

// PHP
$ Rows = array ();
$ Db = new mysqli ($ server, $ user, $ psd, $ dbname );
If (mysqli_connect_errno ()){
$ This-> message ('Can not connect to MySQL server ');
}
$ Db-> query ("set names UTF8 ");
$ Db-> query ("SET @ Message ");
If ($ db-> real_query ("call P _ Test_GetData2 (@ Message )")){
Do {
If ($ result = $ db-> store_result ()){
While ($ row = $ result-> fetch_assoc ()){
Array_push ($ rows, $ row );
}
$ Result-> close ();
}
} While ($ db-> next_result ());
}
$ Db-> close ();
Print_r ($ rows );
// Procedure
......
Select * from T1 where ......
Select * from T2 where ......
......