mysql_affected_rows

(PHP 4, PHP 5)

mysql_affected_rowsGet number of affected rows in previous MySQL operation

Warning

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

Description

int mysql_affected_rows ([ resource $link_identifier = NULL ] )

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.

Parameters

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

Returns the number of affected rows on success, and -1 if the last query failed.

If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero with MySQL versions prior to 4.1.2.

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

The REPLACE statement first deletes the record with the same primary key and then inserts the new record. This function returns the number of deleted records plus the number of inserted records.

In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row.

Examples

Example #1 mysql_affected_rows() example

<?php
$link 
mysql_connect('localhost''mysql_user''mysql_password');
if (!
$link) {
    die(
'Could not connect: ' mysql_error());
}
mysql_select_db('mydb');

/* this should return the correct numbers of deleted records */
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n"mysql_affected_rows());

/* with a where clause that is never true, it should return 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n"mysql_affected_rows());
?>

The above example will output something similar to:

Records deleted: 10
Records deleted: 0

Example #2 mysql_affected_rows() example using transactions

<?php
$link 
mysql_connect('localhost''mysql_user''mysql_password');
if (!
$link) {
    die(
'Could not connect: ' mysql_error());
}
mysql_select_db('mydb');

/* Update records */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Updated records: %d\n"mysql_affected_rows());
mysql_query("COMMIT");
?>

The above example will output something similar to:

Updated Records: 10

Notes

Note: Transactions

If you are using transactions, you need to call mysql_affected_rows() after your INSERT, UPDATE, or DELETE query, not after the COMMIT.

Note: SELECT Statements

To retrieve the number of rows returned by a SELECT, it is possible to use mysql_num_rows().

Note: Cascaded Foreign Keys

mysql_affected_rows() does not count rows affected implicitly through the use of ON DELETE CASCADE and/or ON UPDATE CASCADE in foreign key constraints.

See Also

add a note add a note

User Contributed Notes 10 notes

up
7
EToS
6 years ago
i found a pretty nice way, this db class/function will accept an array of arrays of querys, it will auto check every line for affected rows in db, if one is 0 it will rollback and return false, else it will commit and return true, the call to the function is simple and is easy to read etc
----------

<?php
class MySQLDB
{
   private
$connection;          // The MySQL database connection

   /* Class constructor */
  
function MySQLDB(){
     
/* Make connection to database */
     
$this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
     
mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
   }

  
/* Transactions functions */

  
function begin(){
        
$null = mysql_query("START TRANSACTION", $this->connection);
      return
mysql_query("BEGIN", $this->connection);
   }

   function
commit(){
      return
mysql_query("COMMIT", $this->connection);
   }
  
   function
rollback(){
      return
mysql_query("ROLLBACK", $this->connection);
   }

   function
transaction($q_array){
        
$retval = 1;

     
$this->begin();

         foreach(
$q_array as $qa){
           
$result = mysql_query($qa['query'], $this->connection);
            if(
mysql_affected_rows() == 0){ $retval = 0; }
         }

      if(
$retval == 0){
        
$this->rollback();
         return
false;
      }else{
        
$this->commit();
         return
true;
      }
   }

};

/* Create database connection object */
$database = new MySQLDB;

// then from anywhere else simply put the transaction queries in an array or arrays like this:

  
function function(){
      global
$database;

     
$q = array (
         array(
"query" => "UPDATE table WHERE something = 'something'"),
         array(
"query" => "UPDATE table WHERE something_else = 'something_else'"),
         array(
"query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
      );

     
$database->transaction($q);

   }
?>
up
2
HMax
6 years ago
If you use "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, mysql_affected_rows() will return you 2 if the UPDATE was made (just as it does with the "REPLACE INTO" syntax) and 1 if the INSERT was.

So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won't get the real count.
up
2
sean at adtools dot co dot uk
5 years ago
Here's a little function I've been using for a while now, pass it two parameters (action command (1 or 0 see notes)) and a sql statement.

It returns a simple line which shows the length of time taken to action the query, the status of the query (0= query not actioned, you can set this value for testing, 1=success qry executed successfully, -1= failed, there was a problem with the sql statement) the number of lines affected by that query and the sql statement itself.

I've found this invaluable when trying to tie down large amounts of updates to a table, using this you can easily see where a query was successfully executed and the number of rows are affected, or where there are problems and a statement has failed for example.

<?php
function dosql($action,$sql){
 
# assuming you have setup a link to your database entitled $link
  # action = 1 run this query
  # action = 0 don't run, just return sql statement
 
 
$start = getmtime();
 
  if(
$action==1){
   
$result = mysql_query($sql);
   
$affectedrows = "[".mysql_affected_rows($link)."]";
  }
  return
"[".number_format((getmtime()-$start),3)."][$action]: $sql\n";
 
mysql_free_result($result);
}
?>

Example output:
[0.072][1][80]: UPDATE MYTABLE SET FIELD = 1;
[0.106][1][758]: UPDATE ANOTHERTABLE SET FIELD = 2;
[0.006][-1][0]: UPDATER ANOTHERTABLE SET FIELD = 2;

The output shows:

[Timetaken][result]][lines affected]

The result will be either -1, 0 or 1, -1 means there's a problem with the sql statement, 1 means it executed correctly, 0 means it wasn't executed.
up
1
temp02 at flexis dot com dot br
8 years ago
SCENARIO
1. You're using MySQL 4.1x with foreign keys.
2. You have table t2 linked to table t1 by a CASCADE ON DELETE foreign key.
3. t2 has a UNIQUE key so that duplicate records are unacceptable.
3. You have a REPLACE query on t1 followed by an INSERT query on t2 and expect the second query to fail if there's an attempted insert of a duplicate record.

PROBLEM
You notice that the second query is not failing as you had expected even though the record being inserted is an exact duplicate of a record previously inserted.

CAUSE
When the first query (the REPLACE query) deletes a record from t1 in the first stage of the REPLACE operation, it cascades the delete to the record that would be duplicated in t2. The second query then does not fail because the "duplicate" record is no longer a duplicate, as the original one has just been deleted.
up
1
Ome Ko
2 years ago
There are no rows affected by an update with identical data.
So here is one very ugly solution for these cases:
<?
function mysql_matched_rows() {
   $_kaBoom=explode(' ',mysql_info());
   return $_kaBoom[2];
}
?>
up
1
steffen at showsource dot dk
9 years ago
Using OPTIMIZE TABLE will also return true.
So, if you want to check the numbers of deleted records, use mysql_affected_rows() before OPTIMIZE TABLE
up
2
dobrys at abv dot bg
6 years ago
I see that when try to use mysql_affected_rows() with "mysql_pconnect(...)" without link indetifier as param in "mysql_affected_rows()" the result is allways -1.
When use link identifier "mysql_affected_rows($this_sql_connection)" - everything is Fine. This is is on PHP Version 5.2.0
Hope that this was helpfull for somebody
up
0
PaulD
4 years ago
Just a note: [you should] check for a mysql_affected_rows() return value of -1. If a series of queries were run as a transaction and one query failed, the logic in the foreach loop of the transaction() method in class MySQLDB will not issue a ROLLBACK. It may also be slightly more efficient to stop processing queries on the first failure.
up
-1
cbal at freemail dot hu
2 years ago
I dont know why but sometimes mysql_affected_rows does not work (return 0 or nothing) besides the query worked well.
So use this if you have a problem:

<?php
$result
= mysql_query("update/delete ...");
$last = mysql_query("SELECT ROW_COUNT();");
$last = mysql_fetch_array($last);
?>
up
-1
deponti A_T tiscalinet D0T it
10 years ago
It works also for REPLACE query,returning:
0 if the record it's already updated (0 record modified),
1 if the record it's new (1 record inserted),
2 if the record it's updated (2 operations: 1 deletion+ 1 insertion)
To Top