Example to explain how to use MySQL stored procedures in PHP

Introduction to MySQL stored procedures and examples used in PHP

MySQL stored procedures are a powerful feature in the MySQL database. It can run independently of the application, execute a series of SQL statements and return results, thereby simplifying application development; it can also effectively reduce server pressure and improve database performance. This article will explain the basic knowledge related to MySQL stored procedures and provide sample code for using MySQL stored procedures in PHP.

1. Basic knowledge of MySQL stored procedures

MySQL stored procedures are a collection of precompiled SQL statements used to complete specific functional tasks. Unlike functions, stored procedures can accept input parameters and return values, and can be shared among different clients.

The syntax of MySQL stored procedures is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name datatype [, ...])
BEGIN
    -- 执行内容
END;

CREATE PROCEDURE means creating a stored procedure; OR REPLACE is optional, indicating that if a stored procedure with the same name already exists, replace the original stored procedure; procedure_name Indicates the name of the stored procedure; parameter_name indicates the input/output parameter name of the stored procedure; datatype indicates the data type of the parameter.

The following example shows how to write a simple MySQL stored procedure:

CREATE PROCEDURE my_procedure(IN name VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE name = name;
END;

2. Using MySQL stored procedures in PHP

Using MySQL stored procedures in PHP is very Conveniently, PHP provides two interfaces, mysqli and PDO, for us to call MySQL stored procedures. Here we take mysqli as an example to introduce and provide a sample code for reference.

First, we need to connect to the MySQL database, the specific method is as follows:

$mysqli = new mysqli("localhost", "root", "", "test");
if ($mysqli->connect_error) {
    die("连接失败: " . $mysqli->connect_error);
}

Next, we need to call the stored procedure and pass the parameters. Mysqli provides two methods, namely bind_param and call_user_func_array. Here we take bind_param as an example to illustrate.

// 准备参数
$name = "Tom";

// 准备调用存储过程的语句
$stmt = $mysqli->prepare("CALL my_procedure(?)");
$stmt->bind_param("s", $name); // 绑定参数

// 执行存储过程
$stmt->execute();

// 处理返回结果
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['id'] . " " . $row['name'] . " " . $row['age'] . "<br/>";
}

// 释放资源
$stmt->close();
$mysqli->close();

In the above code, we call the my_procedure stored procedure and pass a string type parameter. The first parameter of bind_param is the parameter type. Here we use s to represent the string type. For details, please refer to the PHP official documentation. After executing the stored procedure, we get the returned result set through get_result, and then output each row of data in turn.

Summary

This article introduces the basics of MySQL stored procedures, as well as sample code for using MySQL stored procedures in PHP. Using stored procedures allows us to process SQL statements more efficiently, improve database performance, and also facilitate interaction with applications. In actual development, we can choose to use stored procedures or other methods to operate the database according to specific scenarios.

The above is the detailed content of Example to explain how to use MySQL stored procedures in PHP. For more information, please follow other related articles on the PHP Chinese website!