Php call 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, for more information, see. mySQL from 5 .... 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. call the stored procedure method.

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 of phpar can be passed IN.

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, execute select @ ar and 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 obtain the result through functions such as mydql_fetch_row.

Below I have summarized some stored procedures that call the stored procedure without parameters. 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; // open source code phprm.com "; 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 a method 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; // outputs 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 echo 'user id:'. $ uId from the variable output in the stored procedure; // obtain the User id

Type 3: Call the method with the 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 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: The 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 myproce4 stored procedure $ 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. check the effect under cmd.

Example 5: use a variable stored procedure. 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 'Excellence '; else select 'unknown fractional '; end case; end; "; mysql_query ($ SQL); // Create a myproce6 stored procedure $ SQL =" call test. myproce6 (100); "; mysql_query ($ SQL );//

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

Example 7: loop statement, 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 myproce7 stored procedure $ 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 myproce9 stored procedure $ SQL = "call test. myproce9 (); "; mysql_query ($ SQL );//

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

Instance 10: delete the stored procedure. the code is as follows:

Mysql_query ("drop procedure if exists myproce"); // delete the stored procedure of test

Address:

Reprinted at will, but please attach the article address :-)