[RESOLVED] mysql update query fails

[David_P]

Sure wish the "remember me" was the default setting for logging in. Save a lot of time and trouble. Let me do this again.

I'm running php5 and mysql5 I have edit.php page that takes info from table and populate fields. When submitted it goes to update.php and as the name implies it updates the table with altered info.

If you change just one tiny thing on edit.php page -> like one number in a phone number -> and hit submit it updates fine. But if you load edit.php and hit update without changing anything the update query fails.

I use mysql_affected_rows() to show success or failed to update message. Again no change it fails with no query error given with

or die ($query\n<br />mysql_error());

[bradgrafelman]

You didn't ask any questions, nor did you show us any (useful and/or valid) code, so... in case you were expecting us to be able to help you in some way, you might want to do both of those.

Additionally, note that the entire [man]mysql[/man] extension is severely outdated and has been deprecated in favor of newer extensions such as [man]MySQLi[/man] or [man]PDO[/man].

[sneakyimp]

I wonder if your code might be checking the number of affected rows rather than the success/failure of your query? Remember that an update query which changes nothing will affect no rows.

[David_P]

Yeah, I just ordered a book from Amazon that is about Php 5, Mysql 5 and MySQLi. My edit/update pages didn't give me any trouble with Php & MySQL 4, so something in MySQL has changed. I've had Php 5 on my server but MySQL 5 is new so that must have something to do with why all of a sudden my website is misbehaving.

[David_P]

This forum must have changed to a wider resolution because I'm having to scroll horizontally now to read the posts.

[David_P]

sneakyimp -> The query is like this:

$query = "UPDATE table SET f_name='$f_name', l_name='$l_name', phone='$phone', email='$email' WHERE user_id='$user_id'";
$result = mysql_query($query) or die("Query: $query\n<br />MySQL Error: " . mysql_error());
if (mysql_affected_rows() == 1)
{
     echo "Update Successful";
}
else
{
     echo "Update Failed";
}

The edit page form is being populated with the existing database information. Even if nothing is changed it should just re-enter the information since there is no script to check and see if the posting information is the same as what is already in the database and if so turn it away. Unless this is a new automatic "feature" in MySQL 5.

[Weedpacket]

If nothing changes then mysql_affected_rows() returns 0 - because that's how many rows were affected by the change.

See what it says about return values when using UPDATE on the [man]mysql_affected_rows[/man] manual page.

[David_P]

Sure as the world, there it is...

When using UPDATE, MySQL will not update columns where the new value is the same as the old value.

So what would be better to use in this instance?

[Weedpacket]

David P;11017169 wrote:

Sure as the world, there it is...

So, just like sneakyimp said.

So what would be better to use in this instance?

That depends on what you are trying to do - you're the one writing this, after all. Judging from the messages you display, am I right in guessing that you're wanting to check for an error? There may be a function for that.

[sneakyimp]

Whether or not you consider it a success or failure is entirely up to what you expect to happen. If a row should be updated but non is, that sounds like failure. If someone "updates" their mailing info without changing anything, I would not consider that a failure -- just obsessive behavior on the part of the user. In the latter case, you should probably, as Weedpacket suggested, check for an error condition when the query runs rather than checking to see if any rows are affected.

[traq]

also consider that checking if the user changed any fields, before querying mysql, could save you the trouble of trying to figure it out afterwards (as well as a round-trip to the D😎.

[bradgrafelman]

traq;11017233 wrote:

also consider that checking if the user changed any fields

... something which could be done on the client side of things, meaning you could even eliminate the trip to the server altogether.

[traq]

indeed.

[Weedpacket]

bradgrafelman wrote:

... something which could be done on the client side of things, meaning you could even eliminate the trip to the server altogether.

And a server-side check can also eliminate the database hit, if you cache the current values in the session at the same time you send them to the client.

[David_P]

Lets say that someone has already entered information into the database. They then decide to edit that information. But once they get to the edit form and read what what they had already entered and decide to just leave it as is -> but rather than clicking on a button that takes them back to the starting page, they click on the update button they'll get an error message because PHP 5 will not reenter the same information.

Checking a single entry like say (cat) is easy, but checking to see if the contents of a POSTed textarea is identical to what is in the mysql field is not.

$duty_id = $_POST['duty_id'];
$_SESSION['duty'] = $_POST['duty'];

$q = SELECT duty FROM duties WHERE duty_id='$duty_id'";
$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc));
if ($r === $_SESSION['duty']) { // also tried ($r == $_SESSION['duty'])

echo "Information is the same so it does not need updating. <a href='start.php'>Return to Starting Point</a>";

} else {

$qu = "UPDATE duties SET duty='" . $_SESSION['duty'] . "' WHERE duty_id='$duty_id'";
$ru = mysqli_query($dbc, $qu) or die(mysqli_error($dbc));
if (mysqli_affected_rows($dbc) == 1) {

echo "Updated!";

} else {

echo "ERROR! The same information cannot be re-entered.";

}

}

