PHP method to call MySQL stored procedure and return value, mysql stored procedure_PHP tutorial

WBOY

Release： 2016-07-13 10:10:37

Original

988 people have browsed it

PHP method to call MySQL stored procedure and return value, mysql stored procedure

The example 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. I would like to share it with you for your reference. The specific analysis is as follows:

Methods that call stored procedures.

a. If the stored procedure has IN/INOUT parameters, declare a variable and input the parameters to the stored procedure. The variable is a pair, a PHP variable, or not. However, if there is no PHP variable, there is no way to dynamically input a Mysql variable.

b. If the stored procedure has an OUT variable, declare a Mysql variable. The declaration of the mysql variable is special. The mysql server must know the existence of this variable. In fact, it means executing a mysql statement and entering set @mysqlvar=$phpvar;

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

The code is as follows:

Copy codeThe code is as follows:

mysql_query("set @mysqlvar=$pbpvar");

In this way, there is a variable in the mysql server, @mysqlar. If it is an IN parameter, then its value can be passed in from phpar.

For example, using the mysqli function instance, we can first create a stored procedure in mysql, the code is as follows:

Copy code 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)

Then write as follows in php, the code is as follows:

Copy code The code is as follows:

Employee listing

$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 ‘Employee_idSurnameFirstname');
                while ($row = $result_set->fetch_object()) {
                                                                                              printf("%s%s%sn", $row->employee_id, $row->surname, $row->firstname);
            }
} else {
                  printf("

Error:%d (%s) %sn", mysqli_errno($dbh),
mysqli_sqlstate($dbh), mysqli_error($dbh));
}
Print (" ");
$dbh->close();
}
?>

The core code is as follows:

Copy code The code is as follows:$result_set = $dbh->query("call employee_list( $dept_id )")

This employee_list is our mysql stored procedure.

I hope this article will be helpful to everyone’s PHP programming design.