UPDATE on changed fields

[cavey5]

I have an html form that is populated from my database so that users can change their data. What is the correct syntax to do an UPDATE query and only change the variables that the user updated, leaving the rest alone? Do you just UPDATE * and just overwrite the unchanged variables with the same value, or is there a cleaner way to pull this off?

[btherl]

UPDATE table SET field1 = 'value1', field2 = 'value2' WHERE key = 'key_value'

 

That's actually the more common update syntax, which allows natural updating of only a few fields.  Key is whatever column you use to identify a row (probably username or user_id).

[btherl]

It just occurred to me.. does the user submit ALL fields, and you want to test which are different before updating?  In that situation, the safest way is to check every field individually and act appropriately.  The simplest way is to update them all.  It depends on how much effort you want to expend.

[cavey5]

That's what I wanted to know, Ill just update all. Just wondering if there was a built in function to test for changes. Guess not.

[bubblegum.anarchy]

The PHP mysql_affected_rows() returns the number of rows affected by a query that modifies a table.

[btherl]

Will that tell you a row is affected if you update it to the exact same value it was before?

[bubblegum.anarchy]

no

[cavey5]

mysql_affected_rows() just returns an integer count right? It doesn't / cannot specify which records changed? I guess if you wanted to keep the old data and new, you could append the old data with the new, and then cut the old and new apart, and display them separately.

[bubblegum.anarchy]

mysql_affected_rows() just returns an integer count right?

 

yes.

 

It doesn't / cannot specify which records changed?

 

no it can't.

[btherl]

Apparently this function can tell you how many rows were matched, in addition to how many were changed.  That may be good enough for some purposes.

 

http://www.php.net/manual/en/function.mysql-info.php