Php calling MySQL stored procedure _ PHP Tutorial

Explain how php calls the MySQL stored procedure method. Php and mysql are a natural pair. next I will introduce how to call the mysql stored procedure and execute the returned results in php. below I will summarize some common php methods for calling the MySQL stored procedure, php and mysql are a natural pair. next I will introduce how to call the mysql stored procedure and execute the returned results in php. below I will summarize some common php methods for calling the MySQL stored procedure, for more information, see.

MySQL introduced the stored procedure from 5.0. it was never touched before when it was used for applications. However, because it is mainly used for internal systems, many applications use the stored procedure, of course, the front-end sometimes needs to call the MySQL stored procedure. the MySQL Lib in PHP does not seem to support very well, but I have searched some materials. although there are not many materials, I still tried to use them, now we will introduce the method

1. The method that calls the stored procedure.

A. If the stored procedure has an IN/INOUT parameter, declare a variable, and input the parameter to the stored procedure. the variable is a pair and a php variable (or not required, but when there is no php variable, there is no way to perform dynamic input), a Mysql variable.

B. If the stored procedure has an OUT variable, declare a Mysql variable. The declaration of mysql variables is special. the mysql server must know the existence of this variable, that is, to execute a mysql statement. Input set @ mysqlvar = $ phpvar;

C. Use mysql_query ()/mysql_db_query () to execute the mysql variable declaration statement.

The code is as follows:
Mysql_query ("set @ mysqlvar [= $ pbpvar ]");

In this way, there is a variable in the mysql server, @ mysqlar. If the IN parameter is used, the value can be phpar input.

D. If the stored procedure is used.

1. Execute the call procedure () statement.

That is, mysql_query ("call proceduer ([var1]...)");

2. if a return value exists, run select @ ar to return the execution result.

The code is as follows:
Mysql_query ("select @ var )"

The next operation is the same as executing a mysql statement in php. You can use functions such as mydql_fetch_row () to obtain the result.

Below I have summarized some stored procedures that call stored procedures without parameters for instances.

The code is as follows:

$ Conn = mysql_connect ('localhost', 'root', 'root') or die ("data connection error !!! "); Mysql_select_db ('test', $ conn ); $ SQL =" Create procedure myproce () Begin Insert into user (id, username, sex) VALUES (NULL,'s, '0 '); End; "; Mysql_query ($ SQL); // Create a myproce stored procedure $ SQL = "call test. myproce ();"; Mysql_query ($ SQL); // call the stored procedure of myproce, a new record will be added to the database.

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 ...... ......

Example 4: inout stored procedure of outgoing parameters

The code is as follows:
$ SQL =" Create procedure myproce4 (inout sexflag int) Begin SELECT * FROM user WHERE sex = sexflag; End; "; Mysql_query ($ SQL); // Create a stored procedure of myproce4 $ SQL = "set @ sexflag = 1 "; Mysql_query ($ SQL); // Set the gender parameter to 1. $ SQL = "call test. myproce4 (@ sexflag );"; Mysql_query ($ SQL); // call the stored procedure of myproce4. see the result in cmd.

Example 5: variable stored procedures

The code is as follows:
$ SQL =" Create procedure myproce5 (in a int, in B int) Begin Declare s int default 0; Set s = a + B; Select s; End; "; Mysql_query ($ SQL); // Create a myproce5 stored procedure $ SQL = "call test. myproce5 (4, 6 );"; Mysql_query ($ SQL );//

Call the stored procedure of myproce5 and check the effect in cmd.

Example 6: case Syntax

The code is as follows:
$ SQL =" Create procedure myproce6 (in score int) Begin Case score When 60 then select 'pass '; When 80 then select 'and '; When 100 then select 'excellent '; Else select 'unknown score '; End case; End; "; Mysql_query ($ SQL); // Create a stored procedure of myproce6 $ SQL = "call test. myproce6 (100 );"; Mysql_query ($ SQL );//

Call the stored procedure of myproce6 and check the effect in cmd.

Example 7: cyclic statements

The code is as follows:
$ SQL =" Create procedure myproce7 () Begin Declare I int default 0; Declare j int default 0; While I <10 do Set j = j + I; Set I = I + 1; End while; Select j; End; "; Mysql_query ($ SQL); // Create a stored procedure of myproce7 $ SQL = "call test. myproce7 ();"; Mysql_query ($ SQL );//

Call the stored procedure of myproce7 and check the effect in cmd.

Example 8: repeat statement

The code is as follows:
$ SQL =" Create procedure myproce8 () Begin Declare I int default 0; Declare j int default 0; Repeat Set j = j + I; Set I = I + 1; Until j> = 10 End repeat; Select j; End; "; Mysql_query ($ SQL); // Create a myproce8 stored procedure $ SQL = "call test. myproce8 ();"; Mysql_query ($ SQL );//

Call the stored procedure of myproce8 and check the effect in cmd.

Example 9: loop statement

The code is as follows:
$ SQL =" Create procedure myproce9 () Begin Declare I int default 0; Declare s int default 0; Loop_label: loop Set s = s + I; Set I = I + 1; If I> = 5 then Leave loop_label; End if; End loop; Select s; End; "; Mysql_query ($ SQL); // Create a stored procedure of myproce9 $ SQL = "call test. myproce9 ();"; Mysql_query ($ SQL );//

Call the stored procedure of myproce9 and check the effect in cmd.

Instance 10: Delete a stored procedure

The code is as follows:
Mysql_query ("drop procedure if exists myproce"); // delete the stored procedure of test

Instance 10: cursor in the stored procedure

Success ,...