I have a table containing various information describing room requirements for an architectural project with about 500 different rooms. Each record in the table represents a single room and its attributes. There are about 200 attributes in all, but each room uses only a small subset of those attributes.

To make data entry easier, each record is created from a basic template for the room type (meeting rooms, washrooms, offices, etc.).

Here are the two main tables:

room table: rds
fields: id, template, room_name, department, attr1, attr2, attr3, ......

template table: templates
fields: template_id, template_attr1,template_attr2,template_attr3, .....

The challenge is that when a template is edited, the rooms based on the template should reflect the updates, unless a field in an individual room has been altered from the template (thus reflecting a customization). So if the template for offices is updated to reflect changes in three attributes, all those attributes should be changed in rooms based on the office template unless a change has already been made.

I can get the old value from the original state of the template table and get the new value from $_POST generated by the template update form. The problem is how to update multiple columns in multiple rows of the room table (rds) while applying this logic.

I guess it should be something incorporating the mysql update statement using REPLACE:

UPDATE rds SET rds.[I]fieldname [/I]= REPLACE(rds.[I]fieldname[/I],$oldvalue,$newvalue) WHERE rds.template = templates.template_id;

That works with one attribute, but how to do it in PHP with many attributes is my problem.

Any suggestions?

:) :'( ;) :-O :@ :$ :-/ :* :?: :angry: :zzz: :swe:icon_sad: :icon_frown: :icon_rolleyes: :icon_idea: :icon_redface: :icon_redface: :icon_lol: :icon_biggrin: :icon_biggrin: :icon_razz: :icon_razz: :icon_cheesygrin: :icon_question: :icon_confused: :icon_neutral:

  1. :icon_cool: :icon_mrgreen: :icon_mad: :icon_exclaim: :icon_smile: :icon_evil: :icon_surprised: :icon_eek:

at: :twisted:

Hey the post above was just to get those smileys
I'm embedding them in my website
sorry for inconvenience

This article has been dead for over six months. Start a new discussion instead.