How to correctly use PDOStatement::rowCount to get the number of rows affected by an INSERT INTO statement

When operating databases using PHP, PDO is a recommended way to provide a unified, flexible and secure interface. Among them, the PDOStatement::rowCount() method is often used to obtain the number of rows affected by SQL operations, but it behaves slightly differently in different statement types (such as SELECT , INSERT , UPDATE , DELETE ). This article focuses on how to avoid common misunderstandings.

1. Applicability of rowCount() to INSERT

Usually we want to know how many lines are successfully inserted after performing the insert operation, such as:

 $pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

$stmt->bindValue(':name', 'Alice');
$stmt->bindValue(':email', '[email protected]');
$stmt->execute();

echo $stmt->rowCount(); // Expected output：1

In most cases, especially when using MySQL , rowCount() correctly returns the number of rows affected (usually 1) if a single INSERT statement is executed. But if you care about cross-database compatibility , you need to be careful.

2. RowCount behavior during batch insertion

For the following batch insert statements:

 $sql = "INSERT INTO users (name, email) VALUES 
        ('Bob', '[email protected]'), 
        ('Charlie', '[email protected]')";
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo $stmt->rowCount(); // exist MySQL Next usually returns 2

Although rowCount() is likely to return 2, this is not always supported by all database drivers , and in PostgreSQL, SQLite and other databases, this behavior may be unavailable or return 0.

Official documentation tips:

For some databases, such as PostgreSQL, rowCount() returns values ​​for statements of type INSERT .

So, if you want to use rowCount() safely to get the number of inserted rows in a cross-platform environment, make sure:

• Use a database driver (such as MySQL) that supports this feature.

• Use standard INSERT statements, without relying on ON DUPLICATE KEY or RETURNING clauses (unless the database explicitly supports them).

3. How to obtain the number of rows affected by the insert more securely?

Here are some practical suggestions:

1. Make sure you are using PDO + MySQL

Confirm that the current environment is using MySQL or compatible drivers, and the return value of rowCount() is meaningful.

 if ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'mysql') {
    echo "Insert successfully，Influence number of rows：" . $stmt->rowCount();
} else {
    echo "No MySQL database，rowCount() The results may be inaccurate";
}

2. For single-line insertion, it is safe to use rowCount()

Most drivers support returning an affected number of rows of 1 for a single INSERT, which is recommended.

3. For multi-line insertion, it is recommended to use transactions to loop

 $pdo->beginTransaction();
$total = 0;

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$data = [
    ['name' => 'David', 'email' => '[email protected]'],
    ['name' => 'Eve', 'email' => '[email protected]']
];

foreach ($data as $row) {
    $stmt->execute($row);
    $total += $stmt->rowCount();
}

$pdo->commit();

echo "Insert in total $total OK";

Through a single insertion matching loop, you can ensure that rowCount() behaves consistently under each driver.

4. Summary of common misunderstandings

5. Alternative solution: Use LAST_INSERT_ID()

If you only care about the inserted primary key ID (especially the autoincrement primary key), you can use:

 $pdo->lastInsertId();

Note: Only suitable for inserting single rows and tables with auto-increment primary keys.

6. Summary

PDOStatement::rowCount() is a powerful tool that behaves slightly differently between different databases. When using it to get the number of rows affected by an INSERT INTO statement, it is recommended:

• Priority is given to use in MySQL environment.

• It is more secure to use transactions for batch insertion.

• In multi-database scenarios, you should consider using database-specific RETURNING or other features to avoid relying on rowCount() .

To see more PDO practice examples, visit https://gitbox.net/docs/pdo .