English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
The mysqli_affected_rows() function retrieves the number of rows affected by the last MySQL operation.
mysqli_affected_rows()The function's purpose is: If called after an INSERT, UPDATE, REPLACE, or DELETE query, it returns the number of rows affected by the previous operation.
This function returns the number of rows when used after a SELECT statement.
mysqli_affected_rows($con)
| Serial Number | Parameters and Description |
|---|---|
| 1 | con (required) This is an object representing the connection to the MySQL Server. |
The 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, then this function returns -1. If there are no affected rows, or the previous query/is not the query mentioned above/one of the operations, then this function returns 0.
This function was originally in PHP version5introduced and can be used in all higher versions.
The following examples demonstratemysqli_affected_rows()Usage of the function (programming style)
<?php
//Create connection
$con = mysqli_connect("localhost", "root", "password", "mydb");
//Query to retrieve all rows from the employee table
mysqli_query($con, "SELECT * FROM employee");
//Number of affected rows
$rows = mysqli_affected_rows($con);
print("Number of affected rows: ".$rows);
//Close connection
mysqli_close($con);
?>Output result
Number of affected rows: 5
In an object-oriented style, the syntax of this function is $con -> affected_rows, where $con is a connection object-
<?php
//Create connection
$con = new mysqli("localhost", "root", "password", "mydb");
//Query to retrieve all rows from the employee table
$con -> query("SELECT * FROM employee");
//Number of affected rows
$rows = $con -> affected_rows;
print("Number of affected rows: ".$rows);
//Close connection
$con -> close();
?>Output result
Number of affected rows: 5
Let's check the return value of this function under the following conditions: no (specified) query, and the query has an error or does not affect any rows:
<?php
//Create connection
$con = mysqli_connect("localhost", "root", "password", "mydb");
$rows1 = mysqli_affected_rows($con);
print("Affected rows (when no operation is specified previously): ".$rows1".\n");
//Query to retrieve all rows from the employee table
mysqli_query($con, "SELECT * FORM employee");
$rows2 = mysqli_affected_rows($con);
print("Affected rows (when there is an error in the query): ".$rows2".\n");
//Query to retrieve all rows from the employee table
mysqli_query($con, "SELECT FIRST_NAME FROM employee WHERE AGE \<=");19");
$rows3 = mysqli_affected_rows($con);
print("Affected rows (when no operation is executed during the query): ".$rows3".\n");
//Close connection
mysqli_close($con);
?>Output result
Rows affected (when no operation is specified previously): 0 Rows affected (when there is an error in the query): -1 Rows affected (when the query does not perform any operation): 0
The following examples demonstrate SELECT, UPDATE, INSERT, and DELETE queries inmysqli_affected_rowsFunction usage-
<?php
//Establish connection
$con = mysqli_connect("localhost", "root", "password", "mydb");
//Query all rows from the employee table
mysqli_query($con, "SELECT * FROM employee WHERE INCOME > 8000");
print("Number of rows affected by SELECT query: ".mysqli_affected_rows($con)."\n");
//Query to update rows in the employee table
mysqli_query($con, "UPDATE employee SET INCOME=INCOME+5000 WHERE FIRST_NAME IN ('Ramya', 'Trupthi', 'Sarmista')");
print("Number of 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("Number of rows affected by INSERT query: ".mysqli_affected_rows($con)."\n");
//Query to delete rows from the employee table
mysqli_query($con, "DELETE FROM employee WHERE AGE > 25");
print("Number of rows affected by DELETE query: ".mysqli_affected_rows($con)."\n");
//Close connection
mysqli_close($con);
?>Output result
Number of rows affected by SELECT query: 4 Number of rows affected by UPDATE query: 3 Number of rows affected by INSERT query: 1 Number of rows affected by DELETE query: 3