asked on

Use cfloop in a query that INSERTS data into one row?

Hi,

First a quick thanks to everyone on EE for all their help

I need some help, this is a registration page for a bball league, the INSERT statement I'm using does it's job but inserts data into different rows, I need it all to go to the same row.

User chooses which division(s) they want to register for here

<cfquery name="getTourneys" datasource="hoops">
SELECT *
FROM tourneyInfo
WHERE num = #URL.num#
</cfquery>

THE FORM

<cfform action="register2.cfm" name="myForm">
<cfoutput>
<cfloop index="i" from="1" to="9">
<cfif Evaluate("getTourneys.div" & i) NEQ "">
<cfinput name="chkdiv" type="checkbox" value="#Evaluate("getTourneys.div" & i)#">
<font size="4">#Evaluate("getTourneys.div" & i)#</font><br><br>
<input name="#num#" type="hidden" value="#num#">
</cfif>
</cfloop>
</cfoutput>

<input type="submit" name="Submit" value="Submit">
</cfform>

ACTION PAGE-----------------------------------------------------------------------------------------

DATA FROM FORM INSERTED INTO ACCESS, IT INSERTS FINE BUT INTO DIFFERENT ROWS, I NEED IT TO GO TO THE SAME ROW.

<cfif IsDefined('form.chkdiv')>
<cfloop index="i" from="1" to="#ListLen(form.chkdiv)#">
<cfquery datasource="xxx">
INSERT INTO infoTourneyTeams(div#i#)
VALUES(<cfqueryparam cfsqltype="cf_sql_varchar" value="#ListGetAt(form.chkdiv,i)#">)
</cfquery>
</cfloop>
</cfif>

Any help appreciated.
Thanks,
Greg Smith

mmc98dl1

First, I think your database structure needs some work - heres the reason.

If you had 50 divisions, then in your infoTourneyTeams table you would need 50 rows, and every time a division is created you would need to create another row to cater for it.

Realistically you should have your division table, a joining table with the divisionid and teamid in, then your infotourneyteam table, this would then mean your database can scale very nicely.

Second - to make it work the way you have, the most effective way i think, you need to put in comma delimited list in one cell, so use

<cfquery datasource="xxx">
INSERT INTO infoTourneyTeams (div)
VALUES(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.chkdiv#">)
</cfquery>

ASKER CERTIFIED SOLUTION

Renante Entera

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregoryrsmith

ASKER

That's it exactly. You are a lifesaver!! Add that to your already extensive resume.

Thanks again,
G

Renante Entera

Oh! That's great.

I'm glad that I have helped you. Hope to help you again.

Regards!
eNTRANCE2002 :-)