(PHP 4, PHP 5)

mysql_affected_rowsOttiene il numero di righe coinvolte nelle precedenti operazioni MySQL


int mysql_affected_rows ([ resource $ identificativo_connessione ] )

mysql_affected_rows() restituisce il numero di righe coinvolte nell'ultima query INSERT, UPDATE o DELETE associata a identificativo_connessione. Se l'identificativo di connessione non è specificato, viene considerata l'ultima connessione aperta con mysql_connect().


Se sono usate le transazioni, è necessario richiamare mysql_affected_rows() dopo le query INSERT, UPDATE, o DELETE e non dopo il commit.

Se l'ultima query era una query DELETE senza clausola WHERE, tuti i record saranno cancellati dalla tabella ma questa funzione restituirà zero.


Usando UPDATE, MySQL non aggiornerà le colonne nelle quali il nuovo valore è uguale al vecchio valore. Questo crea la possibilità che mysql_affected_rows() può non uguagliare realmente il numero di righe corrispondenti ma solo il numero di righe effettivamente coinvolte dalla query.

mysql_affected_rows() non funziona con l'istruzione SELECT ma solo con le istruzioni che modificano i record. Per ricavare il numero di righe restituite da SELECT, usare mysql_num_rows().

Se l'ultima query fallisce, questa funzione restituisce -1.

Example #1 Query di eliminazione

/* connessione al database */
mysql_pconnect("localhost""utente_mysql""password_mysql") or
"Connessione non riuscita: " mysql_error());

/* questo dovrebbe restituire il numero corretto di record eliminati */
mysql_query("DELETE FROM mia_tabella WHERE id < 10");
printf ("Records eliminati: %d\n"mysql_affected_rows());

/* senza la clausola WHERE nell'istruzione DELETE, dovrebbe restituire 0 */
mysql_query("DELETE FROM mia_tabella");
printf ("Record eliminati: %d\n"mysql_affected_rows());

L'esempio riportato sopra dovrebbe produrre il seguente output:

Records eliminati: 10
Records eliminati: 0

Example #2 Query di aggiornamento

/* connessione al to database */
mysql_pconnect("localhost""utente_mysql""password_mysql") or
"Connessione non riuscita: " mysql_error());

/* aggiornamento dei record */
mysql_query("UPDATE mia_tabella SET used=1 WHERE id < 10");
printf ("Record aggiornati: %d\n"mysql_affected_rows());

L'esempio riportato sopra dovrebbe produrre il seguente output:

Record aggiornati: 10

Vedere anche: mysql_num_rows(), mysql_info().

add a note add a note

User Contributed Notes 7 notes

dobrys at abv dot bg
8 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
Ome Ko
4 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());
8 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.
deponti A_T tiscalinet D0T it
12 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)
temp02 at flexis dot com dot br
10 years ago
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.

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.

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
11 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
sean at adtools dot co dot uk
7 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.

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();
$result = mysql_query($sql);
$affectedrows = "[".mysql_affected_rows($link)."]";
"[".number_format((getmtime()-$start),3)."][$action]: $sql\n";

Example output:
[0.072][1][80]: UPDATE MYTABLE SET FIELD = 1;

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.
To Top