11 Replies Latest reply on Jan 7, 2009 6:43 AM by Lumpia

    2 dimensional table

    Lumpia
      I have a excel sheet in which a user can checkbox several options and I am trying to make this web based. Here is an example of what the file looks like:
      [WEEKEND]
      ......................APPLES | PEARS | ORANGES | GRAPES | BANANAS
      RED
      ORANGE
      BLUE
      YELLOW
      GREEN

      [WEEKDAY]
      ......................APPLES | PEARS | ORANGES | GRAPES | BANANAS
      RED
      ORANGE
      BLUE
      YELLOW
      GREEN

      So, to explain, there are [WEEKEND] and [WEEKDAY] sections for a user to checkmark. The fruits up top are the column headers, and the colors of fruits are the row headers. My initial reaction was to create columns in SQL Server 2005 for each checkbox. For example, the column names would be: ApplesRedWknd, ApplesOrangeWknd, GrapesBlueWknd, BananasYellowWkdy, PearsGreenWkdy, etc... this way I could tell if they put a checkmark in the weekend or weekday section. This would be fine if I only had a small set of checkboxes like in this example, but in reality, I have over a hundred checkboxes.

      I'm now leaning towards 'check tables' where instead of combining row and column into one header name, I break out each category in Sql Server into their own tables, and then the values roll up into a main table. For example, there would be a table for fruits:
      Fruit_UID | Fruit_Description
      1 | Apple
      2 | Pears
      3 | Orange

      For Colors of Fruit
      Color_UID | Color Description
      1 | Red
      2 | Green
      3 | Orange

      And a table for Weekend/Weekday
      DOW_UID | DOW_Description
      1 | Weekend
      2 | Weekday

      Then, the main table just stores the UID values and maintains a foreign key relationship to the other tables like so:
      UID | Fruit_UID | Color_UID | DOW_UID
      1 | 2 | 1 | 1
      2 | 3 | 1 | 2

      This makes sense to me, and is probably the best choice. I'm having a problem; however, understanding how I'm going to get these values into the database, and then once in, how to pull them out to display what the user chose.

      I've gone back and forth on how to create the <input type="checkbox"> section, but nothing makes sense. Can someone please describe how I can build the input boxes so that the values sent to the database get entered into the correct tables. Also, once the data is in the database, how can I parse it out so that the right checkboxes are checked. I'm using CFMX 7 if that makes a difference. Thanks in advance for the assistance.
        • 1. Re: 2 dimensional table
          Level 7
          Lumpia wrote:

          > This makes sense to me, and is probably the best choice. I'm having a
          > problem; however, understanding how I'm going to get these values into the
          > database, and then once in, how to pull them out to display what the user chose.
          >

          Good for you, you have normalized you database design and will be much
          happier for it.

          There's nothing that is going to do this without some background logic.
          Basically you need to pull your various sections, columns and rows
          either into one large grouped record set or several individual record
          sets that you then loop over in a nested manner to build the desired
          user interface.

          Your <input...> tags are then going to need to provide the three
          relevant pieces of information either in its name or value or both. A
          common approach would be to name the check box control something and
          then make its value the three required pieces of information in a list.
          Something like <cfinput name="something" value="1_3_5"> which could be
          the check box for section 1, column 3 and row 5.

          You then use this information to create the required inserts, updates
          and deletes on your action pages.

          There can be much more for this type of interface but that should get
          you started down the correct path.
          • 2. Re: 2 dimensional table
            Lumpia Level 1
            Ian. Thanks for the reply and confirming that I'm on the right track. I had already toyed around with the idea of storing values for each checkbox like so: <input type="checkbox" name="weekday" value="1,2,1,3" /> and then on the processing page, was going to break up each value with a listGetAt like: <cfoutput>#listGetAt(weekday, 1)#</cfoutput>. This would give me the first value, and then I could set to to a variable name and store in the database. However, this would be a whole lot of individual coding for over 100 checkboxes. In this example, I'd need 4 listGetAt's per checkbox, times that by 100, and that's 400.

            Perhaps I'm missing a way to loop over this (I hope so) or maybe there is a better way of doing it altogether. Also, let's say I get the values into the database via a list. How can I retrieve the values and put them back in a checkbox. Like so: <cfoutput query="whatever"><input type="checkbox" value="#UID#, #Fruit_UID#, #Color_UID#, #DOW_UID#">? Hmm, but then I somehow have to figure out that this means checked. Don't know. The more I think about it, the more confusing it seems.
            • 3. Re: 2 dimensional table
              Dan Bracuk Level 5
              You are on right track for your db design. You didn't mention anything about user info, and storing their previous selections.

              That additional information will make it easier for you.

              Don't store lists. You will end up with unusable data.
              • 4. Re: 2 dimensional table
                Level 7
                Lumpia wrote:
                > Perhaps I'm missing a way to loop over this (I hope so)

                If you gave all your check boxes the same name, all the selected values
                would be returned to the action page as a comma delimited list. If you
                used something other then a comma to delimit your list of values for
                each check box you would have a nested list that is easy to loop over
                with nested loops.

                <cfoutput>
                <cfloop list="#form.checkBoxName#" index="boxValue" delimiter=",">
                #boxValue#<br>
                <cfloop list="#boxValue# index="data" delimiter="_">
                #data# -
                </cfloop>
                <br>
                </cfloop>
                </cfoutput>

                With this, you just need to include all the necessary data in the list
                stored in the value for each check box.
                • 5. Re: 2 dimensional table
                  Lumpia Level 1
                  quote:

                  Originally posted by: Dan Bracuk
                  You are on right track for your db design. You didn't mention anything about user info, and storing their previous selections.

                  That additional information will make it easier for you.

                  Don't store lists. You will end up with unusable data.


                  Dan, can you elaborate more on what you mean. The checkboxes are already being stored in the database. Are you referring to some session variables or something. Please give specifics, to help me understand better. Thanks.


                  • 6. Re: 2 dimensional table
                    Level 7
                    Lumpia wrote:

                    > Dan, can you elaborate more on what you mean. The checkboxes are already
                    > being stored in the database. Are you referring to some session variables or
                    > something. Please give specifics, to help me understand better. Thanks.

                    I believe Dan was referring to a common, poor database desing where a
                    field in a database stores a list of related values, instead of a proper
                    relational join table.

                    It is not uncommon to find questions on this list where somebody asks
                    something like: "I have a customer table with a category that stores a
                    list of categories like '3,5,13'. How do I join this to my category
                    table to get the category names?
                    • 7. Re: 2 dimensional table
                      Level 7
                      Lumpia wrote:

                      > Dan, can you elaborate more on what you mean. The check boxes are
                      already being stored in the database. Are you referring to some session
                      variables or something. Please give specifics, to help me understand
                      better. Thanks.

                      I believe Dan was referring to a common, poor database design where a
                      field in a database stores a list of related values, instead of a proper
                      relational join table.

                      It is not uncommon to find questions on this list where somebody asks
                      something like: "I have a customer table with a category field that
                      stores a list of categories like '3,5,13'. How do I join this to my
                      category table to get the category names?
                      • 8. Re: 2 dimensional table
                        Dan Bracuk Level 5
                        Ian interpreted my previous answer correctly. Storing lists is a horrible idea.

                        It's hard to give advice without knowing your entire situation, but, the thought process I recommend is:
                        1. Identify your business requirements.
                        2. Design your database to satisfy those requirements
                        3. Design your user interface.

                        Just to be sure there is no doubt, your user interface is not part of your business requirements.

                        And don't store lists.
                        • 9. 2 dimensional table
                          Lumpia Level 1
                          quote:

                          Originally posted by: Ian Skinner
                          <cfoutput>
                          <cfloop list="#form.checkBoxName#" index="boxValue" delimiter=",">
                          #boxValue#<br>
                          <cfloop list="#boxValue# index="data" delimiter="_">
                          #data# -
                          </cfloop>
                          <br>
                          </cfloop>
                          </cfoutput>



                          Thanks for this bit of code Ian. This did the trick, although I altered it slightly like so:
                          <cfoutput>
                          <cfloop list="#form.checkBoxName#" index="boxValue" delimiter=",">
                          <cfset fruit = ListGetAt(boxValue, 1, "_") />
                          <cfset color = ListGetAt(boxValue, 2, "_") />
                          <cfset dow = ListGetAt(boxValue, 3, "_") />

                          <cfquery name="DBInsert" datasource="#datasource#">
                          Insert into someTable (fruit, color, dow)
                          Values (#fruit#, #color#, #dow#)
                          </cfquery>
                          </cfloop>
                          </cfoutput>

                          This works like a charm - inserts each value into the appropriate category, loops through, etc. I've also been able to display all checked values back to the user using the same code. The problem I'm having now is that I'm not sure how to "update" values already entered in the database. SQL Server is set to auto-increment the UID primary key, and I'd typically update via the Where clause (ie: Where UID = #url.uid#) but considering there are not links, but MANY checkboxes, this method escapes me.

                          Thanks again for your help.
                          • 10. Re: 2 dimensional table
                            Level 7
                            Lumpia wrote:
                            >
                            > This works like a charm - inserts each value into the appropriate category,
                            > loops through, etc. I've also been able to display all checked values back to
                            > the user using the same code. The problem I'm having now is that I'm not sure
                            > how to "update" values already entered in the database. SQL Server is set to
                            > auto-increment the UID primary key, and I'd typically update via the Where
                            > clause (ie: Where UID = #url.uid#) but considering there are not links, but
                            > MANY checkboxes, this method escapes me.
                            >

                            Well you don't give much of an idea on what you are having difficulty
                            with, but with this kind of interface there are two common methods. The
                            simplest, but heavy handed, approach is to just delete all existing
                            values from the database and then insert all the new values from the form.

                            The more subtle way, but it takes work, is to compare the state of the
                            data from the form with the current state of the data in the database
                            and make decisions on what needs to be added, updated and deleted. This
                            takes more work as you either need to re-query the current state or
                            store it when you displayed it for the form. Then loop over all the
                            data and compare it with the form data and see what is new, what is
                            different and what is removed.
                            • 11. Re: 2 dimensional table
                              Lumpia Level 1
                              Thanks for all your help Ian. Much appreciated.