Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-20
4
Medium Priority
?
398 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
  • 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 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