English
English
In PHP, PDO (PHP Data Objects) is an extension that provides a database access abstraction layer. It supports multiple database systems and provides a unified way to perform database operations. PDO::exec and PDOStatement::rowCount are two common tools for operating databases. It is important to understand how they are used and how they are used in combination. This article will explain in detail how to correctly use PDOStatement::rowCount to get the number of affected rows after PDO::exec execution.
PDO::exec is a method in the PDO class. It is usually used to execute SQL statements that do not need to return result sets, such as INSERT , UPDATE , DELETE , etc. It returns the number of rows affected, that is, the number of database records affected after the operation is performed. If execution fails, it returns false .
Example:
<?php
// Database connection
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
// implement SQL Statement
$sql = "UPDATE users SET status = 'active' WHERE status = 'inactive'";
$affectedRows = $pdo->exec($sql);
echo "Number of affected rows: " . $affectedRows;
?>
In the above code, $pdo->exec($sql) performs an update operation and returns the number of affected rows.
PDOStatement::rowCount is a method that gets the number of rows affected by the last executed SQL statement. It should be noted that the rowCount method can only obtain the number of affected rows when executing SELECT queries, or after UPDATE , DELETE , and INSERT operations. For some databases, like MySQL, rowCount may not return an exact number of rows, especially when executing DELETE or UPDATE statements, the number of rows deleted or updated may be different from the actual number of affected rows.
Example:
<?php
// Database connection
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
// implement SQL Statement
$stmt = $pdo->prepare("UPDATE users SET status = 'active' WHERE status = 'inactive'");
$stmt->execute();
// 获取Number of affected rows
$affectedRows = $stmt->rowCount();
echo "Number of affected rows: " . $affectedRows;
?>
In the above code, first prepare the SQL statement through the prepare method and execute it through execute . After that, the affected row count is obtained through the rowCount method.
In actual development, PDO::exec and PDOStatement::rowCount often need to be used in combination. Although PDO::exec itself returns the number of rows affected, if you want to perform more queries, or want to operate the database more flexiblely, PDOStatement::rowCount may be more suitable.
For example, we can use PDO::exec to execute some SQL statements that do not need to return the result set, and then use PDOStatement::rowCount to get the number of affected rows after the statement is executed.
Example:
<?php
// Database connection
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
// implement SQL Statement
$sql = "UPDATE users SET status = 'active' WHERE status = 'inactive'";
$pdo->exec($sql);
// use rowCount 获取Number of affected rows
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = 'active'");
$stmt->execute();
$affectedRows = $stmt->rowCount();
echo "Number of affected rows: " . $affectedRows;
?>
In the above code, we first execute the UPDATE operation through exec , and then execute a SELECT query through prepare and execute to get the number of affected rows.
Through the introduction of this article, we can see that PDO::exec and PDOStatement::rowCount are two very useful methods that can help us better operate the database. It should be noted that the PDO::exec method returns the number of rows affected when executing the SQL statement, while the PDOStatement::rowCount method is usually used to return the number of rows affected by the last query executed. During the development process, using these two methods reasonably can allow you to interact with the database more efficiently.
References
