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.

[Newsgroup_User]

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.

[Lumpia]

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.

[Newsgroup_User · Accepted solution]

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.

[Lumpia]

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.

[Newsgroup_User]

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.

[Lumpia]

Thanks for all your help Ian. Much appreciated.

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

[Lumpia]

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.

[Newsgroup_User]

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?

[Newsgroup_User]

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?

[Dan_Bracuk]

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.