Current Location: Home> Latest Articles> Examples of standard usage of mysqli::stmt_init and prepare()

Examples of standard usage of mysqli::stmt_init and prepare()

M66 2025-05-29

Prepared Statements are an important means to ensure data security and improve efficiency when using MySQLi extensions in PHP. This article will introduce in detail the correct usage of the mysqli::stmt_init function and prepare() method, and analyze it in combination with actual examples.

1. What is mysqli::stmt_init ?

mysqli::stmt_init is a method provided by MySQLi to initialize an empty mysqli_stmt (statement) object. This object can then be used to preprocess SQL statements.

The syntax is as follows:

 mysqli_stmt mysqli::stmt_init ( void )

It is usually used with the prepare() method to prepare SQL statements.

2. What is the prepare() method?

The prepare() method is used to prepare a SQL statement that will be executed later and can be bound using parameter. This can effectively prevent SQL injection attacks.

The syntax is as follows:

 bool mysqli_stmt::prepare ( string $query )

3. Detailed explanation of the use steps

Here is the standard process using mysqli::stmt_init and prepare() :

  1. Connect to the database

  2. Initialize the preprocessing statement object

  3. Prepare statement using prepare()

  4. Bind parameters (if any)

  5. Execution statement

  6. Get the result (if it is a query statement)

  7. Close statements and connections

4. Detailed code examples

Suppose we have a user table users with fields: id , username , email . We want to query the user's email address based on the user name.

 <?php
// first step：Establish a database connection
$mysqli = new mysqli("localhost", "db_user", "db_password", "db_name");

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

// Step 2：Initialize the preprocessing statement object
$stmt = $mysqli->stmt_init();

// Step 3：Prepare SQL Statement
$sql = "SELECT email FROM users WHERE username = ?";
if (!$stmt->prepare($sql)) {
    die("SQL Preprocessing failed: " . $stmt->error);
}

// Step 4：Bind parameters
$username = "testuser";
$stmt->bind_param("s", $username);  // "s" Represents the string type

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

// Step 6：Bind the results and get the data
$stmt->bind_result($email);
if ($stmt->fetch()) {
    echo "User email is: " . $email;
} else {
    echo "This user was not found。";
}

// Step 7：关闭Statement与连接
$stmt->close();
$mysqli->close();
?>

5. Frequently Asked Questions and Precautions

1. Why not write SQL directly with prepare() ?

Although SQL can be executed directly using the query() method, this is vulnerable to SQL injection attacks. Binding prepare() and parameter can effectively prevent this problem.

2. What is the difference between stmt_init() and prepare() ?

stmt_init() creates an empty statement object, and prepare() loads the SQL template into it. You can also skip stmt_init() and directly call $mysqli->prepare() to complete initialization and preparation in one step.

3. Can statement objects be reused?

Yes, as long as the SQL template structure is consistent, the statement object can be reused and the bound parameters can be changed.

6. Example: Insert data using POST form

The following example demonstrates how to submit data through a form and insert it safely into the database:

 <?php
$mysqli = new mysqli("localhost", "db_user", "db_password", "db_name");

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $username = $_POST["username"] ?? "";
    $email = $_POST["email"] ?? "";

    $stmt = $mysqli->stmt_init();
    if ($stmt->prepare("INSERT INTO users (username, email) VALUES (?, ?)")) {
        $stmt->bind_param("ss", $username, $email);
        if ($stmt->execute()) {
            echo "User registration successfully！";
        } else {
            echo "Execution failed：" . $stmt->error;
        }
        $stmt->close();
    }
}

$mysqli->close();
?>
<form method="POST" action="https://m66.net/register.php">
    username：<input type="text" name="username" required><br>
    Mail：<input type="email" name="email" required><br>
    <input type="submit" value="register">
</form>

7. Summary

The combination of mysqli::stmt_init and prepare() provides a powerful database security mechanism for PHP. Parameter binding not only prevents SQL injection, but also improves execution efficiency. In actual development, it is recommended to always use preprocessing statements to operate the database.

Want to continue to learn more about the advanced usage of bind_param() and bind_result() in MySQLi?

Related