MySql UPDATE command not detecting errors

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bowlesj
Forum Contributor
Posts: 179
Joined: Fri Jul 18, 2014 1:54 pm

MySql UPDATE command not detecting errors

Post by bowlesj »

Hi, I have just become aware that the MySql UPDATE command is not returning an error if I force the command to try and update a record that is not on file. The experimental code I am using to try to catch the error is below. Using the NetBeans debugger to look at the values, I complete an UPDATE command with a valid record key then I force the record key to 9999 that does not exists. The error related fields return exactly the same values even with the submission of 9999. I have tried a variety of ways to catch the error with no luck. I checked the syntax and all looks well. The exact same types of checks (the ones with the error handler below) catch a SELECT trying to get a record not on file. I suspect the same problem is occurring with the INSERT command if I try to insert a record that already exists. I am using XAMPP with PHP 5.5.11. I am using MyIsam. I am stumped for what to check next. Does anyone have any ideas.

Thanks,
John

Code: Select all

$MyString = "
   SELECT 
      tblMemberMaster.fldMM_Key,
      tblMemberMaster.fldMM_User_ID
  FROM
      tblMemberMaster;
";
$result = @mysqli_query($con,$MyString);
if (!$result) {
   trigger_error("Error" . mysqli_error($con),E_USER_ERROR);
}
while($row = mysqli_fetch_array($result)) {
   $fldMM_Key = $row['fldMM_Key'];
   $fldMM_User_ID = $row['fldMM_User_ID'];
   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_User_ID = '$fldMM_User_ID'
      WHERE
         fldMM_Key='$fldMM_Key';
   ";
   if (!mysqli_query($con, $sql)) {
      $ResultMessage = "Error updating member file. " . mysqli_error($con);
      trigger_error($ResultMessage,E_USER_ERROR);
   }
   $MyErrorDesc = mysqli_sqlstate($con);  //Contains 00000
   $MyErrorNumber = mysqli_errno($con); //Contains 0

   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_User_ID = '$fldMM_User_ID'
      WHERE
         fldMM_Key='9999';
   ";
   if (!mysqli_query($con, $sql)) {
      $ResultMessage = "Error updating member file. " . mysqli_error($con);
      trigger_error($ResultMessage,E_USER_ERROR);
   }
   $MyErrorDesc = mysqli_sqlstate($con); //Contains 00000
   $MyErrorNumber = mysqli_errno($con); //Contains 0
} //End of the while loop.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySql UPDATE command not detecting errors

Post by Celauran »

UPDATE won't return an error if your WHERE clause doesn't match anything, it will just return 0 rows affected.
bowlesj
Forum Contributor
Posts: 179
Joined: Fri Jul 18, 2014 1:54 pm

Re: MySql UPDATE command not detecting errors

Post by bowlesj »

Thanks Celauran. After your response I did a little research and tried the mysqli_affected_rows command. In every case I get -1 as the result even though the actual command to update the database worked as it should have. I gather the -1 means an error but there is no error (and I ma pretty sure I am using the command correct as shown below). It seems as though my version of MySql has a bug. However, i have some good news about the insert command I mentioned may also have a problem. I had an insert command give me a correct response in that it detected the record was already on file and it gave me an error.

Code: Select all

   $FirstX = $fldMM_FirstName . "X2";
   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_FirstName = '$FirstX' 
      WHERE
         fldMM_Key='$fldMM_Key';
   ";
   $UpdateResult = mysqli_query($con, $sql);
   if (!$UpdateResult) {
      $ResultMessage = "Error updating member file. " . mysqli_error($con);
      trigger_error($ResultMessage,E_USER_ERROR);
   }
   $cnt = mysqli_affected_rows($con); //This always gives me -1 even though the actual update worked.
Post Reply