Number of lines changed by the user

Asked

Viewed 189 times

1

I have some tables in a system and for all entries I do the validation and compare if the input is equal to what is currently defined in the table and use PHP rowCount() to check if I had an update and display the correct message to the user.

If I do not compare the input value with the table value and run the update, I will certainly display an error message to the user, since being the defined value and the new entry being equal, SQL does not perform the update.

There is some parameter SQL, or parameterization, that allows you to perform the upgrade, even if it is the same as the one currently defined, so that I eternally avoid this comparison of the input with what is currently defined?

php mysql

  • An error occurs when trying to update a record with the same value that is already in the database?

  • It does not generate any failure when I upgrade with the same data as the database, only that I usually check with rowCount() if there was an update in the database, the return is 0(zero). With the current script I can display an error message. Another detail, I use Exception (Try / catch), I can disregard rowCount() and use Try catch considering that I was successful. What do you think?

1 answer

1


You can use the function mysqli_affected_rows which returns the number of rows that have been changed.

Table:

+----+----------+--------------------+
| id | nome     | email              |
+----+----------+--------------------+
|  1 | Laerte   | [email protected]   |
|  2 | Ana      | [email protected]      |
+----+----------+--------------------+

Script:

<?php
    $conexao = mysqli_connect("servidor", "usuario", "senha", "banco");

    if (!$conexao) {
        printf("Não foi possível conectar ao banco. Erro: %s\n", mysqli_connect_error());
        exit();
    }

    /* UPDATE com valores iguais */
    mysqli_query($conexao, "UPDATE contatos SET nome = 'Laerte', email = '[email protected]' WHERE id = 1");
    printf("# de linhas alteradas: %d\n", mysqli_affected_rows($conexao));

    /* UPDATE com um valor igual e outro diferente. */
    mysqli_query($conexao, "UPDATE contatos SET nome = 'Laerte', email = '[email protected]' WHERE id = 1");
    printf("<br># de linhas alteradas: %d\n", mysqli_affected_rows($conexao));

    mysqli_close($conexao);
?>

Exit:

# de linhas alteradas: 0
# de linhas alteradas: 1

This function returns the Rows Changed, as seen on console output:

mysql> UPDATE contacts SET name = 'Laerte', email = '[email protected]' WHERE id = 1; Query OK, 0 Rows affected (0.03 sec) Rows Matched: 1 Changed: 0 Warnings: 0

mysql> UPDATE contacts SET name = 'Laerte', email = '[email protected]' WHERE id = 1; Query OK, 1 Row affected (0.04 sec) Rows Matched: 1 Changed: 1 Warnings: 0

Reference: mysqli_affected_rows

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.