Edit Report a Bug

    mysql_affected_rows

    (PHP 4, PHP 5)

    mysql_affected_rows — Obtém o número de linhas afetadas na operação anterior do MySQL

    Aviso

    Esta extensão tornou-se defasada a partir do PHP 5.5.0 e foi removida no PHP 7.0.0. Em vez disso, as extensões MySQLi ou PDO_MySQL devem ser usadas. Veja também o guia MySQL: escolhendo uma API. Alternativas a esta função incluem:

    Descrição

    mysql_affected_rows(resource $link_identifier = NULL): int

    Obtém o número de linhas afetadas pelo último INSERT, UPDATE, REPLACE ou DELETE associado ao link_identifier.

    Parâmetros

    link_identifier

    A conexão MySQL. Se o identificador da conexão não for especificado, a última conexão aberta por mysql_connect() será usada. Se não houver uma conexão anterior, haverá uma tentativa de criar uma como se mysql_connect() tivesse sido chamada sem argumentos. Se nenhuma conexão for encontrada ou estabelecida, um erro de nível E_WARNING será gerado.

    Valor Retornado

    Retona o número de linas afetadas em caso de sucesso, e -1 se a última consulta falhou.

    Se a última consulta foi um DELETE sem a claúsula WHERE, todos os registros terão sido excluídos da tabela mas esta função irá retornar zero em versões do MySQL anteriores a 4.1.2.

    Ao usar UPDATE, o MySQL não irá atualizar colunas aonde o novo valor é o mesmo que o valor anterior. Isto cria a possibilidade de que mysql_affected_rows() possa não ser igual ao número de linhas encontradas, apenas o número de linhas que literalmente foram afetadas pela consulta.

    O comando REPLACE primeiro exclui o registro com a mesma chave primária e então insere o novo registro. Esta função retorna o número de registros excluídos mais o número de registros inseridos.

    No caso de consultas "INSERT ... ON DUPLICATE KEY UPDATE", o valor retornado será 1 se um insert foi executado, ou 2 para um update de uma linha existente.

    Exemplos

    Exemplo #1 Exemplo mysql_affected_rows()

    <?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());
    ?>

    O exemplo acima produzirá algo semelhante a:

    Records deleted: 10
Records deleted: 0

    Exemplo #2 Exemplo de mysql_affected_rows() usando transações

    <?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");
    ?>

    O exemplo acima produzirá algo semelhante a:

    Updated Records: 10

    Notas

    Nota: Transações

    Se você estiver usando transações, você deve utilizar mysql_affected_rows() depois da sua consulta INSERT, UPDATE, ou DELETE, não depois de COMMIT.

    Nota: Comandos SELECT

    Para obter o número de linhas retornados por um SELECT, é possível usar mysql_num_rows().

    Nota: Chaves estrangeiras em cascata

    mysql_affected_rows() não conta linhas afetadas implicitamente pelo uso de ON DELETE CASCADE e/ou ON UPDATE CASCADE em chaves estrangeiras.

    Veja Também

    add a note add a note

    User Contributed Notes 11 notes

    up
    3
    Ome Ko
    13 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
    3
    temp02 at flexis dot com dot br
    19 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
    ahmd
    8 years ago
    I was just testing  "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, on PHP 5.3.29 and mysql_affected_rows() was returning either 2 for updated row, 1 for inserted new row, and also 0, which was not documented, evidently when nothing was inserted. I was inserting a single row.
    up
    2
    HMax
    17 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
    1
    dobrys at abv dot bg
    17 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
    1
    deponti A_T tiscalinet D0T it
    21 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)
    up
    0
    gtisza at gmail dot com
    5 years ago
    Note that when the CLIENT_FOUND_ROWS connection flag was used, affected_rows returns the number of rows matched by the WHERE condition of an UPDATE query, even if the query doesn't actually change those rows. I.e. for

         INSERT INTO t(id, val) VALUES (1, 'x');
         UPDATE t SET val = 'x' WHERE id = 1;

    the number of affected rows will be 0 normally but 1 with CLIENT_FOUND_ROWS.
    up
    0
    vitospericolato at gmail dot com
    7 years ago
    calling mysql_affected_rows(null)
    is not the same that calling mysql_affected_rows()

    So, if you have a $link variable that could be null, you must write

    if($link)
      $n=mysql_affected_rows($link);
    else
      $n=mysql_affected_rows();
    up
    -2
    brian at smitherconsulting dot com
    8 years ago
    In the case of INSERT where a row/slot had been previously deleted, making an uncollapsed hole in the table, and the record being inserted fills that empty row/slot, that is to say, the inserted data did not create a new row/slot/space, then this may explain why a zero result is returned by this function.
    up
    -7
    steffen at showsource dot dk
    20 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
    -10
    sean at adtools dot co dot uk
    16 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.
    add a note add a note
    To Top