I've tried this and several other ways of checking to see if the information is the same but it breezes right past them and goes straight for the update query and the error message.

If the purpose of PHP 5 not re-entering in the same data in an update is to save time and resources, but you have to perform an additional query to test the MySQL data against the posted variable data, then what have you gained? It seems this would take up more time and/or resources than just simply re-entering the same data.

[bradgrafelman]

David P;11017497 wrote:

PHP 5 will not reenter the same information.

PHP (any version) doesn't enter any information on its own... it does exactly what you tell it to do.

David P;11017497 wrote:

Checking a single entry like say (cat) is easy, but checking to see if the contents of a POSTed textarea is identical to what is in the mysql field is not.

Who said you had to check what was in the database? You already know what was in the database when you first generated that form, so you could either a) store that data in a session, or b) store that data in hidden form elements or in cookies. If you get a submission where the POST'ed data matches the data you previously used to generate the update page, then you know that the user didn't change anything - thus, there's no reason to even bother connecting to your DB, let alone trying to query it in any way.

But again, you could accomplish this quite easily on the client side of things using Javascript, meaning you could save even more resources by not allowing the form to be submitted in the first place.

David P;11017497 wrote:

if ($r === $_SESSION['duty']) { // also tried ($r == $_SESSION['duty'])

Neither one of those comparisons makes any sense; [man]mysqli_query/man is going to (hopefully) return a resource, yet you're trying to compare it to a string (POST'ed data).

David P;11017497 wrote:

If the purpose of PHP 5 not re-entering in the same data in an update is to save time and resources, but you have to perform an additional query to test the MySQL data against the posted variable data, then what have you gained?

Not much (i.e. probably nothing), which is why I never suggested doing that.

[traq]

Just to clarify, [man]MySQL_num_rows/man returning 0 when no rows were actually updated is

a) not a new behavior - it has always done so

b) not a behavior of PHP - it's the value that MySQL returns.

[David_P]

I just changed and tested my script and I have it working. I was pulling the information out of the database to compare it to the posted variable instead of testing for identical content in the query.

$q = "SELECT * FROM duties WHERE duty_id='$duty_id' AND duty='".$_SESSION['duty']."'";

This works and satisfies my immediate need, but as some suggested, checking for any change of content prior to the query also makes sense so I'll experiment with that some.

I don't seem to recall earlier versions of PHP or MySQL throwing errors when attempting to update the database with the same information. Maybe it did and I didn't notice, although it it wasn't working I'm sure someone would have notified me. Hmmm. This is why I thought that this was new behavior.

I just got my book in on PHP 5 and MySQL 5 and have began updating my pages with the mysqli. I'll use the procedural method since it's closer to what I'm used to. I've also started reading about JavaScript and I can see where it could certainly be very beneficial at times.

Anyway, thanks for all the help on this. I know that teaching can be very frustrating at times, but I appreciate your willingness to do it. I'm not immediately sure as to how to mark this topic resolved but I'll look into that more after I make this post.

[bradgrafelman]

David P;11017519 wrote:

I don't seem to recall earlier versions of PHP or MySQL throwing errors when attempting to update the database with the same information.

That behavior has certainly been around for several versions (and, subsequently, several years). From the MySQL 4.1 manual for the UPDATE query:

MySQL 4.1 Manual wrote:

If you set a column to the value it currently has, MySQL notices this and does not update it.

(It follows that if you do that for all columns in a given row, none of the columns in that row are updated, thus there are 0 affected rows.)

[Weedpacket]

DavidP wrote:

I don't seem to recall earlier versions of PHP or MySQL throwing errors when attempting to update the database with the same information. Maybe it did and I didn't notice, although it it wasn't working I'm sure someone would have notified me. Hmmm. This is why I thought that this was new behavior.

And it's not throwing errors now. Your code is saying that there is an error.