2 Replies Latest reply on Nov 15, 2011 8:40 AM by insuractive

    Insert multiple checkboxes with textbox

    jfvaught

      All,

       

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

       

      gc-screenshot.gif

       

      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:

       

      gc-database.gif

       

      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.

        • 1. Re: Insert multiple checkboxes with textbox
          -==cfSearching==- Level 4

          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?

          • 2. Re: Insert multiple checkboxes with textbox
            insuractive Level 3

            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