#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    31
    Rep Power
    7

    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:

    option1
    option2
    option3
    option4

    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:

    option1: foooo1
    option2:
    option3: foooo3
    option4: foooo4

    I would like to move options 3 and 4 down, to become options 2 & 3, so it ends up looking like this:

    option1: foooo1
    option2: foooo3
    option3: foooo4
    option4:

    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....

    etc.

    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.

    Thank you.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep 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.

    For Example:

    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.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    31
    Rep Power
    7
    Originally Posted by irenetuer2
    I am about to pull my hair out trying to figure this out...
    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.

    Thanks for the help all.

    Comments on this post

    • Jacques1 disagrees : Leave your threads open so that others have a chance to reply as well. Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo