This article introduces how to call the stored procedure of mysql5 in php. Why do we only talk about mysql5? The reason is that only mysql5.0 and later versions support the stored procedure, next we will look at it from the beginning.
1. The method that calls the stored procedure.
A. If the stored procedure has the IN/INOUT parameter, declare a variable and input the parameter to the stored procedure. The variable is a pair,
A php variable (or not required, but there is no way to enter the php variable dynamically), 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: |
Copy code |
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: |
Copy code |
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.
If the time function. You can directly execute select function.
The Code is as follows: |
Copy code |
$ Host = "localhost "; $ User = "root "; $ Password = "11212 "; $ Db = "samp_db "; $ Dblink = mysql_connect ($ host, $ user, $ password) Or die ("can't connect to mysql "); Mysql_select_db ($ db, $ dblink) Or die ("can't select samp_db "); $ Res = mysql_query ("set @ a = $ password", $ dblink ); $ Res = mysql_query ("call aa (@ a)", $ dblink ); $ Res = mysql_query ("select @ a", $ dblink ); $ Row = mysql_fetch_row ($ res ); Echo $ row [0]; |
Find an instance from the Internet
The Code is as follows: |
Copy code |
<? Php /* Connect to a MySQL server */ $ Link = mysqli_connect ( 'Localhost',/* The host to connect */ 'Root',/* The user to connect */ 'Root',/* The password to use */ 'Db _ name');/* The default database to query */ If (! $ Link ){ Printf ("Can't connect to MySQL Server. Errorcode: % sn", mysqli_connect_error ()); Exit; } /* Send a query to the server */ If ($ result = mysqli_query ($ link, "call se_proc ('crm ')")){ /* Fetch the results of the query */ While ($ row = mysqli_fetch_array ($ result )){ Echo ($ row [0]. "--------- SR.". $ row [1]." "); } /* Destroy the result set and free the memory used for it */ Mysqli_free_result ($ result ); } /* Close the connection */ Mysqli_close ($ link ); ?> |
This query will return a data set.