Number of records updated or affected after any execution of insert, update or delete query in MySQL

Read the affected-row count from the statement

After an INSERT, UPDATE, or DELETE prepared statement executes, the statement affected_rows value tells you how many rows were changed.

<?php
$stmt = $connection->prepare('UPDATE student SET mark = ? WHERE id = ?');
$mark = 90;
$id = 5;
$stmt->bind_param('ii', $mark, $id);
$stmt->execute();

echo $stmt->affected_rows;
?>

SQL concepts: INSERT · UPDATE · DELETE · SELECT · LIMIT

We are changing all records to Class= Four

Using UPDATE Query

// Database connection file to declare $connection variable//
require "config.php"; 

$query="UPDATE  student SET class='Four'";
if ($connection->query($query)) {
echo "Records Updated : ";
echo $connection->affected_rows;
}else{
echo $connection->error;
}

Output

Records Updated : 5

This will only count records changed from any other class to class=Four, it will not count records which already have class=Four

Student Table : Sample Data and query to create table
config.php Database connection object $connection is taken from config.php file.

By using DELETE Query

// Database connection file to declare $connection variable//
require "config.php"; 

$query="DELETE FROM student WHERE class='Four'";
$connection->query($query);
echo " Records Deleted =  ".$connection->affected_rows;

Output is here

Records Deleted = 9

By Using INSERT Query

$query="INSERT INTO student (id,name,class,mark,sex) values(100,'John','Five','80','Male')";
if($connection->query($query)){
echo "
No of records inserted : ".$connection->affected_rows;
}

Output is here

No of records inserted : 1

By using SELECT Query

$query="SELECT * FROM student WHERE class='Four'";
$connection->query($query);
echo " Records returned =  ".$connection->affected_rows;

Output is

Records returned = 9

By using SELECT with LIMIT Query

$query="SELECT * FROM student WHERE class='Four' LIMIT 2,4";
$connection->query($query);
echo " Records returned =  ".$connection->affected_rows;

Records returned = 4

Object oriented style with parameters

<?php
require "config.php";// Database connection file.

$class='Three';
$mark=66;
$id=5;
$query="UPDATE  student SET class=?,mark=? WHERE id=?";
$stmt = $connection->prepare($query);
if ($stmt) {
$stmt->bind_param('sii', $class, $mark, $id);
$stmt->execute();
echo "Record Updated:";
echo $stmt->affected_rows;
}else{
echo $connection->error;
}
?>

Procedural style

$class='Three';
$mark=68;
$id=5;
$query="UPDATE  student SET class=?,mark=? WHERE id=?";
if ($stmt = mysqli_prepare($connection,$query)){
mysqli_stmt_bind_param($stmt, "sii", $class, $mark, $id);
mysqli_stmt_execute($stmt);
echo "Record Updated:";
echo mysqli_affected_rows($connection);

}else{
echo mysqli_error($connection);
}

By using mysql_affected_rows we can get number of records changed / updated / deleted /inserted or selected after using SELECT, UPDATE, REPLACE, DELETE and INSERT query .

Where to use mysqli_affected_rows

After updating one account record, check the affected-row count when your application expects exactly one row to change. For passwords, store a password_hash() result rather than a plain-text password.

IN all the below examples you need to use database config file with connection string. MySQLI database connection file
MySQL DUMP of student table
MYSQLI Functions mysqli_num_rows() Number of rows in result set

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: