English
English
In PHP, the mysqli extension provides multiple ways to interact with a database. Among them, the mysqli::stmt_init function is a very useful tool that can be used to initialize a prepared statement. With this function, you can execute stored procedures more safely and efficiently and process the returned results. This article will explain in detail how to use mysqli::stmt_init to execute stored procedures and process results.
First, make sure your database has created stored procedures. Suppose we have a stored procedure called get_user_info which returns the user's name and email based on the user's ID. The SQL code for stored procedures is as follows:
DELIMITER $$
CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
SELECT name, email FROM users WHERE id = user_id;
END$$
DELIMITER ;
In PHP, we will use mysqli::stmt_init to initialize a statement object and execute stored procedures through that object.
First, you need to connect to the database using mysqli extension:
<?php
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'test_db';
$mysqli = new mysqli($host, $username, $password, $database);
if ($mysqli->connect_error) {
die('Connection failed: ' . $mysqli->connect_error);
}
?>
The mysqli::stmt_init function is used to initialize a mysqli_stmt object. In this example, we will initialize a statement object to execute the get_user_info stored procedure.
<?php
// Initialize statement object
$stmt = $mysqli->stmt_init();
// Check whether the initialization is successful
if (!$stmt) {
die('Unable to initialize statements: ' . $mysqli->error);
}
?>
Once the statement object is initialized, we can use the prepare method to prepare the stored procedure's SQL statement. When executing the stored procedure, we will pass the parameters (in this example the user ID):
<?php
// Prepare the stored procedure
$query = "CALL get_user_info(?)";
if (!$stmt->prepare($query)) {
die('Stored procedure preparation failed: ' . $stmt->error);
}
// Bind parameters
$user_id = 1; // Suppose we want to query the user ID for 1 Information
$stmt->bind_param("i", $user_id); // "i" express integer type
// Execute stored procedures
if (!$stmt->execute()) {
die('Stored procedure execution failed: ' . $stmt->error);
}
?>
After the stored procedure is executed, the result set is usually returned. Here, we need to obtain the execution result through the get_result method and process it.
<?php
// Get query results
$result = $stmt->get_result();
if ($result->num_rows > 0) {
// Output each line of data
while ($row = $result->fetch_assoc()) {
echo "Name: " . $row['name'] . "<br>";
echo "e-mail: " . $row['email'] . "<br>";
}
} else {
echo "No relevant records were found";
}
?>
Finally, don't forget to close the statement object and database connection:
<?php
$stmt->close();
$mysqli->close();
?>
In a production environment, it is important to ensure proper error handling. You can catch the error by checking the return values of prepare , execute , and get_result methods. In addition, you can also use the try-catch statement to catch exceptions to ensure that the program can exit gracefully when an error occurs.
Through the mysqli::stmt_init function, you can execute stored procedures safely and effectively, and obtain functions through parameter binding and result sets to flexibly process the returned data. Using prepared statements not only prevents SQL injection, but also improves execution efficiency. Hopefully this article can help you better understand and use mysqli extensions to operate stored procedures.
