stmt_init how to work with num_rows or affected_rows to obtain results

When using the MySQLi extension of PHP for database operations, mysqli::stmt_init() is an important method, which is used to initialize a statement object ( mysqli_stmt ), and then prepares to execute SQL statements. To determine if the query statement has result returned, or how many rows are affected, use num_rows (for SELECT statements) or affected_rows (for INSERT , UPDATE or DELETE ).

This article will use examples to illustrate how to use mysqli::stmt_init() and combine num_rows and affected_rows to get the impact of query results.

1. Preparation: Database connection

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

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

2. Use stmt_init to initialize the statement and prepare for the query

 $stmt = $mysqli->stmt_init();

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

    $stmt->store_result(); // This step is a must，otherwise num_rows Will always return 0
    echo "Number of rows returned：" . $stmt->num_rows . "<br>";

    if ($stmt->num_rows > 0) {
        $stmt->bind_result($id, $name);
        while ($stmt->fetch()) {
            echo "userID: $id, Name: $name<br>";
        }
    } else {
        echo "No records that meet the criteria。";
    }

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

3. Use affected_rows to get the number of rows affected by update, insert or delete

 $stmt = $mysqli->stmt_init();

$sql = "UPDATE users SET status = ? WHERE last_login < ?";
if ($stmt->prepare($sql)) {
    $new_status = 'inactive';
    $cutoff_date = '2024-01-01';
    $stmt->bind_param("ss", $new_status, $cutoff_date);
    $stmt->execute();

    echo "Number of affected rows：" . $stmt->affected_rows;

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

4. Things to note

1. When using num_rows , store_result() must be called otherwise it will always return 0.

2. affected_rows can be read directly without calling store_result() .

3. After using the statement object, remember to call $stmt->close() to release the resource.

4. To debug or view error messages, use $stmt->error or $mysqli->error .

V. Conclusion

By rationally using mysqli::stmt_init() and combining num_rows and affected_rows , we can manage feedback and result processing of database queries more efficiently. This is critical to developing stable and performing PHP applications.