Insert multiple checkboxes with textbox

[Guest]

All,

I have an interesting/challenging question in which I would really appreciate some guidance.  I recently created a form like so:

Essentially, the user selects what type of fruit they have per state.  If they are currently out of stock of a particular fruit, but it is a fruit they carry, then they enter in the "Dates Unavail" column the days that the fruit is not available at that location.  This list shown is for the weekend, and broken down by district.

My database looks like so:

Definitions:

district_uid = The district

dow_uid = 1 for Weekend, 2 for Weekday

state_uid = The states

fruid_uid = The fruits

CA_unavail = Represents the text boxes for the dates_unavail (same for FL_unavail, etc)

There are also separate tables that define the particular states, fruits, districts, and dow.

Example: 

fruit_uid | fruit_description

1  |  apple

2  |  orange

3  |  banana

Ok, so part of this is actually a project I've completed in the past.  I was able to get all the checkboxes to insert into the database; however, was recently tasked add the dates_unavail textboxes to each section.  This is the part I am having troubles with. 

Here is how I got the checkboxes to work:

<cfset qAppleWknd = QueryNew("fruit, UID")>

<cfset QueryAddRow(qAppleWknd, 3)>

<cfset QuerySetCell(qAppleWknd, "fruit", "1_1_1", 1)>

<cfset QuerySetCell(qAppleWknd, "UID", "1", 1)>

<cfset QuerySetCell(qAppleWknd, "fruit", "1_2_1", 2)>

<cfset QuerySetCell(qAppleWknd, "UID", "2", 2)>

<cfset QuerySetCell(qAppleWknd, "fruit", "1_3_1", 3)>

<cfset QuerySetCell(qAppleWknd, "UID", "3", 3)>

This essentially creates a query to define each checkbox.  So, 1_1_1 represents dow_uid, state_uid, fruit_uid.  Therefore, this would be 1 = weekend, 1 = florida, 1 = apple. 

Here's the ColdFusion code for the table:

<tr>

   <cfoutput query="qCapCatWknd">     

      <td><input type="checkbox" name="fruit" value="#qAppleWknd.fruit#" /></td>

   </cfoutput>

</tr>

Again, if the user chose the first checkbox, the value outputted to the action page will be:  1_1_1.

Here is how the code gets inserted into the database:

<cfoutput>

  <cfloop list="#form.fruit#" index="box" delimiters=",">

    <cfset dow = listGetAt(box, 1, "_")>

    <cfset state = listGetAt(box, 2, "_")>

    <cfset fruit = listGetAt(box, 3, "_")>

      <cfquery name="qInsertFruit" datasource="#database#" username="#dbid#" password="#dbpass#">

      INSERT INTO fruit (

        district_uid,

        dow_uid,

        state_uid,

        fruit_uid,

        )

      VALUES (

        #cookie.districtCookie#,

        #dow#,

        #state#,

        #fruit#

        )

      </cfquery>

  </cfloop>

</cfoutput>

So, I know this may not be the ideal way to code this (this project was initially done years ago), but now I'm trying to get the textboxes (dates_unavil) to work.  I hope I don't have to recode a whole lot of stuff, but please give any advice you can. 

Thanks in advance.  Sorry for this being a long one, just trying to make sure you guys understand it.

[-__cfSearching__-]

CA_unavail = Represents the text boxes for the dates_unavail (same for FL_unavail, etc)

FL_unavail = Represents the text boxes for the dates_unavail (same for FL_unavail, etc)

I do not think it makes sense to add a bunch of columns for all states when each record represents data for a single state. Why not just add two date/time columns to store the date range for the current state: UnavailStartDate, UnavailEndDate?

[insuractive]

cfsearching's suggestion above solves 2 of the 3 problems you are facing:

1) How do you store the data in a scalable manner (hint: creating a new column for each state is not very scalable if your organization decides to move into US territories or Canada)

2) How do you store your date data in a format that allows you to retrieve the data - by adding the start and end dates you are essentially recording in your database record the times when that fruit* (defined by fruit_uid) is unavailable in that state (defined by state_uid).  While it is true that your queries will require some reworking to get the same format that you are using to load into your interface, it should be relatively easy to do using a query of a query statement

3) The only piece that you are missing is how to pass that information via form field in a way that lets you extract that date range and insert into your database

Currently, you are using a method by which you use a delimeted string to store your data (1st position: dow, etc).  You can use this same format as a prefix to your date fields and dynamically retireve your data from the action page at runtime:

e.g. checkbox field: 1_1_1

start field: 1_1_1_UnavailStartDate

end field: 1_1_1_UnavailEndDate

Then inside of your loop you can use an isDefined/neq test to see if your user has entered dates:

<cfset startDate = "">

<cfif isDefined("FORM.#box#_UnavailStartDate") and FORM["#box#_UnavailStartDate"] neq "">

     <cfset startDate = FORM["#box#_UnavailStartDate"]>

</cfif>

In the example interface you listed above, you only had one field for the user to enter a range.  To get that working you would just have to add one step to the above code where once you retrieve the data from the form scope, you parse the start/end dates for use in your INSERT statement.

Hope that helps,

- Michael

*In my original post, I accidentally typo'd this word as "fruid", which now that I think about it is the perfect variable name for a unique ID of a fruit. :) Message was edited by: insuractive