?
Solved

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

Posted on 2005-03-20
4
Medium Priority
?
389 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:gregoryrsmith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:mmc98dl1
ID: 13587525
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>
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 2000 total points
ID: 13588108
Hi gregoryrsmith!

I just need some clarification about the flow.

:: FORM PAGE ::
checkbox 1 - div1 - value1
checkbox 2 - div2 - value2
checkbox 3 - div3 - value3
... until checkbox 9 ...

If checkbox 3 is chosen, it should be save into "div3" of table "infoTourneyTeams" considering that checkboxes 1 and 2 are not chosen.  I suspect this is what you want to overcome but the action page doesn't do the requirements.  Am i right ???

So, your code needs to be revised again.  #-o

:: REVISED FORM PAGE ::
<cfform action="register2.cfm" name="myForm">
  <cfoutput>      
    <cfloop index="i" from="1" to="9">
      <cfif Evaluate("getTourneys.div" & i) NEQ "">
        <cfinput name="chkdiv#i#" 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>

* Note : In this case, you don't need to have the same name for your checkboxes.

:: REVISED ACTION PAGE ::
<cfloop index="i" from="1" to="9">
  <cfif IsDefined('form.chkdiv#i#')>
    <cfquery datasource="xxx">
    INSERT INTO infoTourneyTeams(div#i#)
    VALUES(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form['chkdiv#i#']#">)
    </cfquery>
  </cfif>
</cfloop>

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 

Author Comment

by:gregoryrsmith
ID: 13590757
That's it exactly. You are a lifesaver!! Add that to your already extensive resume.

Thanks again,
G
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13597702
Oh!  That's great.

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


Regards!
eNTRANCE2002 :-)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

800 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