PHP calls MySQL stored procedure and returns value implementation program_PHP tutorial

This article will give you a detailed introduction to how to call and execute the mysql stored procedure in PHP and then return the value returned by the stored procedure. Students who need to know more can refer to it.

. 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 (it is not necessary, but there is no way to perform dynamic input when there is no PHP variable) and 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. In fact, it means executing a mysql statement. Enter set @mysqlvar=$phpvar;

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

代码如下	复制代码
mysql_query("set @mysqlvar【=$pbpvar】");

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

Example

Use mysqli function instance

We can first create a stored procedure in mysql

The code is as follows

Copy code

代码如下	复制代码
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)

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)

然后在php中如下写

 代码如下

 代码如下

复制代码

Employee listing

Enter Department ID:

$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 ('

'.
               '');
          while ($row = $result_set->fetch_object()) {
               printf("n",
                      $row->employee_id, $row->surname, $row->firstname);
          }
     } else {
          printf("

Error:%d (%s) %sn", mysqli_errno($dbh),
                 mysqli_sqlstate($dbh), mysqli_error($dbh));
     }
     print ("

Employee_id	Surname	Firstname
%s	%s	%s

");
     $dbh->close();
}
?>

复制代码

代码如下	复制代码
$result_set = $dbh->query("call employee_list( $dept_id )")

Employee listing

Enter Department ID:

$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'];