Hi people,

 

I am having a bit of an issue with an UDATE query using mySQL. I have a from where the user can change any of the content of a record in my database, obviously it does this using an UPDATE query.

 

The trouble is sometimes the user won't actually change any of the content in the database so the update query will be over writing the record with the same information and it doesn't like this and tells me there is an error in my query! Is there a way around this?

 

The reason the update may be the same as the existing information within the database is because the form may be used to upload an image with the same name as the image name already stored in the database (and if the image has the same name, along with every other field, then it will cause an error in my query)

 

I don't think I've explained this very well but if anybody has any clue as to what I'm going on about it would be much appreciated!

If you are updating it will override. It sounds as though you are inserting a new query with unique fields.

If the posted data is the same as the existing data mysql will not update the row so mysql_affected_rows will return 0. Have a look at the comments for possible solutions.

One solution that would work in this case (same name image upload) would be to add a variable to the file name like time().

 

file.jpg becomes file_17821374.jpg

Can you post the segment of code that tries to do the update?

ahh yeah you guys are probably right in that it does over_ride it but as Mihai suggests mysql_affected_rows() understandably returns a 0 because nothing is actually changed. It was such a long time ago I wrote the code that I didn't check that.

 

I've tried doing what this guy suggests here but am unable to test it as my server is currently down, which also means I cannot show you the query code as it is dynamically created. I will do so when it's back up!

 

Also can someone tell me what the difference between checking mysql_affected_rows() and simply checking whether the variable that ran the query is TRUE

 

e.g.

$result = @mysql_query($query);

if ($result) {
ehco "query succesful";
}

 

as appose to

if (mysql_affected_rows() == 1) {
echo "query successful";
}

Well ...

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

"on success" of sending that is.

 

Here's an example:

$result = @mysql_query("UPDATE table SET field = 'new_value' WHERE id='3'; ");

if ($result) returns TRUE (query ran) even if the row doesn't exist so you can't tell if the value was updated unless you check with mysql_affected_rows().

Thanks for the clarification,

 

I'll have to remember that mysql_query() returns TRUE simply on success of sending!

 

So I ended up going through the comments of that link you offered and this worked

 

It involves setting CLIENT_FOUND_ROWS flag in mysql_connect() which from what I can tell means mysql_affected_rows() will return greater than 0 simply if it has found a match and not only if it has actually changed a record.

 

Cheers for your help!