How to Get the Number of Affected Rows By A Query In PHP?

Problem:

You just run a query (edit, delete, select or others). Now, you want to know how many rows have been affected.

Solution:

You know PHP provides three APIs to connect with MySQL. In the following, you’ll find how to get the number of rows affected by a query using these three APIs. We’ll run each query to select the marked rows from the following table .

1. Using PDO
Use rowCount().

Example:

[php]<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

try {
// Establish server connection and select database
$dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// run query to select records
$rows = $dbh->query("SELECT * FROM students WHERE class = ". $dbh->quote($class));
echo $rows->rowCount() . " rows selected.";
}catch(PDOException $e){
echo "Error occurs:". $e->getMessage();
}
?>[/php]

Output:
2 rows selected.

You can also find the number of affected rows by querying SELECT COUNT(*) statement at first, then, using fetchColumn().

Example:

[php]<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

try {
// Establish server connection and select database
$dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// run query to select records
$rows = $dbh->query("SELECT COUNT(*) FROM students WHERE class = ". $dbh->quote($class));
echo $rows->fetchColumn() . " rows selected.";
}catch(PDOException $e){
echo "Error occurs:". $e->getMessage();
}
?>[/php]

Output:
2 rows selected.

2. Using PHP’s MySQLi Extension
In the following two examples we’ll see how to find out the number of affected rows using the following two MySQLi coding style.

1. Procedural style and
2. Object oriented style

2.a. Procedural style

Use mysqli_affacted_rows().

Example:

[php]<?php
// MySQLi Procedural Style
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection and select database
$dbh = mysqli_connect($host, $user, $password, $database);

if (mysqli_connect_errno()) {
die(‘Unable to connect to database ‘. mysqli_connect_error());
}else{
$class = mysqli_real_escape_string($dbh, $class);
// run query to fetch records
$result = mysqli_query($dbh, "SELECT Name, Email FROM students WHERE Class = 7 ");
echo mysqli_affected_rows($result) . “ rows selected.”;
}
?>[/php]

Output:
2 rows selected.

2.b. Object Oriented style
Use affected_rows().

Example:

[php]<?php
// MySQLi Object Oriented Style
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection and select database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
echo ‘Unable to connect to database ‘. $dbh->connect_error;
}else{
if ($stmt = $dbh->query("SELECT Name, Email FROM students WHERE Class = 7")) {
echo $dbh->affected_rows . " rows selected."; // 2 rows selected.
}else{
echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
}
}
?>[/php]

Output:
2 rows selected.

3. Using PHP’s MySQL Extension
Use mysql_affacted_rows().

Example:

[php]<?php
// Connection strings
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection
$link = mysql_connect($host, $user, $password);

if(!$link) {
die("Not connected: ". mysql_error());
}else{
// Select Database
mysql_select_db($database, $link);
// run query to delete records
mysql_query("SELECT Name, Email FROM students WHERE Class = ‘7’");
echo mysql_affected_rows() . “ rows selected.”;
}
?>
[/php]

Output:
2 rows selected.