MYSQLI Number of Records affected by mysqli_affected_rows

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

Your Rating





Post your comments , suggestion , error , requirements etc here .




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