3 Replies Latest reply on Apr 16, 2015 2:14 PM by EddieLotter

    cfloop not Working

    ammarq48536816

      ggood morning,

       

      i am new to This forum. I am working on a PC, using MySQL and Coldfusion 9.

      The query below does a multiple row insert just fine with one problem. The query is inserting the same ID of one of my students the number of times that I specify. Is there a way to loop through the insert so that all of the different ID's of the inactive student are inserted for each new row? Am I missing something obvious?

       

      <cfquery name="maxID" datasource="master">

      select max(stuid) as maxid from student

      </cfquery>

       

      <cfquery name="student" datasource="master">

      select count(stuid) as inactive_number

      from name

      where type = 'senior'

      AND squadlt = '#session.user_id#'

      AND CITY = 'richmond'

      AND STATUS = 'i'

      order by stuid

      </cfquery>

       

       

       

       

      <cfquery name="student2" datasource="master">

      select stuid, fname as name

      from name

      where type = 'senior'

      AND squadlt = '#session.user_id#'

      AND CITY = 'richmond'

      AND STATUS = 'i'

      order by stuid

      </cfquery>

       

       

       

       

      <cfset initialmaxid1 = (#maxID.maxid# + 1)>

      <cfset initialmaxid2 = (#initialmaxid1# + #student.inactive_number#)>

      <cfset list1 = "#stuid#">

       

       

      <cfloop from="#initialmaxid1#" to="#initialmaxid2#" index="i">

      <cfquery name="insertabsentee" datasource="master">

      INSERT INTO student_attendance (stuid, name)

      VALUES (#i#,'#list1#')

      </cfquery>

      </cfloop>

        • 1. Re: cfloop not Working
          Dave Ferguson Level 3

          I am really trying to understand what you are doing here.  The student2 query doesn't even appear to be used.  You are setting list1 as stuid but it is unscoped so I am not sure where that is coming from.  If you could explain what you are trying to accomplish I can better help you.

           

          Thanks,

           

          --Dave

          • 2. Re: cfloop not Working
            ammarq48536816 Level 1

            Thank you for your reply. I want to do a query that will collect and list the primary id's of all of my students who are currently inactive. The intent of the   is to contain the studentID's for later insertion into another table entitled "student_attendance". The goal is To insert Each ID's consecutively until the #list1# is exhausted. This query will run every 30 days or so. So it may be 10, 15, 20 at a time that is being inserted with just one query.  When the insert is executed the results looks like the following:

             

            Stuid Name

            23      Jonathan

            23      Jonathan

            23      Jonathan

             

             

            I want it to execute like this:

             

             

            Stuid Name

            23      Jonathan

            34      Alfred

            5        Harry

             

             

            Am I doing something wrong?

            • 3. Re: cfloop not Working
              EddieLotter Level 3

              Why are you trying to read the data into ColdFusion and then send it back again? You can do it in one SQL statement:

              <cfquery name="insertabsentee" datasource="master">

              INSERT INTO student_attendance (stuid, name)

              select stuid, fname

              from name

              where type = 'senior'

              AND squadlt = '#session.user_id#'

              AND CITY = 'richmond'

              AND STATUS = 'i'

              </cfquery>