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>

[Dave Ferguson]

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

[ammarq48536816]

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?

[EddieLotter]

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>