mysqli_affected_rows(): Number of Records affected

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

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.

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
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here





    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer