Update several fields at once. Best way to do this?

TheJuice

Hi,
Let's say I have a table in MySQL which represents an object:

TABLE: net_item, with the following fields:
id (primary)
provider_ref
install_cost
monthly_cost
...

If I want to make an update page, I'll have several input fields with current values in the database, and the possibility to change them.
One single Update button will make the changes in the database.

But what is the best way to do this?
I see 3 ways:
1. Use REPLACE in the MySQL query.
2. UPDATE net_item
SET provider_ref=$pr,
install_cost=$ic,
monthly_cost=$mc
WHERE id=$id
3. same as 2, but only for the fields that have actually been changed. Bu then I'll have many many if/then/else in my PHP code, to SET only if needed.

The solution I'd prefer is 3, what do you think?

nnichols

To be honest there is little in it between 2 and 3, but probably better application logic to do 3.

planetsim

2 is your best chance. How are you gonna find out if any data been changed unless you start doing if's

Now although it sounds economical, will it really be an advantage to you to have 10extra lines of code that really arent even nessacary

TheJuice

I thought of something:
if I always use REPLACE instead of INSERT and UPDATE, I can reuse the same functions for both ADD and edit rows to my table...

So I think I'll try to use the first solution, and if I finally don't think it's fit, I'll use the second one.

Thanks for your suggestions.