PHP mysqli执行多条UPDATE语句时更新行数统计失效问题求助

I get it, tracking the number of rows updated across multiple queries can be tricky if you're not handling each step properly. Let's break down why this happens and how to fix it.

Common Causes for Inaccurate Row Counts #

• When using mysqli_multi_query() to run multiple statements at once, mysqli_affected_rows() will only return the rows affected by the last query unless you explicitly process each result set.
• If you're executing queries one by one but not storing the affected rows count for each individual query, you'll only have the count from the final query.
• For prepared statements (mysqli_stmt), you need to use mysqli_stmt_affected_rows() instead of the regular mysqli_affected_rows() for each statement.

Solutions #

1. Executing Queries Separately and Summing Counts #

If you're running each UPDATE query individually, capture the affected rows for each and add them up. Also, always use prepared statements for queries with user input to avoid SQL injection risks:

// Initialize total affected rows counter
$totalAffected = 0;
$conn = mysqli_connect("your_host", "your_user", "your_pass", "your_db");

// First UPDATE query
$firstUpdateQuery = "UPDATE `register` SET suser = '1'";
if (mysqli_query($conn, $firstUpdateQuery)) {
    $totalAffected += mysqli_affected_rows($conn);
} else {
    echo "First query failed: " . mysqli_error($conn);
}

// Second UPDATE query (using prepared statement for safety)
$secondUpdateQuery = "UPDATE `register` SET suser = ?, steamleader = ?, ipdate = ?, customer = ?, cperson1 = ?, mobile1 = ?, phone = ?, fax = ?, email = ?, website = ?, pincode = ?, state = ?, city = ?, address = ?, status = ?, data_resource = ?, comments = ?, data_status = ?";
$stmt = mysqli_prepare($conn, $secondUpdateQuery);

// Bind parameters (adjust types: s=string, i=int, d=float, b=blob)
mysqli_stmt_bind_param($stmt, "ssssssssssssssssss", $suser, $steamleader, $ipdate, $customer, $cperson1, $mobile1, $phone, $fax, $email, $website, $pincode, $state, $city, $address, $status, $data_resource, $comments, $data_status);

if (mysqli_stmt_execute($stmt)) {
    $totalAffected += mysqli_stmt_affected_rows($stmt);
} else {
    echo "Second query failed: " . mysqli_stmt_error($stmt);
}

// Clean up
mysqli_stmt_close($stmt);
mysqli_close($conn);

echo "Total rows updated: " . $totalAffected;

2. Using mysqli_multi_query() and Processing Each Result #

If you must run multiple queries in one call, loop through each result set to collect affected rows from every query. Note: This method is not recommended for queries with user input due to high SQL injection risk.

$conn = mysqli_connect("your_host", "your_user", "your_pass", "your_db");
$totalAffected = 0;

// Combine queries with semicolons (complete your second query fully)
$combinedQueries = "UPDATE `register` SET suser = '1'; UPDATE `register` SET suser = '$suser', steamleader = '$steamleader', ...;";

if (mysqli_multi_query($conn, $combinedQueries)) {
    do {
        // Add rows affected by current query to total
        $totalAffected += mysqli_affected_rows($conn);
        // Move to the next result set
    } while (mysqli_next_result($conn));
} else {
    echo "Query failed: " . mysqli_error($conn);
}

mysqli_close($conn);
echo "Total rows updated: " . $totalAffected;

Key Takeaways #

• For individual queries: Capture mysqli_affected_rows() (or mysqli_stmt_affected_rows() for prepared statements) after each execution and sum them.
• For multi_query: Use mysqli_next_result() to loop through all result sets and collect counts from every query.
• Always prioritize prepared statements when working with user input to keep your code secure and avoid SQL injection.

内容的提问来源于stack exchange，提问作者user9341437