PDO rowcount() to get number of rows or records presently updated or added or deleted by the Query

WE can use rowCount() to know number of rows or records affected by the latest sql statement involving any Delete , update , insert command. This function is to be used immediately after the sql execute command. This function works similar to mysql_affected_rows function

rowcount() to get number of records affected by Query involving any Delete , update & insert command

WE will learn some examples on how to use this in our pdo example table. Download the dump file of pdo_admin table at the end of the tutorial.

$count=$dbo->prepare("update  pdo_admin set status='T' "); 
//$count=$dbo->prepare("delete from pdo_admin where status='F'");

$count->execute();
$no=$count->rowCount();
echo " No of records = ".$no;

Output will show number of records updated or deleted from the table ( based on which line is executed )

No of records = 7

Same way let us try with one delete command

$count=$dbo->prepare("delete from pdo_admin where status='F'");
$count->execute();
$no=$count->rowCount();
echo " No of records = ".$no;

The output will show us number of records deleted from the pdo_admin table.
Same way you can do for insert command.

Podcast on MySQL database management using PHP PDO

rowcount with SELECT Command

Usually rowCount() works with delete, update and insert commands but rowCount does not give reliable data when used with SELECT query in MySQL, so it is better to use Count command to get total number of records. Here is an example.

$nume = $dbo->query("SELECT COUNT(id) FROM  pdo_admin")->fetchColumn();
echo "<br>Number of records : ". $nume;

Here id field we used as this is an auto incremented field. If such field is not there then we can use all the records like this.

$nume = $dbo->query("SELECT COUNT(*) FROM  pdo_admin")->fetchColumn();

Above code returns

Number of records = 10

rowCount and truncate

If you are deleting all records of a table by using Truncate command then rowCount won't able to return you number of records deleted.

Example 1: Using rowCount() with UPDATE Query

This example shows how to use rowCount() after an UPDATE query to check how many rows were affected.

$stmt = $pdo->prepare("UPDATE users SET age = age + 1 WHERE active = 1");
$stmt->execute();
echo $stmt->rowCount() . " rows updated.";

Output:

5 rows updated.

Example 2: rowCount() with DELETE Query

This example demonstrates using rowCount() after a DELETE query.

$stmt = $pdo->prepare("DELETE FROM users WHERE age < 18");
$stmt->execute();
echo $stmt->rowCount() . " rows deleted.";

Output:

3 rows deleted.

Using rowCount() for Confirmation in PHP PDO Transactions with Rollback

rowCount(): Checks if any rows were updated. If not, the transaction is rolled back.
try-catch: Handles any potential errors and ensures rollback if the transaction fails.

try {
    // Begin Transaction
    $dbo->beginTransaction();

    // Prepare an UPDATE query
    $stmt = $dbo->prepare("UPDATE student SET mark = mark + 5 WHERE id = :id");
    $stmt->bindParam(':id', $student_id, PDO::PARAM_INT);
    
    // Set ID and execute query
    $student_id = 1;
    $stmt->execute();

    // Check the number of rows affected
    if ($stmt->rowCount() > 0) {
        // Commit if rows were updated
        $dbo->commit();
        echo "Transaction successful, rows updated: " . $stmt->rowCount();
    } else {
        // Rollback if no rows were affected
        $dbo->rollBack();
        echo "Transaction rolled back, no rows updated.";
    }

} catch (PDOException $e) {
    // Rollback in case of error
    $dbo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Matching to the total number of students in the class.

try {
    // Begin Transaction
    $dbo->beginTransaction();

    // Prepare an UPDATE query
    $stmt = $dbo->prepare("UPDATE student SET mark = mark + 5 ");
    $stmt->execute();

    // Check the number of rows affected
    if ($stmt->rowCount() == 35) { // 35 number of students in the class
        // Commit if rows were updated
        $dbo->commit();
        echo "Transaction successful, rows updated: " . $stmt->rowCount();
    } else {
        // Rollback if no rows were affected
        $dbo->rollBack();
        echo "Transaction rolled back, no rows updated.";
    }

} catch (PDOException $e) {
    // Rollback in case of error
    $dbo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Note on rowCount() with SELECT Queries

For some databases, rowCount() does not work with SELECT queries. Instead, use fetchAll() and count() for accurate row counts.

PDO References PDO multiple records collecting from database

Subscribe to our YouTube Channel here

Steve	09-07-2015
Thanks for this. It's nice and easy - and it works!

Michael	25-06-2018
If you use select count before truncating the table, you'll know the number of rows deleted.

smo1234	29-06-2018
No , that is not the way to count records deleted. It must come directly from MySQL with a single query.

khan	02-12-2012
can u send me file for that plzzz

Rowcount() to get number of rows affected by Query