PHP mysqli_stmt_affected_rows() Function

---

---

Definition and Usage

The mysqli_stmt_affected_rows() function returns the number of rows affected (changed, deleted, inserted) by the recently executed statement.

This function works fine only if invoked after INSERT, UPDATE, or DELETE statements. If you need to know the number of rows affected by the SELECT query you need to use the mysqli_stmt_num_rows() function.

Syntax

mysqli_stmt_affected_rows($stmt)

Parameters

Return Values

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

If the statement has an error this function returns -1. If there are no affected rows, this function returns 0.

PHP Version

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

Example

Assume we have created a table named employee in the MySQL database with the following contents $minus;

mysql> select * from employee;
+------------+--------------+------+------+--------+
| FIRST_NAME | LAST_NAME    | AGE  | SEX  | INCOME |
+------------+--------------+------+------+--------+
| Vinay      | Bhattacharya |   20 | M    |  21000 |
| Sharukh    | Sheik        |   25 | M    |  23300 |
| Trupthi    | Mishra       |   24 | F    |  51000 |
| Sheldon    | Cooper       |   25 | M    |   2256 |
| Sarmista   | Sharma       |   28 | F    |  15000 |
+------------+--------------+------+------+--------+
5 rows in set (0.00 sec)

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

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

   $stmt = mysqli_prepare($con, "UPDATE employee set INCOME=INCOME-? where INCOME>=?");
   mysqli_stmt_bind_param($stmt, "si", $reduct, $limit);
   $limit = 20000;
   $reduct = 5000;

   //Executing the statement
   mysqli_stmt_execute($stmt);
   print("Records Updated......\n");

   //Affected rows
   $count = mysqli_stmt_affected_rows($stmt);

   //Closing the statement
   mysqli_stmt_close($stmt);

   //Closing the connection
   mysqli_close($con);

   print("Rows affected ".$count);
?>

This will produce following result −

Records Updated......
Rows affected 3

Example

In object oriented style the syntax of this function is $con->affected_rows; Following is the example of this function in object oriented style $minus;

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

   $con -> query("CREATE TABLE Test(Name VARCHAR(255), AGE INT)");
   print("Table Created.....\n");
   $con -> query("insert into Test values('Raju', 25),('Rahman', 30),('Sarmista', 27)");
   print("Records Inserted.....\n");

   $stmt = $con -> prepare( "DELETE FROM Test WHERE Name in(?, ?)");
   $stmt -> bind_param("ss", $name1, $name2);
   $name1 = 'Raju';
   $name2 = 'Rahman';
   print("Records Deleted.....\n");

   //Executing the statement
   $stmt->execute();

   //Affected rows
   $count = $stmt ->affected_rows;
   print("Rows affected ".$count);

   //Closing the statement
   $stmt->close();

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

?>

This will produce following result −

Table Created.....
Records Inserted.....
Records Deleted.....
Rows affected 2

Example

Let's check the return values of this if the query does not effect any rows −

<?php
   $con = @mysqli_connect("localhost", "root", "password", "mydb");

   mysqli_query($con, "CREATE TABLE Test(Name VARCHAR(255), AGE INT)");
   print("Table Created.....\n");
   mysqli_query($con, "insert into Test values('Raju', 25),('Rahman', 30),('Sarmista', 27)");
   print("Records Inserted.....\n");

   $stmt = mysqli_prepare($con, "DELETE FROM test where Age<?");
   mysqli_stmt_bind_param($stmt, "i", $num);
   $num = 8;
   //Executing the statement
   mysqli_stmt_execute($stmt);
   //Affected rows
   $count = mysqli_stmt_affected_rows($stmt);
   print("Rows affected (when query does nothing): ".$count);

   //Closing the statement
   mysqli_stmt_close($stmt);
   //Closing the connection
   mysqli_close($con);

?>

This will produce following result −

Table Created.....
Records Inserted.....
Rows affected (when query does nothing): 0