1

This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes using multiple queries). So, please hold my hand here, and tell me if this is being done the "right" way?:

Table Breakdowns

SettingsList enter image description here

UserSettings enter image description here

SettingTypes enter image description here

Basically when I add a setting to the API (through the control panel) it gets put into SettingsList, I give the setting a value and a type (so that it actually does something) in the UserSettings table (this will be the only table accessed by the client from the app) . and the setting itself can only be a type specified in the enumeration (of sorts) in the SettingTypes table.

Right now, when I edit a setting from MY administrative control panel (the client will have far less control), I am running an update qiery that looks something like this:

UPDATE `SettingsList` l, `UserSettings` u
SET
l.settingName = 'pow',
l.settingDescription = 'otherTest',
u.setting = 0,
u.type = 1 WHERE
l.settingID = 3 AND u.settingID = 3

Does this look correct? Again, it does work but as most here would agree, just because it works, doesn't mean I did it right.

Well, I may have supplied more information than you need to give me an answer, but how am I doing in my first application with relational tables?

  • Had no idea that even existed. Can I move this? – anwyatt Apr 12 '13 at 22:26
  • 1
    It'll automatically be moved. You can flag it yourself, and ask for moderator attention. – hjpotter92 Apr 12 '13 at 22:26
1

You can use JOIN clause, as mentioned by @tadman

UPDATE `SettingsList` l
JOIN `UserSettings` u
    ON l.settingID = u.settingID
SET
  l.settingName = 'pow',
  l.settingDescription = 'otherTest',
  u.setting = 0,
  u.type = 1
WHERE l.settingID = 3;

This is what you'd call a good programming etiquette. It is fine if you update as you currently are.

2

Usually updates to two tables are done as two different queries to avoid making a mistake and badly mangling both tables.

A combined update is a form of JOIN, so you need to be careful when establishing update conditions. I don't see how this query you've created is any better than two simple queries that achieve the same effect. If you need to make the operation atomic, frame it inside a transaction.

2

Use two queries inside a transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.