PHP Basic Tutorial

PHP Advanced Tutorial

PHP & MySQL

PHP Reference Manual

PHP CURL Functions PHP OpenSSL Functions

PHP mysqli_stmt_affected_rows() function usage and example

The mysqli_stmt_affected_rows() function returns the total number of rows changed, deleted, or inserted by the last executed statement.

Definition and usage

mysqli_stmt_affected_rows()function returns the number of rows affected by the most recently executed statement (change, delete, insert).

This function works correctly only after calling it in the statements of INSERT, UPDATE, or DELETE. If you need to know the number of rows affected by a SELECT query, you need to use mysqli_stmt_num_rows() Function.

Syntax

mysqli_stmt_affected_rows($stmt)

Parameter

Serial number	Parameters and descriptions
1	stmt(Required) This is the object representing the statement for executing SQL queries.

Return value

The 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-1If there are no affected lines, this function returns0.

PHP version

This function was originally written in PHP version5introduced and can be used in all higher versions.

Online example

Assuming that we have already created a table named employee in the MySQL database, its content is as follows:

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)

The following example demonstratesmysqli_stmt_affected_rows()Usage of the function (procedural style), after executing update to update data, it returns the number of affected rows:

Example

<?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;
　　　//Execute statement
　　　mysqli_stmt_execute($stmt);
　　　print("Updated records......\n");
　　　//Number of affected rows
　　　$count = mysqli_stmt_affected_rows($stmt);
　　　//End statement
　　　mysqli_stmt_close($stmt);
　　　//Close connection
　　　mysqli_close($con);
　　　print("Affected rows \".$count");
?>

Output results

Updated records......
Number of affected rows　3

Online example

The syntax of this function in object-oriented style is$con-> affected_rows;.The following is an example of this function in object-oriented style, after executing delete to delete data, it returns the number of affected rows:

Example

<?php
　　　//Establish connection
　　　$con = new mysqli("localhost", "root", "password", "mydb");
　　　$con　-> query("CREATE TABLE Test(Name VARCHAR(255), AGE INT");
　　　print("Creating table.....\n");
　　　$con　-> query("insert into Test values('Raju',　25),('Rahman',　30),('Sarmista',　27);
　　　print("Inserting records.....\n");
　　　$stmt = $con　-> prepare("DELETE FROM Test WHERE Name in(?, ?)");
　　　$stmt　-> bind_param("ss", $name1, $name2);
　　　$name1　='Raju';
　　　$name2　='Rahman';
　　　print("Record deleted.....\n");
　　　//Execute statement
　　　$stmt->execute();
　　　//Affected rows
　　　$count = $stmt　->affected_rows;
　　　print("Number of affected rows ".$count);
　　　//End statement
　　　$stmt->close();
　　　//Close connection
　　　$con->close();
?>

Output results

Creating table.....
Inserting records.....
Record deleted.....
Number of affected rows　2

Online example

If the query does not affect any rows, let's check its return value-

Example

<?php
　　　$con = @mysqli_connect("localhost", "root", "password", "mydb");
　　　mysqli_query($con, "CREATE TABLE Test(Name VARCHAR(255), AGE INT");
　　　print("Creating table.....\n");
　　　mysqli_query($con, "insert into Test values('Raju',　25),('Rahman',　30),('Sarmista',　27);
　　　print("Inserting records.....\n");
　　　$stmt = mysqli_prepare($con, "DELETE FROM test WHERE Age<?");
　　　mysqli_stmt_bind_param($stmt, "i", $num);
　　　$num =　8;
　　　//Execute statement
　　　mysqli_stmt_execute($stmt);
　　　//Number of affected rows
　　　$count = mysqli_stmt_affected_rows($stmt);
　　　print("Number of affected rows (when the query does not perform any operation): ".$count);
　　　//End statement
　　　mysqli_stmt_close($stmt);
　　　//Close connection
　　　mysqli_close($con);
?>

Output results

Creating table.....
Inserting records.....
Number of affected rows (when the query does not perform any operation): 0

PHP MySQLi Reference Manual

PHP CURL Functions PHP OpenSSL Functions