Current Location: Home> Latest Articles> Detailed explanation of the use of stmt_init and fetch() in conjunction with

Detailed explanation of the use of stmt_init and fetch() in conjunction with

M66 2025-05-16

When using PHP to operate a MySQL database, the mysqli extension provides an object-oriented interface that allows us to execute SQL statements more securely and structuredly. Especially when executing Prepared Statements, mysqli::stmt_init and fetch() are two very critical functions.

This article will introduce in detail how to use mysqli::stmt_init() to initialize statement objects, and combine the fetch() method to safely and efficiently obtain data in the database.

1. What is mysqli::stmt_init ?

mysqli::stmt_init() is a method of the mysqli class, used to initialize a statement object ( mysqli_stmt ). This object can then prepare SQL statements through the prepare() method. The advantage of this is that it can reuse statements and bind parameters to improve the security and efficiency of the code.

Example:

 $mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

$stmt = $mysqli->stmt_init();

2. Prepare and execute SQL statements

After creating a statement object with stmt_init() , prepare the query statement using prepare() and bind the parameters using bind_param() .

 $query = "SELECT id, name, email FROM users WHERE status = ?";
if ($stmt->prepare($query)) {
    $status = 'active';
    $stmt->bind_param("s", $status);
    $stmt->execute();
}

Tip: The first parameter "s" of bind_param means that the parameter type is a string ( string ).

3. Bind the results and use fetch() to extract data

After executing the statement, we need to use bind_result() to bind the column of the query result to the variable, and then we can use the fetch() method to extract the data row by row.

 $stmt->bind_result($id, $name, $email);

while ($stmt->fetch()) {
    echo "userID: $id<br>";
    echo "user名: $name<br>";
    echo "Mail: $email<br><br>";
}

4. Complete example

Here is a complete PHP sample program:

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

$stmt = $mysqli->stmt_init();
$query = "SELECT id, name, email FROM users WHERE status = ?";

if ($stmt->prepare($query)) {
    $status = 'active';
    $stmt->bind_param("s", $status);
    $stmt->execute();
    $stmt->bind_result($id, $name, $email);

    echo "<h2>活跃user列表：</h2>";
    while ($stmt->fetch()) {
        echo "<div>";
        echo "<strong>ID:</strong> $id<br>";
        echo "<strong>Name:</strong> $name<br>";
        echo "<strong>Mail:</strong> <a href='mailto:$email'>$email</a><br>";
        echo "<a href='https://m66.net/user/profile.php?id=$id'>check the details</a>";
        echo "</div><hr>";
    }

    $stmt->close();
} else {
    echo "SQL Preprocessing failed: " . $stmt->error;
}

$mysqli->close();
?>
Related