How to initialize and prepare MySQL statements using the mysqli::stmt_init function? Detailed explanation of the basic usage of mysqli::stmt_init

M66 2025-05-29

In PHP, the mysqli extension provides powerful MySQL database operation functions, where the mysqli::stmt_init function is a key function used to initialize a preprocessing statement. This article will explain in detail how to initialize and prepare MySQL statements using the mysqli::stmt_init function.

What are preprocessing statements?

Preprocessing statements are a technology provided by MySQL to improve SQL execution efficiency and security. Using preprocessing statements, SQL queries are sent to the database server for parsing, and data is passed in when executed. This not only improves execution efficiency, but also effectively prevents security issues such as SQL injection.

In PHP, when using the mysqli extension for database operations, you can initialize a preprocessing statement object through the stmt_init method, and then prepare the SQL statement through the prepare() method.

Basic usage of mysqli::stmt_init function

The mysqli::stmt_init function is used to create a mysqli_stmt object, which is necessary to execute preprocessing statements.

grammar

 $stmt = $mysqli->stmt_init();

Here $mysqli is a valid MySQLi connection object.

Example: Initialize preprocessing statements using stmt_init function

 <?php
// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database_name");

// Check if the connection is successful
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Initialize a preprocessing statement object
$stmt = $mysqli->stmt_init();

// Check whether the initialization is successful
if ($stmt === false) {
    die("Failed to initialize statement.");
}

// Prepare SQL Statement
$query = "SELECT id, name FROM users WHERE email = ?";
if ($stmt->prepare($query)) {
    // Bind parameters
    $stmt->bind_param("s", $email); // 's' Represents the string type

    // Set parameter values
    $email = "user@m66.net";

    // 执行Statement
    $stmt->execute();

    // Binding results
    $stmt->bind_result($id, $name);

    // Get query results
    while ($stmt->fetch()) {
        echo "ID: $id, Name: $name\n";
    }

    // 关闭Statement
    $stmt->close();
} else {
    echo "Failed to prepare statement.";
}

// 关闭database连接
$mysqli->close();
?>

Detailed step analysis

Connect to the database Use new mysqli() to connect to the MySQL database and check if the connection is successful.
Initialize preprocessing statements Call $mysqli->stmt_init() to initialize a preprocessing statement object $stmt . If false is returned, it means that initialization failed.
Prepare SQL statements Use the $stmt->prepare($query) method to prepare the SQL statement. ? is a placeholder that represents the parameters in the query.
Bind parameters Use the $stmt->bind_param() method to bind the actual parameters to the placeholder in the preprocessing statement. "s" means that this parameter is a string type.
Execution statement Use $stmt->execute() to execute SQL queries.
Binding result Use $stmt->bind_result() to bind the query result to a PHP variable.
Get query results Use $stmt->fetch() to get the query result and output it through the bound variable.
Close statements and connections Use $stmt->close() to close the preprocessing statement object, and finally use $mysqli->close() to close the database connection.

Why use mysqli::stmt_init ?

Improve performance Preprocessing statements can greatly improve the efficiency of executing the same query multiple times by performing the SQL query analysis process in advance.
Prevent SQL injection By binding parameters, mysqli can effectively avoid SQL injection attacks.
Easy to manage and maintain Use the stmt_init function to initialize the statement object, making the code structure clearer and easier to maintain.

Related Tags:
mysqli