mysql update not updating if the record doesn't have to be changed

[jackvull]

On some updates you get the follwoing in MySQL if the record is found but the update column doesn't need to eb changed:
Rows matched: 1 Changed: 0 Warnings: 0

This causes a problem when you then use mysql_affected_rows() in PHP as it reports 0

Is there a way to code around this without adding an extra call to the DB first to see if the record is there?

[IamStang]

Have a look at [php.net ]

It might be what you need in this situation.

Hope it helps.
IamStang

[jackvull]

Hi
Thanks for the reply.
Unfortunately mysql_num_rows only works with select statements.
I am trying to do an UPDATE statement.

Of course, I could runa SELECT before to see if the record exists but there must be a better way of doing this. Other database servers update the record whether the data has to change or not, so wondering if this can be done with MySQL.

[coopster]

If there is nothing to be updated then why do you care?

[jackvull]

It's an activation page where people activate their accounts from an email link.

So there 2 times it would try an update and would update nothing:
1) if the email link is incorrect or if someone is trying to hack it somehow; or
2) if it has already been activated and they are just clicking the email link agaion by mistake or refreshing th browser.

I nee to know which of the 2 is happening to present the correct message to the user.

[StupidScript]

In either case, using

mysql_affected_rows

would provide the same clue ... either a/some row *had* been affected ... or not. Neither would tell you which of the 2 scenarios you describe had occurred.

At least you could try:

--> [i]update query[/i]

if (!mysql_affected_rows($Uq)) {

--> [i]select query to see if row does exist [/i]

if (!mysql_num_rows($Sq)) {

--> [i]record does not exist ... hack attempt?[/i]

} else {

--> [i]record exists, and was not updated ... already active/mistake?[/i]

}

} else {

--> [i]record was updated[/i]

}

[coopster]

OK, fair enough. Then think of how mysql_affected_rows() [php.net] works and see if you can apply the logic to your editing ...

mysql_affected_rows() returns the number of affected rows on success, and -1 if the last query failed. As you already mentioned, when using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query, zero.

So, if your query is running on a 1-1 match (a single row PRIMARY KEY UPDATE, and you know this because you are writing your statement this way) then you only have three possibilities ...

1. 0 = No row updated
   
2. 1 = 1 row updated
   
3. -1 = query failed
   

<added> Thanks btw, StupidScript -- didn't realize you had snuck one in there ;-)

[jackvull]

Thanks but I don't think this will work as the 2 scenarios where an update can occur will result in the wuery run ning successfully but not updating anything.
For example a row in the table (assume this is the only row):
CustomerID Activated
1 0

The user runs the activation script which is:
(1)
UPDATE Table SET Activated = 1 WHERE CUstomerID = 1
This updates 1 row and myswl_affected_rows returns 1

(2)
UPDATE Table SET Activated = 1 WHERE CUstomerID = 2
This updates 0 rows and myswl_affected_rows returns 0

Now the table has changed to this as query 1 ran successfully (assume the below runs becasue they clicked twice on the link or refreshed the browser):
CustomerID Activated
1 1
(3)
UPDATE Table SET Activated = 1 WHERE CUstomerID = 1
This updates 0 rows as affected is already 1 and myswl_affected_rows returns 0.

Now, I need to know the difference between 2 and 3.
I think I will go for the SELECT statement before running the update to see if a record exists. I just didn;t think this was goof programming as it increases the number of round trips to the database and if there was a way of doing it with PHP or mysql...even returning the number of rows matched rather than updated then that might be better?

[coopster]

All right. Then you may want to have a look at the mysql_info() [php.net] function. It is the PHP interface to the MySQP API of the same name. Should give you what you are truly looking for.