Member Avatar for acornist

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?

  • 3 Contributors
  • 3 Replies
  • 96 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by vssp

Dani AI

Generated

— two practical patterns work well here: 1) do a conditional in-place update that only writes a new template value when the room still has the old template value (or is empty), or 2) change the data model so a room only stores overrides and otherwise inherits from the template. The first is the least-invasive; the second is more robust long-term.

A safe SQL pattern for the in-place approach uses a conditional per column so many attributes can be updated in one statement without overwriting custom values. For example, a single UPDATE can set each attribute to the new template value only when the current room value equals the old template value (or is NULL):

UPDATE room_table r
JOIN template_table t ON r.template_id = t.id
SET
  r.attrA = CASE WHEN r.attrA = ? OR r.attrA IS NULL THEN ? ELSE r.attrA END,
  r.attrB = CASE WHEN r.attrB = ? OR r.attrB IS NULL THEN ? ELSE r.attrB END
WHERE r.template_id = ?

Build that SET clause dynamically in PHP so you can handle dozens of attributes. Example pattern using PDO and positional parameters:

$sets = [];
$params = [];
foreach ($changed as $col => $pair) { // $pair = ['old'=>..., 'new'=>...]
  $sets[] = "`$col` = CASE WHEN `$col` = ? OR `$col` IS NULL THEN ? ELSE `$col` END";
  $params[] = $pair['old'];
  $params[] = $pair['new'];
}
$sql = "UPDATE room_table r JOIN template_table t ON r.template_id = t.id SET " . implode(", ", $sets) . " WHERE r.template_id = ?";
$params[] = $templateId;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

Notes and cautions: run this inside a transaction on a copy first, apply in batches for large tables to avoid long locks, ensure comparisons respect types and collation, and keep an audit log of applied diffs. For future-proofing, consider a normalized attributes/overrides table so template edits simply change template rows and rooms only store exceptions — that design makes propagation and auditing much easier. Ignore the smiley-only posts from and ; focus testing on the conditional-update or normalization approach above.

Recommended Answers

All 3 Replies

Member Avatar for himanjim

:) :'( ;) :-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:

Member Avatar for himanjim

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

Member Avatar for vssp

:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.