PHP Mysql Tutorials, Function, mysql_affected

Topic

What is mysql_affect_row function in PHP?
How does mysql_affect_row() works?

Explanation

This mysql_affected_rows() function in php returns the number of affected rows in the previous table manipulation by INSERT, UPDATE, REPLACE or DELETE query. It does not work with SELECT statement, works only on statements which modify records.

Syntax

int mysql_affected_rows ( [resource link_identifier])

Returns the number of affected rows on success, or -1 if the last operation failed.

Here mysql_affected_rows() returns the number of rows affected by the last INSERT, UPDATE or DELETE query associated with link_identifier, this should be called before commit.

When using UPDATE query, MySQL will not update columns where the new value is same as the old value. Here the result of Mysql_affected_rows() will not be equal to the number of rows matched, only the number of literally affected rows is returned.

Example: Update-Query

<?php

// Attempt to connect to the default database server

// An ID that refers to the connection opened is stored in $link

$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die ("Could not connect");

if($link)

// Select the database which will be used when making queries

mysql_select_db ("my_database",$link)

// Query to Update records

mysql_query("UPDATE my_table SET count=1 WHERE id > 20",$link);
printf ("Updated records: %d\n", mysql_affected_rows());

mysql_query("COMMIT");

?>

In this script, replace "mysql_host", "mysql_user" and "mysql_password" with your mySQL login and password supplied by the information you used when you installed mySQL.

RESULT:
Updated Records: 10

When COMMIT is executed befor mysql_affected_rows(), then the result will be

Updated Records: 0

See also: mysql_num_rows(), and mysql_info()

mysql_affected_rows Function in PHP