0

When I try to update mysql from php with the same value as the existing one in the data then affected_rows returns zero. Which means the database operation went successful anyway. So I want to return the successful message, irrespective of the same value as before. but based on the affected_rows value I can't say anything. So in this situation how should i check that the operation was successful anyway? The below will be an example case....

$query='update chapter set ChapterName=? where ChapterId=?';
$stmt=$mysqli->stmt_init();
$stmt->prepare($query);
$stmt-> bind_param('si', $chaptername, $chapterid);
$stmt->execute();
if($stmt->affected_rows>0)
{return true;}

EDIT I have removed the typos.. that was my mistake

whatever
  • 332
  • 2
  • 16
  • `if(condition) { do something } else{ do something else }` – Funk Forty Niner May 13 '15 at 16:06
  • @Fred-ii- ??? what do you mean? – whatever May 13 '15 at 16:06
  • as per your edit, you're doing that now, aren't you? I'm obviously not grasping the question correctly then. – Funk Forty Niner May 13 '15 at 16:10
  • @Fred-ii- I believe they want rows matched instead of rows affected. – Uueerdo May 13 '15 at 16:12
  • No @Uueerdo, the OP wants to know the number of rows affected by the update. – Jay Blanchard May 13 '15 at 16:12
  • @JayBlanchard that would make the inclusion of "same value as the existing one" in the question irrelevant. – Uueerdo May 13 '15 at 16:15
  • yes I want to know the rows matched.. – whatever May 13 '15 at 16:17
  • that's a typo btw `if($smt` – Funk Forty Niner May 13 '15 at 16:17
  • Replacing with the *same value as the existing one* doesn't occur and therefore implies no success. It is nonsensical as only a negative number would be a failure. – Jay Blanchard May 13 '15 at 16:18
  • @JayBlanchard that is exactly the problem in some cases though. Often, the rows matched is what is wanted, not the rows that actually change. I know from experience if you don't set that option on an ODBC connection, it can cause all sorts of headaches on MSAccess forms the use "linked" tables. – Uueerdo May 13 '15 at 16:21
  • 1
    No doubt @Uueerdo, and a separate function would have to be used to determine what was matched. A 0 return of affected rows does not indicate success though. Perhaps the OP wants to look at `MYSQLI_CLIENT_FOUND_ROWS` in `real_connect()`. I've never used it with an UPDATE, so I don't know if it would return a match. – Jay Blanchard May 13 '15 at 16:26
  • @whatever if `0` is returned that *is not an indication of success* because even if there is a match *nothing gets replaced*. – Jay Blanchard May 13 '15 at 16:30
  • @JayBlanchard no I am not going for zero.. does my code say so?? – whatever May 13 '15 at 16:31
  • That is your very first sentence @whatever Maybe you need to clarify what you're trying to do? – Jay Blanchard May 13 '15 at 16:33
  • @JayBlanchard I think you did not understand me.. I am saying that `affected_rows` return zero as usual if you try to update the field with same value as before.. – whatever May 13 '15 at 16:36
  • OK - I understand that. What is it you want to do? – Jay Blanchard May 13 '15 at 16:42
  • @JayBlanchard to check if the row matched in the database or not?? if it actually did not update, as the values are the same, but I want to notify the success message. so if do not get greater than zero value, I still want to display the success message because the operation reached to the row but did not deem it necessary to update.. – whatever May 13 '15 at 16:55
  • 1
    Then look at `mysqli_real_connect` and `MYSQLI_CLIENT_FOUND_ROWS` as stated before. It will show 1 for a match and 0 for no match. Then you have to do a separate test for affected rows to see if a change was actually made. If match, success, if no match was there an update? Yes? Success. – Jay Blanchard May 13 '15 at 16:57
  • @JayBlanchard Okay now I got it... writing the code first will have to check later so will employ this procedure then.. I asked early while I am writing code, because the reset of the code was depended on this sollution.. Thanks very much – whatever May 13 '15 at 17:08

2 Answers2

1

Depending on your connection method, there is usually a connection option to return matched rows instead of changed rows.

If you're using mysqli this might be the answer: https://stackoverflow.com/a/5289535/4104224

This one looks a little more thorough and explicit though: https://stackoverflow.com/a/2925972/4104224

Community
  • 1
  • 1
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • @whatever answer has been updated with links to specific answers that may help – Uueerdo May 13 '15 at 16:16
  • yes checked it.. it will help me to narrow down now.. Thanks.. but not clear yet.. – whatever May 13 '15 at 16:20
  • I figured since they were "internal" to stackoverflow, it would be safe; and would've felt like I was plagiarizing repeating it. Plus, I consider my first sentence the main answer as it applies more generally... and was given before the question was elaborated. – Uueerdo May 13 '15 at 22:11
0

You have to attach affected_rows() to the connection, not the statement:

if($mysqli->affected_rows >= 1) {
    // success
} else {
    // failure
}
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119