How to obtain the affected number of rows through PDOStatement::rowCount for conditional judgment

When using PHP to operate a database, we often need to know how many rows of data it affects after the execution of a SQL statement. For example, when performing UPDATE , DELETE , or some INSERT operations, understanding the number of affected rows helps us determine whether the operation is successful or whether the next step is required.

At this time, the PDOStatement::rowCount() function comes in handy. This article will introduce how to use the rowCount() method and combine actual code examples to demonstrate how to make conditional judgments based on the number of rows returned.

1. What is PDOStatement::rowCount()

rowCount() is a method of the PDOStatement class that returns the number of rows affected by the last executed SQL statement. The return value of this function is usually used to determine whether the data is actually updated or deleted.

 public int PDOStatement::rowCount();

Note: rowCount() may behave slightly differently under different database drivers, especially when used for SELECT queries, which may not return expected results.

2. Basic usage method

The following is an example of updating user information in the database. After executing SQL, we use rowCount() to determine whether any data has been actually modified.

 <?php
// Database connection information
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'dbuser';
$password = 'dbpass';

try {
    // create PDO Example
    $pdo = new PDO($dsn, $username, $password);

    // Set exception error handling
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare SQL Update statement
    $sql = "UPDATE users SET email = :email WHERE id = :id";
    $stmt = $pdo->prepare($sql);

    // Bind parameters
    $stmt->bindValue(':email', '[email protected]');
    $stmt->bindValue(':id', 1);

    // Execution statement
    $stmt->execute();

    // Get the number of affected rows
    $affectedRows = $stmt->rowCount();

    // Conditional judgment
    if ($affectedRows > 0) {
        echo "User information has been updated，Total impact {$affectedRows} OK。";
    } else {
        echo "No user information has been modified，Probably because the data has not changed。";
    }

} catch (PDOException $e) {
    echo "Database error：" . $e->getMessage();
}
?>

3. Common scenarios and usage techniques

1. Confirm the number of deletes after deletion operation

 $sql = "DELETE FROM users WHERE last_login < NOW() - INTERVAL 1 YEAR";
$stmt = $pdo->prepare($sql);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    echo "Inactive user has been successfully deleted。";
} else {
    echo "No user to be deleted was found。";
}

2. Check whether the insertion is successful after batch insertion

Some databases (such as MySQL) allow rowCount() to be returned after batch insertion:

 $sql = "INSERT INTO logs (message, created_at) VALUES 
       ('operate1', NOW()), 
       ('operate2', NOW())";
$stmt = $pdo->prepare($sql);
$stmt->execute();

if ($stmt->rowCount() >= 2) {
    echo "Logs have been written in batches。";
} else {
    echo "Log write failed or incomplete。";
}

4. Things to note

1. For SELECT queries, rowCount() behavior is uncertain : it is usually recommended to use fetchAll() or fetch() to determine whether there is a result.

2. Using the correct database driver : Some databases (such as SQLite) have limited support for rowCount() in some operations.

3. Don't mistake rowCount() as the only sign of successful execution : it only means "number of affected rows", and does not equal "successful operation", because updating the same data may not change the number of rows.