February 19th, 2013, 09:25 PM
Swapping field data, need help - logic problem
Hi all. I'll try and explain this as best I can. I have the following 4 fields in each mySql record:
The user has the ability to delete any of the options at any given time. They become " " or null.
My issue is, for example, if there is data in all four fields, and the user deletes the data for option 2, now there is a blank spot:
I would like to move options 3 and 4 down, to become options 2 & 3, so it ends up looking like this:
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.
February 20th, 2013, 01:24 AM
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.
Last edited by E-Oreo; February 20th, 2013 at 01:29 AM.
February 20th, 2013, 02:12 AM
Yeah, that is how I was feeling earlier too. The person directly above you, of course, has the right answer, and I should have done it this way to begin with.
Originally Posted by irenetuer2
Thanks for the help all.
Comments on this post