Solved

Best way to store values from a checkboxgroup

Posted on 2008-10-21
5
176 Views
Last Modified: 2013-12-24
Hello Experts.
I have one Insertpage for cars.I'm using checkboxes for the extras and i want to know how i can store the values in the DB.
I have tried to use :
<input type="checkbox" name="Extras" value="740" />
<input type="checkbox" name="Extras" value="750" />
<input type="checkbox" name="Extras" value="790"/>
And in the query i have :
<cfset art_uuid = createuuid()>
<cfquery name="InsertArtikel" datasource="#dsn#">
INSERT INTO ARTIKEL
(uuid,ART_ID,EXTRAS,TYP_ID,USER_ID,PRICE,KM)
VALUES
('#art_uuid#','#FORM.EXTRAS#',#Form.TYP_ID#,#SESSION.USER_ID#,#Form.Price#,#Form.KM)
</cfquery>

The result is that i have in the EXTRAS column (artikel table) if all the checkboxes are checked 740,750,790.
The question is that i have been told that this is wrong.The best way is to store the EXTRAS in a second table (extras) like:
Extras_Table_id   Art_ID    Extras
1                             5           740
2                             5           750
3                             5           790

Where the Art_ID is the  autogenerated primary id in the artikel table.(in this case 5)
If that is the right way please help me how the Insert query must be.
 
0
Comment
Question by:Panos
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 22769406
Yes, I agree that the EXTRAS table is the right approach.

To insert into it, just loop through your EXTRAS values ...

To keep from adding duplicates, remove all the extras associated with that Art_ID first
   <cfquery name="clearExtra" ....>
     delete extras where art_id = #val(art_id)#
  </cfquery>

Now add each checked extra...
<cfloop index="kk" list="#form.extras#">
   <cfquery name="addExtra" ....>
     insert into extras (art_id, extras)
     values (#art_id#, #kk#)
  </cfquery>
</cfloop>

0
 
LVL 2

Author Comment

by:Panos
ID: 22769940
Hi gdemaria.
I need the query in the same page .So i need the art_Id.
Is this the right way?
<cfset art_uuid = createuuid()>
<cfquery name="InsertArtikel" datasource="#dsn#">
INSERT INTO ARTIKEL
(uuid,ART_ID,TYP_ID,USER_ID,PRICE,KM)
VALUES
('#art_uuid#',#Form.TYP_ID#,#SESSION.USER_ID#,#Form.Price#,#Form.KM)
</cfquery>
<cfquery name="getNewID" datasource="#dsn#">
SELECT Art_ID FROM artikel WHERE uuid = '#art_uuid#'
</cfquery>
<cfloop index="kk" list="#form.extras#">
   <cfquery name="addExtra" ....>
     insert into extras (art_id, extras)
     values (#art_id#, #kk#)
  </cfquery>
</cfloop>
0
 
LVL 2

Author Comment

by:Panos
ID: 22770179
That's it:
<cfset art_uuid = createuuid()>
<cfquery name="InsertArtikel" datasource="#dsn#">
INSERT INTO ARTIKEL
(uuid,ART_ID,TYP_ID,USER_ID,PRICE,KM)
VALUES
('#art_uuid#',#Form.TYP_ID#,#SESSION.USER_ID#,#Form.Price#,#Form.KM)
</cfquery>
<cfquery name="getNewID" datasource="#dsn#">
SELECT Art_ID FROM artikel WHERE uuid = '#art_uuid#'
</cfquery>
<cfloop index="kk" list="#form.extras#">
   <cfquery name="addExtra" ....>
     insert into extras (art_id, extras)
     values (#getNewID.art_id#, #kk#)
  </cfquery>
</cfloop>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22770181
Right Idea,  just some small tweaks.

Looks like you're using MS SQL Server, right?   Try  SCOPE_IDENTITY() to get the ID
And you have to assign it to the variable used in the last Insert



<cfquery name="InsertArtikel" datasource="#dsn#">

  INSERT INTO ARTIKEL (uuid,ART_ID,TYP_ID,USER_ID,PRICE,KM) VALUES

  ('#art_uuid#',#Form.TYP_ID#,#SESSION.USER_ID#,#Form.Price#,#Form.KM)

</cfquery>
 

-- this fetches the last ID created in the session (more reliable!)

<cfquery name="getNewID" datasource="#dsn#">

  select SCOPE_IDENTITY( ) as ID

</cfquery>

<cfset art_id = getNewID.ID>  --- put the ID into the variable used below
 

<cfloop index="kk" list="#form.extras#">

   <cfquery name="addExtra" ....>

     insert into extras (art_id, extras)

     values (#art_id#, #kk#)

  </cfquery>

</cfloop>

Open in new window

0
 
LVL 2

Author Closing Comment

by:Panos
ID: 31508399
thank you very much for your help.
regards
Panos
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Configure a Local PHP Interpreter for Phpstorm 2 186
ip / url redirect 13 68
Handling onsession end function in Application.cfc 5 43
Domino Website - Redirection 12 50
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now