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.
- Procedural style and
- Object oriented style
2.a. Procedural style
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.