English
English
In PHP, PDO (PHP Data Objects) provides a secure and flexible database operation method. The PDOStatement::rowCount function is a common method. It allows you to check the number of affected rows after an SQL statement is executed, thereby determining whether SQL is successfully executed. This method is especially useful for SQL statements that perform data modifications such as INSERT , UPDATE , or DELETE .
In this article, we will explain how to use the PDOStatement::rowCount function to detect whether SQL execution is successful and get the number of affected rows.
The PDOStatement::rowCount function returns the number of rows affected by the previous executed SQL statement. This method is very suitable for querying INSERT , UPDATE , DELETE statements to get the number of affected rows.
It should be noted that rowCount does not apply to all SQL statements. For example, for a SELECT statement, rowCount does not necessarily return the correct value, depending on the database driver.
Next, we will show how to use PDOStatement::rowCount with a concrete example.
First, we need to establish a PDO connection. Suppose we use a MySQL database to operate, the code is as follows:
<?php
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
In this code, we create a PDO instance named $pdo and connect to a database named testdb .
Next, we execute a UPDATE statement to modify the data in the table and use rowCount to get the number of affected rows:
<?php
$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$email = '[email protected]';
$id = 1;
$stmt->bindParam(':email', $email);
$stmt->bindParam(':id', $id);
$stmt->execute();
// Get the number of affected rows
$affectedRows = $stmt->rowCount();
echo "Number of affected rows: " . $affectedRows;
?>
Similarly, the DELETE statement can also use rowCount to get the number of affected rows:
<?php
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$id = 2;
$stmt->bindParam(':id', $id);
$stmt->execute();
// Get the number of affected rows
$affectedRows = $stmt->rowCount();
echo "Number of affected rows: " . $affectedRows;
?>
For the INSERT statement, rowCount returns the number of inserted rows. If a record is successfully inserted, it returns 1.
<?php
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
$username = 'newuser';
$email = '[email protected]';
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$stmt->execute();
// Get the number of affected rows
$affectedRows = $stmt->rowCount();
echo "Number of affected rows: " . $affectedRows;
?>
The return value of PDOStatement::rowCount can help us detect whether SQL is executed successfully. Normally, when we perform INSERT , UPDATE , or DELETE operations:
If the return value is greater than 0, it means that the operation is successful and at least one row of data is affected.
If the return value is 0, it means that the operation failed to successfully affect any data (for example, UPDATE does not have any matching conditions, or 0 records were deleted by the DELETE statement).
We can use rowCount to determine whether the SQL statement is executed successfully. The sample code is as follows:
<?php
if ($stmt->rowCount() > 0) {
echo "Operation is successful，Number of affected rows: " . $stmt->rowCount();
} else {
echo "Operation failed，No data affected";
}
?>
For SELECT statements, the value of rowCount does not necessarily correctly reflect the number of rows returned, depending on the database driver. Therefore, rowCount is mainly used for INSERT , UPDATE and DELETE operations.
In some cases, rowCount may return 0, but this does not mean that the SQL statement itself fails to execute. It may just be that no rows have been modified or deleted.
PDOStatement::rowCount is a very useful function. It can help us get the number of rows affected after SQL execution and judge whether the SQL operation is successful through this value. Especially when processing data modification operations, you can understand the execution results in a timely manner, which will help program error handling and logical control.
