distinguishing results in MySQL

[sssweb]

I have a php page that updates my MySQL DB; I've got the update code working, but my results display page has a bug. I want the page to work like this:

1) If there is a DB error, display an error message.
2) If there are no affected rows because the update is the same as the current field, display 'no change'
3) If there is no update because no matching record is found, display 'no record found'
4) If update is successful, display 'success'

The problem is distinguishing between cases 2 & 3. I tried using affected_rows, but it is 0 in both cases. I also tried num_rows, but it is 1 in both cases.

Is there a simple way to distinguish between these, w/o having to first check whether the record exists, then updating it?

[ZydoSEO]

I'm not sure I see a difference in #2 and #3. I don't think #2 ever happens in a DBMS. I'm not sure what database you're using but in all the DBMSs I've ever worked with (SQL*Server, Oracle, Sybase, various small DB packages), the DBMS does not care what values you include in the UPDATE.

By that I mean the DBMS doesn't inspect the values already in the DB for that row and compare them to the new values you're including in the UPDATE to determine if it needs to do the update. It always attempts to perform the update. If there is a row (or rows) that matches the WHERE clause on your update, it sets the row(s) to the new value(s) even if the row already contained the exact same values you are trying to SET.

For example, if I have a table tMyTable with columns MyKey, Col1, Col2, Col3:

if tMyTable has a row in it where

MyKey = 1
Col1 = 2
Col2 = 3
Col3 = 4

If I issue the following statement

UPDATE tMyTable
SET Col1 = 2, Col2 = 3, Col3 = 4
WHERE MyKey = 1

then the DBMS is still going update the row with the same values that were already there and the rows affected will = 1.

It does not compare the new values you are SETting to those already in the DB and come to the conclusion that it doesn't need to update it because they already have the same values.

[edited by: ZydoSEO at 6:12 pm (utc) on Jan. 16, 2008]

[sssweb]

Thanks for your reply; I'm using MySQL. I can't say for sure whether my DB performs the update or not if the new value is the same as the current one, but I do know that the affected_rows = 0 if there is no change from the current value -- I verified this in my server control panel.

It shows 1 affected row if there is a changed value, and 0 affected rows if there is either no changed value or no matching record found.

[ZydoSEO]

I see in the docs that "If you set a column to the value it currently has, MySAL notices this and does not update it."
It's about half way down this page:

[dev.mysql.com...]

[sssweb]

OK, so I guess there's no way to distinguish between the two, unless there's some indicator when the DB doesn't find a matching record to update.

[phranque]

i think you want to use mysql-info() [dev.mysql.com].