1

This is more of a theory question. I have a page with a bunch of various textfields, dropdown boxes, etc. Each user has his/her "own page" that can be updated via this update page that I am referring to. He updates the fields at his choosing. It passes about 30 variables (if every field is inputted) to a "preview page". If the person likes the preview page, then they click an "update" button at the bottom of the preview page and all the various variables are updated into the appropriate MySQL table and their "own page" that others see is updated dynamically. (let me know if this explanation isn't clear).

Inserting this information for the first time is easy. However, when a user wants to update only a few of the fields for his page later, this is where I am confused. How do I make the MySQL update query dynamic to recognize an update to ONLY the fields on the page that the user wants to update (while he leaves the other fields blank, thus leaving the old information intact for those columns, and they are disregarded in the update query).

Let me know if what I'm asking doesn't make sense and I'll try again.

Thanks for your help.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
Pete_1
  • 981
  • 3
  • 14
  • 23
  • 1
    ...check to see if the field is blank, and if so, not update it? Where exactly is the coding issue? – Waleed Khan Feb 10 '13 at 05:06
  • Maybe my question is, does anyone know a good script that cycles through the variables to discover which ones are empty and which ones are not? If it were only a few this wouldn't be such an issue. Even once I know that, the mysql update query needs both the column name to be updated and the associated variable. Dynamically updating the column name along with inserting the proper variables to update (that are not left blank) is something confounding me too. – Pete_1 Feb 10 '13 at 05:11
  • You select the page from the database again when the user posts their update and compare, giving you decisions on what to update. Or you just update the lot. What's the harm in that? – Popnoodles Feb 10 '13 at 05:18
  • If leaving a field blank means "do not change" how will the user empty a field that has data? – Popnoodles Feb 10 '13 at 05:20
  • That's a good idea, and you're totally right. I'll think about the old and new page comparisons to see how I'll do that. Thanks. – Pete_1 Feb 10 '13 at 05:25
  • Just present the user with all their old data, and update everything. If you like , you can add a toggle to each form field to make it editable, so reducing the chance of an accidental edit. – Strawberry Feb 10 '13 at 08:38

1 Answers1

0

The easiest way to do this would be

UPDATE MyTable m SET m.f1 = COALESCE(input1,m.f1), m.f2 = COALESCE(input2,m.f2), ....
WHERE m.id = key;

The COALESCE will return the first non-null value (or null if all values are null).

Note that you can insert a default value after the existing field value if you want to force a default.
Like so: 

UPDATE MyTable m SET m.f1 = COALESCE(input1,m.f1,default1), m.f2 = COALESCE(input2,m.f2,default2), ....
WHERE m.id = key;

See: MySQL: how to use COALESCE
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319