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?