PHP calls the MySQL stored procedure and returns the value. PHP calls the MySQL stored procedure and returns the value. This document describes how to call and execute the mysql stored procedure in php and then return the value returned by the stored procedure, I would like to share with you how PHP calls the MySQL stored procedure and returns the value. mysql stored procedure
This example describes how to call and execute the mysql stored procedure in php and then return the value returned by the stored procedure. The specific analysis is as follows:
Method of calling 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 of php variables, which are not required, there is no way to perform Dynamic Input, a Mysql variable.
B. If there is an OUT variable in the stored procedure, declare a Mysql variable. the declaration of the mysql variable is special. you must let the mysql server know the existence of the variable, that is, 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:
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.
For example, using the mysqli function instance, we can first create a stored procedure in mysql, the code is as follows:
The code is as follows:
Mysql> delimiter //
Mysql> create procedure employee_list (OUT param1 INT)
-> BEGIN
-> Select count (*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
Write the following code in php:
The code is as follows:
Employee listing
<? Php
$ Hostname = "localhost ";
$ Username = "root ";
$ Password = "secret ";
$ Database = "prod ";
If (IsSet ($ _ POST ['submit ']) {
$ Dbh = new mysqli ($ hostname, $ username, $ password, $ database );
/* Check connection */
If (mysqli_connect_errno ()){
Printf ("Connect failed: % sn", mysqli_connect_error ());
Exit ();
}
$ Dept_id = $ _ POST ['dept_id'];
If ($ result_set = $ dbh-> query ("call employee_list ($ dept_id )")){
Print ('
Error: % d (% s) % sn ", mysqli_errno ($ dbh ),
Mysqli_sqlstate ($ dbh), mysqli_error ($ dbh ));
}
Print ("
'.'
Employee_id |
Surname |
Firstname |
');While ($ row = $ result_set-> fetch_object ()){Printf ("
% S |
% S |
% S |
N ",$ Row-> employee_id, $ row-> surname, $ row-> firstname );}} Else {Printf ("
");
$ Dbh-> close ();
}
?>
The core code is as follows:
The code is as follows:
$ Result_set = $ dbh-> query ("call employee_list ($ dept_id )")
The employee_list is our mysql stored procedure.
I hope this article will help you with php programming.
Examples in this article describes how to call and execute the mysql stored procedure in php and then return the value returned by the stored procedure...