How to call MySQL stored procedure in PHP

MySQL and PHP are technologies often used in Web development. Among them, stored procedures are an important feature of MySQL. In this article, we will introduce MySQL stored procedures and implement them in combination with PHP language.

1. What is a MySQL stored procedure?

MySQL stored procedure is a reusable SQL code block that can be defined and called in the program. It is similar to a function that accepts parameters and returns a value. Stored procedures can be stored on the MySQL server side and called from client code.

2. Advantages of stored procedures

• Improves code reusability and avoids repeated SQL statements;
• Enhances data security security, access control can be carried out through stored procedures;
• Increases performance, stored procedures are executed on the server side, reducing network communication overhead;
• can reduce database interaction, by calling multiple SQL statements reduce the burden on the database.

3. Basic syntax of MySQL stored procedures

The syntax of MySQL stored procedures is similar to functions and procedures in other languages. It consists of three keywords: CREATE, PROCEDURE and END. The CREATE PROCEDURE keyword is followed by the name and parameter list of the stored procedure, then the body of the stored procedure, and finally the END keyword.

The following is a simple MySQL stored procedure example:

CREATE PROCEDURE `get_employee`(IN id INT, OUT name VARCHAR(50), OUT age INT)
BEGIN
  SELECT `name`, `age` INTO name, age FROM `employee` WHERE `id` = id;
END

The above stored procedure accepts an id parameter, and queries the name and age corresponding to the id from the employee table, and then assigns them values. Output parameters for name and age.

4. Calling MySQL stored procedures in PHP

Calling MySQL stored procedures in PHP code is very simple. To call a stored procedure, you can use the CALL statement provided by the MySQLi extension or the PDO extension. The following is a sample code that uses the MySQLi extension to call a MySQL stored procedure:

// 配置连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 建立连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// 调用存储过程
$id = 1;
$stmt = $conn->prepare("CALL get_employee(?, ?, ?)");
$stmt->bind_param("i", $id);
$stmt->bind_result($name, $age);
$stmt->execute();
$stmt->fetch();
$stmt->close();

echo "Name: ".$name.", Age: ".$age;

The above code connects to the MySQL database, and then calls the stored procedure named get_employee through the CALL statement, passing in an id parameter. Then use the bind_result method to bind the output parameters of the stored procedure to PHP variables, and finally execute the stored procedure and use the fetch method to obtain the output parameter values.

5. Summary

MySQL stored procedures are a very practical feature that can improve code reusability, enhance data security, increase performance and reduce database interactions. It is also very easy to call MySQL stored procedures in PHP code. You only need to use the CALL statement provided by the MySQLi extension or PDO extension. Hopefully this article helps you better understand MySQL stored procedures and stored procedure calls in PHP.

The above is the detailed content of How to call MySQL stored procedure in PHP. For more information, please follow other related articles on the PHP Chinese website!