How to Test If a MySQL Query Was Successful in Modifying Database Table Data

How to judge whether the SQL update statement to delete success

You should use mysql_affected_rows() instead.

So that an you'll know that it made changes you want.

Obligatory note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Here's what it will look like in using mysqli:

if(isset($_GET['id'])) {
    $con = new mysqli('localhost', 'username', 'password', 'database_name');
    $id = $_GET['id'];
    $sql = 'UPDATE user SET age = 50 WHERE id = ?';
    $update = $con->prepare($sql);
    $update->bind_param('i', $id);
    $update->execute();

    if($update->affected_rows > 0) {
        echo 'updated';
    } else {
        echo 'nothing';
    }
}

How do I test if my MySQL update query was successful when there is no update in Code Igniter?

Both will work like same only difference

if you using active record class all the input variables are escaped automatically

If you want security you better to go with active record class

If you use normal query execution you have to escape the input parameters manually using $this->db->escape_str()

  $id = $this->db->escape_str($id)

You can check the query execution status.

$status =  $this->db->update("users", array('firstName' => $newFirstName));

if($status)
{
    //here you can check the number of affected rows if required
    echo $this->db->affected_rows();

}else{

      //here you can prcess for failure case
      log_message('Error', $this->db->_error_message());

}

//return the status to required place
return $status

How to be certain that an SQL QUERY was successful in Express JS

If you are using mysql npm package in your NodeJs project then you can use the logic below to determine what happened after running the UPDATE query on a given userId that you have.

//Query DB to debit user
db.query(`UPDATE users SET wallet=newBalance WHERE userId=${iserId}`, (err, data) => {

  if (err) {
     return res.send(err)
  }

  console.log(data);
  
  /**
   * This is returned when update was successful
   * {
   *   "fieldCount": 0,
   *   "affectedRows": 1,
   *   "insertId": 0,
   *   "serverStatus": 2,
   *   "warningCount": 0,
   *   "message": "(Rows matched: 1  Changed: 1  Warnings: 0",
   *   "protocol41": true,
   *   "changedRows": 1
   * }
   */

   /**
    * This is returned when no matching rows are found
    * {
    *  "fieldCount": 0,
    *  "affectedRows": 0,
    *  "insertId": 0,
    *  "serverStatus": 2,
    *  "warningCount": 0,
    *  "message": "(Rows matched: 0  Changed: 0  Warnings: 0",
    *  "protocol41": true,
    *  "changedRows": 0
    * }
    */

   if (data.affectedRows === 0) {
       console.log("Nothing changed in this update");
       // Do something
   }

  // Do something with data only if UPDATE was successful.
  console.log('Update was successful');
  res.send('wallet debuted Successfully')

});

Another approach is to fetch the data first and check if there is anything to update.

So, you would run a fetch query like

select wallet, userId from users where userId = ?

Now, if the above query returns empty list, then the userId is invalid,
if userId is valid then you can check for the wallet amount and can update accordingly.

Check if MySQL UPDATE query was successful or not

That's not the correct way to execute an UPDATE query.

You don't need to call any ExecuteReader and you don't need any MySqlDataAdapter. Of course leaving the reader open triggers your actual error because the connection cannot execute any other command until it is freed from serving the reader.
But you have also three big problems in your code not immediately evident.

You should always execute parameterized queries and never create sql commands concatenating strings from the user input. (Search about Sql Injection).

You should always enclose your MySqlConnection in a using statement block to correctly dispose it.

Using conn as MySqlConnection = New MySqlConnection("server=localhost; user id=root; password=; database=testing")
    Dim username As Boolean = True
    conn.Open()
    Dim sqlquery As String = "UPDATE user SET password=@pass WHERE id=@id"
    Dim command As New MySqlCommand
    command.CommandText = sqlquery
    command.Connection = conn
    command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = TextBox1.Text
    command.Parameters.Add("@id", MySqlDbType.Int32).Value = frmLogin.CurrentUserID
    Dim i As Integer = command.ExecuteNonQuery()
    If (i > 0) Then
        MessageBox.Show("Success!")
    Else
        MessageBox.Show("Failed!")
    End If
End Using

There is another problem but this is more complex to solve. Storing passwords in clear text inside a database is considered a very high security risk. You should learn how to Hash and Salt passwords before storing them in the database

How to determine if a MySQL update query succeeded when the data passed in the query is the same as what is already in the database?

Just check if no errors occurred after execution of query.

If you use mysql, check mysql_error():

if (!mysql_error()) print 'all is fine';

Same for mysqli.

Check if update query was successful [PHP, mysqli]

"@Fred-ii- Thank you so much that works! – Coffee coder 58 secs ago"

Use if(mysqli_affected_rows($mysqli) >0 ) or no comparison at all.

Sidenote: ==1 is only comparing for 1, as opposed to >0 which you may be trying to update more than one row. However and on the rare occasion, >0 is required where this has also happened to me before; that is the reason of my answer.

affected_rows() uses the connection, not the one for the query.

• http://php.net/manual/en/mysqli.affected-rows.php

Plus, if you're storing plain text passwords, use password_hash() since it's much safer:

• http://php.net/manual/en/function.password-hash.php

Sidenote: If you do decide to move over to that function, make sure that you do not manipulate the password at all. Hashing/verifying it takes care of that and you may be doing more harm than good in doing so and limiting passwords.

I.e.: A valid password of test'123 would be interpreted as test\'123 and rendering FALSE when using real_escape_string() for example.

Or you may still be using hash_hmac as per your other question Comparing/check if correct Password from mysqli database [hash_hmac]

and a prepared statement:

• https://en.wikipedia.org/wiki/Prepared_statement

It is also best to add exit; after header. Otherwise, your code may want to continue to execute.

header("location: ../index.php");
exit;

How to know how many mysql lines updated

mysql_affected_rows : Get number of affected rows in previous MySQL operation :

...
$updated_count = mysql_affected_rows();
...

Put above statement after your queries to count the affected rows.

How do I tell when a MySQL UPDATE was successful versus actually updated data?

Have a look at mysql_affected_rows()

It should tell you if anything was actually updated as opposed to nothing was successfully updated resulting in a return of true.

php.net says:

mysql_affected_rows()

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

You could use the following to achieve your desired results:

if($this->db->affected_rows() >= 0){ }

How do I test if my MySQL update query was successful in CodeIgniter?

As commented, have you tried $this->db->affected_rows()?

That will tell you how many rows were updated.

---

Related Topics