Discuss Swapping field data, need help - logic problem in the PHP Development forum on Dev Shed. Swapping field data, need help - logic problem PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
The only way I could figure out to do this would be to have one sql statement to read and one update query for each subsequent option number. Again, as an an example, if the user deletes record one, I would need 3 updates:
update set option1=$row['option2'] where fooo....
update set option2=$row['option3'] where fooo....
I am wondering if there would be a much more effective and less time consuming way of doing this, because as it is, I'll need to write out up to 1-3 updates every time someone deletes any option other than #4.
Hope I made that fairly clear, if not, please ask for clarification on what you don't understand.
Time spent in forums: 2 Months 2 Days 20 h 11 m 10 sec
Reputation Power: 7170
For good database design, you should never have multiple separate columns that store data with the same meaning. Instead, you should have a separate table that contains multiple rows. This will help you avoid logic problems like that.
Instead of having a table that is:
(1) user id | username | option1 | option2 | option3 | option4
You have two tables:
(1) user id | username
(2) user id | option
Your second table will have 0-4 rows per user id in it. If a user adds a new option, you insert a new row. If a user removes an option, you delete a row.
In database design this concept is called normalization. Parts of it are simple, and parts of it are not, but the simple parts are especially crucial to well designed databases.
It is one of those things were you're only allowed to violate the rules once you understand them.