English
English
When using PDO (PHP Data Objects) in PHP for database operations, the PDOStatement::rowCount() method can be used to obtain the number of rows affected by SQL statements. For INSERT operations, we usually want to use rowCount() to determine whether the insertion is successful.
PDOStatement::rowCount() is a PDO-provided method that returns the number of rows affected by the last executed SQL statement. For an INSERT statement, it returns the number of rows affected by the operation. Typically, INSERT successfully affects 1 row (if a single insert).
<?php
// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");
// Insert data
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$name = 'John Doe';
$email = '[email protected]';
$stmt->execute();
// use rowCount() Determine whether the insertion is successful
if ($stmt->rowCount() > 0) {
echo "Data insertion successfully！";
} else {
echo "Data insertion failed！";
}
?>
After executing the INSERT statement using PDO , we can use rowCount() to determine whether the data has been successfully inserted. Generally speaking, when the INSERT statement is successfully executed, the number of rows returned should be 1, which means that it affects 1 row of data. If 0 is returned, it may be because the SQL statement is not executed correctly or that no data is inserted.
However, it is important to note that PDOStatement::rowCount() may not return an exact number of rows in some database drivers, especially when using some MySQL storage engines. For example, when using the InnoDB storage engine, rowCount() may not return the expected results, especially when performing batch inserts.
For batch insert operations, the return value of rowCount() may not be a single 1. For example, if we insert multiple records, the value it returns will be the number of records that were successfully inserted.
<?php
// Batch Insert Example
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "username", "password");
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$data = [
['name' => 'Alice', 'email' => '[email protected]'],
['name' => 'Bob', 'email' => '[email protected]'],
['name' => 'Charlie', 'email' => '[email protected]']
];
foreach ($data as $user) {
$stmt->bindParam(':name', $user['name']);
$stmt->bindParam(':email', $user['email']);
$stmt->execute();
}
// Determine the number of inserted rows
if ($stmt->rowCount() === count($data)) {
echo "所有Data insertion successfully！";
} else {
echo "Data insertion failed！";
}
?>
In this example, we use rowCount() to determine whether the number of inserted records is consistent with the expected number. If consistent, all data are inserted successfully.
Although rowCount() can tell whether the insertion is successful, if we need to get the ID of the new insert record, we should use the PDO::lastInsertId() method. lastInsertId() returns the autoincrement ID of the last inserted data table, which is very useful for operations that need to insert and return the record ID.
<?php
// Insert data并Get the last inserted ID
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$name = 'David';
$email = '[email protected]';
$stmt->execute();
// Get the last inserted ID
$lastInsertId = $pdo->lastInsertId();
echo "The last inserted ID yes：$lastInsertId";
?>
LastInsertId() can ensure that we can get the ID of the new record, especially when further operations are required to insert data (such as association with other tables).
PDOStatement::rowCount() is used to obtain the number of rows affected by SQL statements after execution.
For the INSERT statement, rowCount() returns the number of inserted rows, usually 1 row, indicating that the insertion is successful.
When inserting in batches, rowCount() returns the actual number of records inserted.
If you need to get the ID of the insert record, you should use the PDO::lastInsertId() method.
By using rowCount() and lastInsertId() correctly, you can handle database operations more flexibly to ensure the correctness and integrity of data insertion.
