How to implement MySQL stored procedures in PHP

1. Introduction to MySQL stored procedures

MySQL stored procedures are a programming language that encapsulates SQL statements and are mainly used in a single transaction Run the SQL statement. Stored procedures have many benefits in MySQL, such as:

1. increased performance. Using stored procedures can reduce the number of communications with the database, thereby improving database performance.

2. Improve security. Stored procedures can implement stricter permission control on data, making the database more secure in maintenance, management and use.

3. Improve maintainability. Stored procedures can encapsulate many complex SQL statements together, making maintenance and modification more convenient.

2. How to implement MySQL stored procedures in PHP

The following describes how to implement MySQL stored procedures in PHP.

1. Create MySQL stored procedure

First, we need to create a MySQL stored procedure. To create a new stored procedure, you can use tools such as MySQL Workbench or the MySQL command line. For example, we can write a simple MySQL stored procedure that accepts an ID as input and returns the corresponding user information. The specific code is as follows:

DELIMITER //
CREATE PROCEDURE `get_user_info`(IN `user_id` INT(11), OUT `user_name` VARCHAR(50), OUT `user_email` VARCHAR(100))
BEGIN
    SELECT `name`, `email` INTO `user_name`, `user_email` FROM `users` WHERE `id` = `user_id`;
END //
DELIMITER ;

The above code first uses the DELIMITER keyword to define the start and end tags of the stored procedure, and then defines a stored procedure named get_user_info, which has an input parameter user_id and The two output parameters user_name and user_email. This sentence can be rewritten as: a stored procedure that uses the SELECT command to retrieve user information from the database and then assigns it to the output parameters.

2. PHP connects to MySQL database

It is very simple to connect to MySQL database using PHP, just call the mysqli_connect() function. For example:

$db_connection = mysqli_connect("localhost", "user_name", "password", "database_name");

where localhost is the host name of the MySQL database, user_name and password are the username and password to log in to the MySQL database, and database_name is the name of the database to be connected.

3. Calling stored procedures in PHP

Calling stored procedures in PHP is very simple, just prepare a SQL statement and use mysqli_query() Just execute the function. For example:

$user_id = 123;
$user_name = "";
$user_email = "";
$stmt = $db_connection->prepare("CALL get_user_info(?, ?, ?)");
$stmt->bind_param("iss", $user_id, $user_name, $user_email);
$stmt->execute();
$stmt->bind_result($user_name, $user_email);
$stmt->fetch();
echo "User Name: " . $user_name . "\n";
echo "User Email: " . $user_email . "\n";

The above code first defines a $user_id variable as the input parameter of the stored procedure, uses the mysqli_prepare() function to prepare a SQL statement, and then uses the mysqli_stmt_bind_param() function to bind the input parameters to the SQL statement middle. Next, use the mysqli_stmt_execute() function to execute the SQL statement and bind the results to the variables $user_name and $user_email. The final step is to use the mysqli_stmt_fetch() function to extract the data in the result set and use the echo statement to output the results.

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