mysql_affected_rows()

You can call the mysql_affected_rows() function to return the number of rows modified, deleted, or inserted by the last UPDATE, DELETE, or INSERT statement.

Syntax

my_ulonglong
mysql_affected_rows(MYSQL *mysql)

Return values

• If an integer greater than 0 is returned, it indicates the number of affected or retrieved rows.

• If 0 is returned, the UPDATE statement is not used to update records, no row matching the WHERE clause in the query exists, or no query has been executed.

• If -1 is returned, an error has occurred, or mysql_affected_rows() has been called before mysql_store_result() is called for a SELECT query.

Errors

None.

Notes

After mysql_real_query() or mysql_query() is executed, you can directly call mysql_affected_rows(). If the last statement is UPDATE, DELETE, or INSERT, the number of modified, deleted, or inserted rows is returned. If the data table does not change, the number of rows is 0. Therefore, mysql_affected_rows() is usually used to check the execution result of a transaction so as to determines the subsequent operation, that is, commit or roll back the transaction.

• For a SELECT statement, mysql_affected_rows() works as mysql_num_rows() to return the number of rows in the query result.

• For an UPDATE statement, the number of affected rows is the number of actually modified rows by default. If a CLIENT_FOUND_ROWS identifier is specified for mysql_real_connect() when you connect to mysqld , the number of affected rows is the number of rows that match the WHERE clause.

• For a REPLACE statement, a row is inserted after a duplicate record is deleted. Therefore, the number of affected rows is 2.

• For an INSERT ... ON DUPLICATE KEY UPDATE statement, if a row is inserted as a new row, the number of affected rows is 1. If an existing row is updated, the number of affected rows is 2. If an existing row is set to the current value, the number of affected rows is 0. If a CLIENT_FOUND_ROWS identifier is specified and an existing row is set to the current value, the number of affected rows is 1, instead of 0.

• A stored procedure is followed by a CALLstatement. The return value of mysql_affected_rows() is the value returned by the last statement of the stored procedure. If the last statement returns -1, mysql_affected_rows() returns 0. In this stored procedure, you can use ROW_COUNT() at an SQL level to obtain the number of affected rows in a single statement.