Chapter 8: Script 8.7 - Password.Php, Mysql_Affected_Rows Question

[markifornia]

This question pertains to Chapter 8: Script 8.7, Line 55

 

if(mysqli_affected_rows($dbc) == 1) { // If it ran OK

 

 

Then at the end of the chapter in TIPS, it says:

 

"If an UPDATE query runs but does not actually change the value of any column (for example, a password is replaced with the same password), mysqli_affected_rows() will return 0."

 

OK, I fully understand this. But...

 

How do we modify the code so that if a user accidentally puts in the same data for each field, it outputs another message instead of outputting a SYSTEM ERROR?

 

I'm going chapter by chapter so I don't know if this is addressed in later chapters, if anyone could advise some sort of direction, even loose logic, I can take a stab at it.

 

My idea would be to create a new else statement:

 

$q = "SELECT pass FROM users WHERE pass = '$np'";

 

$r = @mysqli_query($dbc, $q);

 

while($row = mysqli_fetch_array($r, MYSQLI_ASSOC) {

 

if ($row['pass'] == $np) { If the current password and the new password match, then echo something

 

// echo something

 

}

 

}

 

I could be off here, so any advise appreciated. thanks

[Larry]

Well, you could select the current password and compare them, or you could just confirm that the query successfully ran.

[markifornia]

Thanks Larry, I got to chapter 9 on page 274 there is a suggestion to change the error message to indicate that no alterations where made if mysqli_affected_rows() returns 0.

 

Can't I just say their password has been updated even though behind the scenes it technically didn't?

[Paul Swanson]

From the PHP Manual:

 

Return Values

 

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where (sic) updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error.

 

So you could do something like:

if (mysqli_affected_rows ($dbc) == 1) { // If it ran OK
 echo "Password Updated";
} elseif (mysqli_affected_rows ($dbc) === 0) { // use three equal signs so there is no confusion with a false return
 echo "Password unchanged because it was the same as on record";
} elseif (mysqli_affected_rows ($dbc) == -1) { // update query failed
 echo "Oopsie! System error.";
}

[markifornia]

I didn't even know the comparison can take a negative value such as -1. This is the solution I was looking for.

 

 

} elseif (mysqli_affected_rows ($dbc) == -1) { // update query failed

 

Thanks Paul!

[masterlayouts]

I asked myself the same question the other days and because when updating a record it probably make no difference if the user updates or not the record (unless it is a mandatory update, so the user must update the record), I guess it will be easier to write:

 

 

if (mysqli_affected_rows ($dbc) >= 0) {
  // updated or canceled
}
elseif {
  // system error
}

 

I guess checking for the error works as well:

 

if (mysqli_affected_rows ($dbc) < 0) {
  // error
}

[markifornia]

Masterlayouts, I like the extra step you've taken to investigate further. Paul's suggestion also works.

 

But with your suggestion, it would make sense to combine the notification into one condition like so:

 

if (mysqli_affected_rows($dbc) >= 0)

 

I tried using your method on the edit_user.php script, and after a few tries solely on user experience I found that it can also be confusing if the notification says the submitted info has been edited even though it technically wasn't. I am just speaking in terms of user experience though.

 

We must consider reasons why a user might be editing his/her information. Our first realization is that a user would want to update his information. However, there are instances where a user is unsure whether his/her info is correct to begin with. Therefore the user is forced to reset it. But with Paul's method, it gives the user a peace of mind that it was correct all along, and nothing had to be changed (or has been changed). Very confusing explanation, but to be considered.

 

Both noteworthy scripts. thanks good work.

[markifornia]

Back to Paul's method, I didn't see this at first, I missed something very important:

 

 

} elseif (mysqli_affected_rows ($dbc) === 0) { // use three equal signs so there is no confusion with a false return

 

I've never used the three equal signs for comparisons, while the comment says so there is no confusion with a false return. Is this a special note for the programmer to avoid confusion with the code in question?

[Larry]

Yes, this is definitely the kind of thing that can trip up programmers at all levels.