PHP | mysqli_affected_rows() Function

Hello folks! welcome back to a new edition of our tutorial on PHP. In this tutorial guide, we are going to be studying about the PHP mysqli_affected_rows() Function.

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

When used after select statements, this built-in PHP function returns the number of rows.

Syntax

Following below is the syntax to use this function -

mysqli_affected_rows($con)

READ: PHP | MYSQLi Functions

Parameter Details

Sr.No	Parameter & Description
1	con(Mandatory) This is an object representing a connection to MySQL Server.

Return Value

This PHP 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, then this built-in function returns -1. If there are no affected rows or the previous operation is not one of the above mentioned, this PHP function returns 0.

PHP Version

This built-in function was first introduced in PHP version 5 and it works in all of the later versions.

Example1

The following example illustrates the usage of PHP mysqli_affected_rows() function (in a 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);
?>

Output

When the above code is executed, it will produce the following result -

Number of affected rows: 5

Example2

In an object oriented style the syntax of this PHP 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();
?>

Output

When the above code is executed, it will produce the following result -

Number of affected rows: 5

Example3

Let's check the return value of this function when, there are no specified queries earlier to it and, when the query has no error or it does not affect 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);
?>

Output

When the above code is executed, it will produce the following result -

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

Example4

The following example demonstrates the usage of the PHP 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 ('Nkpara', 'Kennedy', 'Chinaza)");
   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 ('Paul', 'Donald', 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);
?>

Output

When the above code is executed, it will produce the 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

READ: PHP | hash_equals() Function

Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial, we are going to be studying about the PHP mysqli_autocommit() Function.

Do feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Do follow us on our various social media handles available and also subscribe to our newsletter to get our tutorial posts delivered directly to your emails.

Thanks for reading and bye for now.