mysqli_affected_rows() - Count Affected Rows

PHP Modules Tutorials - Herong's Tutorial Examples

∟mysqli_affected_rows() - Count Affected Rows

This section provides a tutorial example on how to get the number of affected rows of the last query by calling the mysqli_affected_rows() function.

After running an INSERT, UPDATE, REPLACE or DELETE query, you can get the number of affected rows by calling the mysqli_affected_rows() function:

 
$num = mysqli_affected_rows($con);

where:
  $con - The MySQL connection object
  $num - The number of affected rows of the last query

Here is a PHP script example showing you how to use the mysqli_affected_rows() function:

<?php
#  mysqli-affected-rows.php
#- Copyright 2009-2026 (c) HerongYang.com. All Rights Reserved.

  $con = mysqli_connect("127.0.0.1", "herong", "TopSecret", 
    "MyBase");
  $res = mysqli_query($con, "DROP TABLE MyTable");
  $res = mysqli_query($con, 
    "CREATE TABLE MyTable ".
    "(ID INTEGER AUTO_INCREMENT, Value INTEGER, PRIMARY KEY (ID))");

  # insert some rows
  $num = mysqli_query_dml($con, 
    "INSERT INTO MyTable (Value) VALUES (100), (200), (300)");

  # clone some rows
  $num = mysqli_query_dml($con, 
    "INSERT INTO MyTable (Value) ".
    "SELECT FLOOR(RAND()*Value)+1 FROM MyTable");

  # update some rows
  $num = mysqli_query_dml($con, 
    "UPDATE MyTable SET Value = 2*Value WHERE Value < 150");

  # delete some rows
  $num = mysqli_query_dml($con, 
    "DELETE FROM MyTable WHERE Value > 250");

  mysqli_close($con);

function mysqli_query_dml($con, $sql) {
  $num = 0;
  print("$sql\n");
  $res = mysqli_query($con, $sql);
  if (!$res) {
    $errno = mysqli_errno($con);
    $error = mysqli_error($con);
    print("Query failed: $errno - $error\n");
  } else {
    $num = mysqli_affected_rows($con); 
    print("   Number of affected rows: $num\n");
  }
  return $num;
}
?>

If you run the script, you will get the following output:

herong$ php mysqli-affected-rows.php 

INSERT INTO MyTable (Value) VALUES (100), (200), (300)
   Number of affected rows: 3

INSERT INTO MyTable (Value) SELECT FLOOR(RAND()*Value)+1 FROM MyTable
   Number of affected rows: 3

UPDATE MyTable SET Value = 2*Value WHERE Value < 150
   Number of affected rows: 3

DELETE FROM MyTable WHERE Value > 250
   Number of affected rows: 1