Panos
asked on
Best way to store values from a checkboxgroup
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,P RICE,KM)
VALUES
('#art_uuid#','#FORM.EXTRA S#',#Form. TYP_ID#,#S ESSION.USE R_ID#,#For m.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.
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
VALUES
('#art_uuid#','#FORM.EXTRA
</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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it:
<cfset art_uuid = createuuid()>
<cfquery name="InsertArtikel" datasource="#dsn#">
INSERT INTO ARTIKEL
(uuid,ART_ID,TYP_ID,USER_I D,PRICE,KM )
VALUES
('#art_uuid#',#Form.TYP_ID #,#SESSION .USER_ID#, #Form.Pric e#,#Form.K M)
</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>
<cfset art_uuid = createuuid()>
<cfquery name="InsertArtikel" datasource="#dsn#">
INSERT INTO ARTIKEL
(uuid,ART_ID,TYP_ID,USER_I
VALUES
('#art_uuid#',#Form.TYP_ID
</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>
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
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>
ASKER
thank you very much for your help.
regards
Panos
regards
Panos
ASKER
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_I
VALUES
('#art_uuid#',#Form.TYP_ID
</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>