cfloop not Working

New Here ,
Apr 12, 2015 Apr 12, 2015

Copy link to clipboard

Copied

‌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>

Views

249

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Apr 12, 2015 Apr 12, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 13, 2015 Apr 13, 2015

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 16, 2015 Apr 16, 2015

Copy link to clipboard

Copied

LATEST

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines