PHP Doku:: Liefert die Anzahl betroffener Datensätze einer vorhergehenden MySQL Operation - function.mysql-affected-rows.html

Verlauf / Chronik / History: (1) anzeigen

Sie sind hier:
Doku-StartseitePHP-HandbuchFunktionsreferenzDatenbankerweiterungenAnbieterspezifische DatenbankerweiterungenMySQLMySQL Funktionenmysql_affected_rows

Ein Service von Reinhard Neidl - Webprogrammierung.

MySQL Funktionen

<<MySQL Funktionen

mysql_client_encoding>>

mysql_affected_rows

(PHP 4, PHP 5)

mysql_affected_rows Liefert die Anzahl betroffener Datensätze einer vorhergehenden MySQL Operation

Beschreibung

int mysql_affected_rows ([ resource $ Verbindungs-Kennung ] )

mysql_affected_rows() liefert die Anzahl betroffener Datensätze durch die letzte INSERT, UPDATE oder DELETE Anfrage an den Server, die mit der angegebenen Verbindungs-Kennung assoziiert wird. Wird die Verbindungskennung nicht angegeben, wird die letzte durch mysql_connect() geöffnete Verbindung angenommen.

Hinweis:

Falls Sie Transaktionen verwenden, müssen Sie mysql_affected_rows() unmittelbar nach Ihrer INSERT, UPDATE, oder DELETE Anfrage aufrufen, nicht nach dem Commit.

War die letzte Anfrage ein DELETE-Anweisung ohne WHERE Bedingung, wurden alle Datensätze aus der Tabelle gelöscht. Für MySQL Versionen vor 4.1.2 wird diese Funktion hier aber Null (0) zurückliefern.

Hinweis:

Benutzen Sie UPDATE wird MySQL keine Spalten aktualisieren, bei denen der neue dem alten Wert entpspricht. Das kann dazu führen, dass mysql_affected_rows() nicht die tatsächliche Anzahl der betroffenen Zeilen liefert, sondern nur die, die wörtlich durch die Anfrage betroffen sind.

REPLACE Anfragen löschen zunächst den Eintrag mit dem gleichen Primärschlüssel und fügen dann den neuen Eintrag ein. Diese Funktion gibt in diesem Fall die Summe aus gelöschten und eingefügten Einträgen zurück.

Um die Anzahl der gelieferten Datensätze einer SELECT-Anfrage zu erhalten benutzen Sie stattdessen die Funktion mysql_num_rows().

Falls die letze Anfrage fehlschlägt, liefert diese Funktion -1 zurück.

Beispiel #1 Delete-Query

<?php
/* Verbindung zur Datenabank */
$link mysql_connect('localhost''mysql_user''mysql_password');
if (!
$link) {
    die(
'Keine Verbindung möglich: ' mysql_error());
}
mysql_select_db('mydb');

/* Das sollte die korrekte Anzahl gelöschter Datensätze liefern */
mysql_query("DELETE FROM mytable WHERE id < 10");
printf ("Datensätze gelöscht: %d\n"mysql_affected_rows());

// Mit einer where Bedingung, die niemals wahr sein kann,
// sollte 0 zurückgegben werden
mysql_query('DELETE FROM mytable WHERE 0');
printf ("Datensätze gelöscht: %d\n"mysql_affected_rows());
?>

Das obige Beispiel gibt folgendes aus:

Datensätze gelöscht: 10
Datensätze gelöscht: 0

Beispiel #2 Update-Query

<?php
/* Verbindung zur Datenabank */
mysql_pconnect("localhost""mysql_user""mysql_password")
    or die (
"Keine Verbindung möglich: " : . mysql_error());
mysql_select_db("mydb");

/* Update von Datensätzen */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Veränderte Datensätze: %d\n"mysql_affected_rows());
mysql_query("COMMIT");
?>

Das obige Beispiel gibt folgendes aus:

Veränderte Datensätze: 10

Siehe auch: mysql_num_rows(), mysql_info().


