PHP calls the MySQL stored procedure and returns a value method _php tips

This example describes the PHP call to execute the MySQL stored procedure and then return the value returned by the stored procedure, shared for everyone to reference. The specific analysis is as follows:

The method that invokes the stored procedure.

A. If the stored procedure has in/inout parameters, declare a variable, input parameters to the stored procedure, the variable is a pair of PHP variables, also can not, just do not have PHP variables, there is no way to dynamic input, a MySQL variable.

B. If the stored procedure has out variables, declaring a MySQL variable, mysql variable declaration is more special, you must let the MySQL server know the existence of this variable, in fact, is to execute a MySQL statement, into the set @mysqlvar = $phpvar;

C. Use mysql_query ()/mysql_db_query () to execute the MySQL variable declaration statement.

The code is as follows:

Copy Code code as follows:

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

In this way, there is a variable in the MySQL server, @mysqlar, if the time in parameter, then its value can have phpar incoming.

example, using the Mysqli function example, we can first create a stored procedure in MySQL, the code is as follows:

Copy Code code as follows:

Mysql> delimiter//
Mysql> CREATE PROCEDURE employee_list (out param1 INT)
-> BEGIN
-> SELECT COUNT (*) into the param1 from T;
-> End
->//
Query OK, 0 rows Affected (0.00 sec)

Then write the following in PHP, the following code:

Copy Code code as follows:

<form method= "POST" >
<p>enter Department ID:
<input type= "text" name= "dept_id" size= "4" >
<input type= "Submit" name= "submit" value= "Submit" ><p>
</form>
<?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 (' <table border= "1" width= "30%" > <tr> ".
' <td>Employee_id</td><td>Surname</td><td>Firstname</td></tr> ');
while ($row = $result _set->fetch_object ()) {
printf ("<tr><td>%s</td><td>%s</td><td>%s</td></tr>n",
$row->employee_id, $row->surname, $row->firstname);
}
} else {
printf ("<p>error:%d (%s)%sn", Mysqli_errno ($DBH),
Mysqli_sqlstate ($DBH), Mysqli_error ($DBH));
}
Print ("</table>");
$DBH->close ();
}
?>

The core code is that the code is as follows:

Copy Code code as follows:

$result _set = $dbh->query ("Call Employee_list ($dept _id)")

This sentence employee_list is our MySQL stored procedure.

I hope this article will help you with your PHP program design.