PHP mysqli_affected_rows() Function

---

---

Definition and Usage

The mysqli_affected_rows() function returns the number of rows affected by the previous operation, if invoked after INSERT, UPDATE, REPLACE or DELETE query.

When used after select statements this function returns the number of rows.

Syntax

mysqli_affected_rows($con)

Parameters

Return Values

PHP mysqli_affected_rows() function returns an integer value indicating the number of rows affected by the previous (SELECT, INSERT, UPDATE, REPLACE or DELETE) operation.

If the previous query has an error this function returns -1. If there are no affected rows or the previous query/operation is not one of the above mentioned, this function returns 0.

PHP Version

This function was first introduced in PHP Version 5 and works works in all the later versions.

Example

Following example demonstrates the usage of the mysqli_affected_rows() function (in procedural style) −

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   //Query to retrieve all the rows of employee table
   mysqli_query($con, "SELECT * FROM employee");

   //Effected rows
   $rows = mysqli_affected_rows($con);
   print("Number of affected rows: ".$rows);

   //Closing the connection
   mysqli_close($con);
?>

This will produce following result −

Number of affected rows: 5

Example

In object oriented style the syntax of this function is $con -> affected_rows, Where, $con is the connection object −

<?php
   //Creating a connection
   $con = new mysqli("localhost", "root", "password", "mydb");

   //Query to retrieve all the rows of employee table
   $con -> query("SELECT * FROM employee");

   //Number of affected rows
   $rows = $con -> affected_rows;
   print("Number of affected rows: ".$rows);

   //Closing the connection
   $con -> close();
?>

This will produce following result −

Number of affected rows: 5

Example

Let's check the return values of this function when, there are no (specified) quires earlier to it and, when the query has an error or it does not effect any rows −

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   $rows1 = mysqli_affected_rows($con);
   print("Rows Affected (no specified previous operations): ".$rows1."\n");

   //Query to retrieve all the rows of employee table
   mysqli_query($con, "SELECT * FORM employee");
   $rows2 = mysqli_affected_rows($con);
   print("Rows Affected (when query has error): ".$rows2."\n");

   //Query to retrieve all the rows of employee table
   mysqli_query($con, "SELECT  FIRST_NAME FROM employee WHERE AGE <=19");
   $rows3 = mysqli_affected_rows($con);
   print("Rows Affected (when query does nothing): ".$rows3."\n");

   //Closing the connection
   mysqli_close($con);
?>

This will produce following result −

Rows Affected (no specified previous operations): 0
Rows Affected (when query has error): -1
Rows Affected (when query does nothing): 0

Example

Following example demonstrates the usage of the mysqli_affected_rows function with SELECT, UPDATE, INSERT and, DELETE queries −

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   //Query to SELECT all the rows of the employee table
   mysqli_query($con, "SELECT * FROM employee where INCOME > 8000");
   print("Rows Affected by SELECT query: ".mysqli_affected_rows($con)."\n");

   //Query to UPDATE the rows of the employee table
   mysqli_query($con, "UPDATE employee set INCOME=INCOME+5000 where FIRST_NAME in ('Ramya', 'Trupthi', 'Sarmista')");
   print("Rows Affected by UPDATE query: ".mysqli_affected_rows($con)."\n");

   //Query to INSERT a row into the employee table
   mysqli_query($con, "INSERT INTO employee VALUES ('Archana', 'Mohonthy', 30, 'M', 13000, 106)");
   print("Rows Affected by INSERT query: ".mysqli_affected_rows($con)."\n");

   //Query to DELETE rows of the employee table
   mysqli_query($con, "DELETE FROM employee where AGE > 25");
   print("Rows Affected by DELETE query: ".mysqli_affected_rows($con)."\n");

   //Closing the connection
   mysqli_close($con);
?>

This will produce following result −

Rows Affected by SELECT query: 4
Rows Affected by UPDATE query: 3
Rows Affected by INSERT query: 1
Rows Affected by DELETE query: 3