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

affected_rows() returns number of records affected by the query.
IN all the below examples you need to use database config file with connection string.

MySQLI database connection file

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;
}

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

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

Your user wants change his password. After updating the password field you can verify that the number of records updated and it should be equal to one.

MySQL DUMP of student table

MYSQLI Functions mysqli_num_rows() Number of rows in result set

Subscribe to our YouTube Channel here

MYSQLI Number of Records affected by mysqli_affected_rows