13 BenutzerBeiträge:
- Beiträge aktualisieren...
info at fedushin dot ru
13.02.2010 3:20
mysql_affected_rows() DOES NOT count rows affected implicitly through 'ON DELETE CASCADE' and/or 'ON UPDATE CASCADE' foreign keys.
For example:

CREATE TABLE `types` (
  `type` varchar(10) NOT NULL,
  PRIMARY KEY (`type`)
) ENGINE=InnoDB;

CREATE TABLE `symbols` (
  `symbol` char(1) NOT NULL,
  `type` varchar(10) NOT NULL,
  PRIMARY KEY (`symbol`),
  KEY `FK_symbol_type` (`type`)
) ENGINE=InnoDB;

ALTER TABLE `symbols`
  ADD CONSTRAINT `FK_symbol_type` FOREIGN KEY (`type`) REFERENCES `types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `types` VALUES ('Number'), ('Letter');
INSERT INTO `symbols` VALUES ('1', 'Number'), ('2', 'Number'), ('A', 'Letter'), ('B', 'Letter');

<?php
mysql_query
('UPDATE types SET type = "Digit" WHERE type = "Number"');
echo
mysql_affected_rows() . '<br>';

mysql_query('DELETE FROM types WHERE type = "Letter"');
echo
mysql_affected_rows() . '<br>';
?>

Each query actually affects 3 rows (= 1 type + 2 symbols), but output is:
1
1
PaulD
8.11.2009 17:35
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.
sean at adtools dot co dot uk
9.09.2008 10:48
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.
EToS
9.08.2007 10:57
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);

   }
?>
HMax
2.07.2007 12:21
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.
dobrys at abv dot bg
28.05.2007 23:35
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
Typer85 at gmail dot com
31.12.2006 17:35
Just to clarify about the possible return values in this Manual those not familiar with PHP and MySQL.

"-1 indicates that the query returned an error."

-1 will be returned if the query itself can not be issued to the server, possibly because of syntax error AND if the last query was not either an Insert or Update statement.
mlugassy at 2find dot co dot il
8.10.2005 3:22
To solve the affectedRows() issue on MySQL using PEAR::DB, simply add a 'client_flags' key with a value of 2 to your $dsn options:

<?php
$dsn
= array(
   
'phptype'  => 'mysql',
   
'client_flags'  => 2,
   
'username' => 'someuser',
   
'password' => 'apasswd',
   
'hostspec' => 'localhost',
   
'database' => 'thedb',
);
?>
temp02 at flexis dot com dot br
29.06.2005 14:39
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.
steffen at showsource dot dk
28.09.2004 12:20
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
deponti A_T tiscalinet D0T it
7.11.2003 13:52
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)
ben-xo at NOSPAMdubplatesNOSPAM dot org
21.04.2002 16:30
mysql_affected_rows() reports on the number of rows affected by an in-place operation on the database, but mysql_num_rows() returns the number of rows in a MySQL record set (which is held by PHP after MySQL has generated it). This means that if you can do

<?php
$a
= mysql_query("SELECT ...");
$b = mysql_query("SELECT ...");
if (
mysql_unm_rows($a) > mysql_num_rows($b)) print "a is larger";
else print
"b is larger";
?>

... but this does not make sense for the operations supported by mysql_affected_rows(), which reports on the status of the database connection as a whole.

Particularly note this:

<?php
$query
= "UPDATE ...";
mysql_query($query);
print
mysql_affected_rows(); // more than 0
mysql_query($query); // same query twice
print mysql_affected_rows(); // 0.
?>

.. this is because the 2nd time you execute the identical query, all the rows are already updated so no rows are affected the 2nd time.

I hope this clears up why mysql_num_rows() and mysql_affected_rows() are fundamentally different
dfylstra at frontsys dot com
13.08.2001 9:06
mysql_affected_rows() also reports the number of rows changed by the LOAD DATA command.  If you use the IGNORE option in LOAD DATA and you know the number of rows in the input file, you can use mysql_affected_rows() to determine the number of rows that were ignored.



PHP Powered Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)