Link to home
Start Free TrialLog in
Avatar of bcrosby007
bcrosby007Flag for United States of America

asked on

CF Select Multiple Select - Pass to Insert Query

I have a checklist database that is used through coldfusion. The form I have created has a cfselect field that allows the user to select multiple options. I need the multiple options to Insert into my database. Currently I can do one field at a time. If i select multiple entries, it only grabs fields that have the values beside each other.
Make sense?

I need the code to add checklist items where the projectSubType contains any of the cfselect options.

<cfset todaydate=now()>

<cfif form.id is"111111">

<cfelse>
<cfquery datasource="checklist" name="addchecklistitems">
INSERT INTO tbl_project_items (ProjectID, Checklist, ChecklistItemID)
SELECT "#form.id#", tbl_checklist_items.Checklist, tbl_checklist_items.ChecklistItemID
FROM tbl_checklist_items
WHERE tbl_checklist_items.ProjType LIKE "%#form.proj_type#%" AND tbl_checklist_items.ProjSUBType LIKE "%,#form.proj_type_sub#,%" OR tbl_checklist_items.ProjSUBType LIKE '%27%'
</cfquery>
</cfif>

<cfif form.sd>
<cfquery datasource="checklist" name="addchecklistproject">
	Insert Into tbl_project_info
	  (id, proj_type, proj_type_sub, date_added, phase)
	Values
	  ('#form.id#', '#form.proj_type#', '#form.proj_type_sub#', '#DateFormat(todaydate, "mm/dd/yyyy")#', "sd")
</cfquery>

<cfelse>

<cfquery datasource="checklist" name="addchecklistproject">
	Insert Into tbl_project_info
	  (id, proj_type, proj_type_sub, date_added)
	Values
	  ('#form.id#', '#form.proj_type#', '#form.proj_type_sub#', '#DateFormat(todaydate, "mm/dd/yyyy")#')
</cfquery>
</cfif>


<cflocation url="\Checklist.cfm?projnum=#id#">

Open in new window

Avatar of bcrosby007
bcrosby007
Flag of United States of America image

ASKER

Some checklist items will have multiple SubTypes as well, so i dont want duplicate checklist items being created.
I think i am going to need to use the cfloop to loop through my sub types, then run that loop into my insert query.
Code Help?
Avatar of gdemaria
First thing you need to do is create a child table for your sub types.   It will be an endless nightmare to try and maintain a comma delimited list of values in a field of your main table

Once you have the child table for subtypes, then all you have to do is loop through them to add the records...

<cfloop index="aType" list="#form.subtypes#">
     insert #aType# into child table...
</cfloop>
The way our checklist items are set up use comma delimited to tell what sub types that checklist item applies to.

Is this on the right track?

<cfloop index="subs" list="form.proj_type_sub">

    <cfquery datasource="checklist" name="addchecklistitems">
    INSERT INTO tbl_project_items (ProjectID, Checklist, ChecklistItemID)
    SELECT "#form.id#", tbl_checklist_items.Checklist, tbl_checklist_items.ChecklistItemID
    FROM tbl_checklist_items
    WHERE tbl_checklist_items.ProjType LIKE "%#form.proj_type#%" AND tbl_checklist_items.ProjSUBType LIKE "%,#form.proj_type_sub#,%" OR tbl_checklist_items.ProjSUBType LIKE '%27%'
    </cfquery>
</cfloop>

Open in new window

This is a snapshot of the back end database.
BackEndDB.JPG
Oh boy, if you have the ability to change your database format, you really should do it now before you have a lot of headaches coming.   This design is really going to cause you problems, you will have trouble changing the value, searching for any subtype and particularly a combination of subtypes, etc.   If this is for a company and your boss tell you no, then you have to sit with him for at least one hour trying to change his mind.   If this is for yourself, then you just have to change it.

if after all that, you cannot change the design, then please clarify the question... I believe you are saying that you have an existing list of subtypes and then the user can select additional subtypes and you need to merge these two together so they are no duplicates; this new list will be the new value stored in the table's field.  Is that correct?

When the form opens, doesn't the select-box highlight all the items in the field that are currently in the database?   That is, the user can see the current values, so when he uncheckes boxes, and checks new ones, the select tag will have a list of ALL subtypes selected.   Therefore a direct update of the field should replace all the old values with all the new values?
How shold i change the format to make it easier? Some checklist items can have up to 25 different sub types assigned to it.
The current code and database works fine if i add a new project with a single sub type. It's jsut being able to select multiple sub types when creating a new checklist and having the appropriate checklist items show up without duplicates.
There are many potential problems with this design that will cause you grief as you build up the system.  Here's are a  few quick examples...

Create a report (list) of projects by their subtypes.  Show the subtype name followed by the project items that are associated with that subtype.

Give a report of the most commonly used subtypes, show the subtype name and the number of projects using them.

Subtype 12 is going away and will be replaced by subtype 17, write a SQL statement to make the change.  

The user wanted to be able to search projects by subtype.  He can check off any number of subtypes, find the projects that use any one of them.
> It's jsut being able to select multiple sub types when creating a new checklist and having the appropriate checklist items show up without duplicates.

when creating a new item?   The field is currently empty when updating it for the first time, why do you have duplicates?   Each subtype selected should be in the form field just one time, true?   Can you check your variable to verify that...

The form variable FORM.SUBTYPE (or whatever its called) would look like this..

 12,15,23,34

which each subtype number appearing once on the list.

Then just take that list and update the field, because the list is 100% complete (because you are not adding or removing items, you are taking the list as-is, right?)

 update table
  set subtypes = '12,15,23,34'
 where ID = xxxx

Sorry if I am not understanding better, it seems to me that the selected items is a compelte and accurate list that should just update directly into the table...
How else would you assign multiple subtypes to a checklist item? Assigning numbers seems to make sense to me and it currently works when adding a single sub type checklist. I just need to be able to add multiple sub types without duplication. I assume this can be done via cfloop, but i have never used it.
Here is the gist of the database.
1 table that contains 750+ checklist items. Each checklist items has a project type and subtype assigned to it. It can have multiple subtypes as well.
1 table that contains project information. This contains a project number, project type as well as the subtypes assigned.
1 table that contains project items. This table contains all of the checklist items that correspond to the project type and subtype.

Does the backend make sense now?
ASKER CERTIFIED SOLUTION
Avatar of bcrosby007
bcrosby007
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Figured it out myself.