We help IT Professionals succeed at work.

looping and updating

nmarano asked

I'm trying to make an update to a table setting the pageNum in the table, but am having some trouble doing so.  what seems to be happening is I am the pageNum is being set to totalPageCount rather than incrementing from 1 to totalpagecount.  Any help would be appreciated.  I basically trying to loop from 1 to totalpagecount and update pageNum in my db to reflect whichever number is current in my loop.  Hopefully this makes sense.

<cfset addPageNum = valueList(getPageNum.tExitSubSectionItemID)><!--- looks like 'surveyAT198,surveyAT199,surveyAT200' --->
<cfset totalPageCount = listLen(addPageNum)><cfdump var="#totalPageCount#">
<cfloop index="i" from="1" to="#totalPageCount#">
<cfloop list="#addPageNum#" index="j">
       <cfoutput query="getPageNum">
         <cfquery name="addnum" datasource="#request.datasource#">
               UPDATE tSubsectionStatus
               SET pageNum = <cfoutput>#i#</cfoutput>
                WHERE tTeacherID = #session.teacher.id#
                AND tExitSubSectionItemID = <cfoutput>'#j#'</cfoutput>
Watch Question


The loops don't seem to make sense to me.  You are updating the ERexitSubSectionItemID  with every page number, writing over the previous update with a new page num i.

So if totalPageCount is 10, then you are updating SET PAGENUMB = 1,  then SET PAGENUMB = 2, then 3, then 4 until you get to totalPageCount which is 10.   Then you are doing it again for the next value of J

That does't mention this loop...

  <cfoutput query="getPageNum">

It doesn't seem you use any getPageNum values in the cfquery update statement?  You are only using i and j ?   So not sure what this one is for at all..

Why don't we start from the top and describe what you're trying to do... I think we have to change how you're doing it.

Also, you need to remove the <cfoutput> gets from inside your cfquery.  They are not needed at all and have some chance of messing things up..



So I have this table tSubSectionStatus which have the following columns populated(tOrderID, tTeacherID, tExitSubSectionItemID)  What I want to do is based on the total number of tExitSubSectionItemID in this table, I want to update the pageNum field with a numeric value based.  

So if my data looks like this...

tOrderID              tTeacherID              tExitSubSectionItemID              pageNum
1                           271                          surveyAT198
2                           271                          survey AT199
3                           271                          survetAT224
4                           271                          surveyAT 226
5                           302                          surveyAT198
6                           302                          surveyAT199
7                           302                          surveyAT270

So I was thinking I could pull a count on the query and this one would be 4(for teacherID 271), then loop from 1 to my count and update the pageNum.  I want to be able to do it for every teacher So my updated result would be....

tOrderID              tTeacherID              tExitSubSectionItemID              pageNum
1                           271                          surveyAT198                            1
2                           271                          survey AT199                           2
3                           271                          survetAT224                              3
4                           271                          surveyAT 226                            4
5                           302                          surveyAT198                              1
6                           302                          surveyAT199                              2
7                           302                          surveyAT270                                3


So i had set these two...
<cfset addPageNum = valueList(getPageNum.tExitSubSectionItemID)>Which gives me a list of tExitSubSectionItemID that this teacher was assigned

And then set this so I knew what to count up to
<cfset totalPageCount = listLen(addPageNum)><cfdump var="#totalPageCount#">

what database are you using?

If this is sql server, I believe you can do this using the RANK() function..


Not sure if MySQL can fake the RANK function, I did some googling on it and there were some results.  Feel free to look up that approach or ask in the mySQL area.

But there the CF way...

I don't think you need to know how many pages per teacher in advance.   It seems like you are just ranking each record 1, 2, 3, 4... until the next teacher ID and then starting over.  

If I got the idea right, here's some code that will loop through and rank the records...
<!--- order by tTeacher and then whatever field you want the 1,2,3.. ranking by (a date or whatever) ---->
<cfquery name="getData" datasource="#request.datasource#">
 select tTeacherID, tOrderID
  from tExitSubSectionItemID 
 order by tTeacherID, tExitSubSectionItemID
<cfset prevTeacher = 0> <!---- keep track of when you change teachers ----->
<cfset counter = 0> <!---- the counter ----->
<cfloop query="getData">
  <cfif prevTeacher neq getData.tTeacherID>
     <cfset counter = 0>
  <cfset counter = counter + 1>
  <cfquery name="updateData" datasource="#request.datasource#">
   update tExitSubSectionItemID
     set pageNum = #counter#
    where tOrderID = #getData.tOrderID#

Open in new window


Modified it a bit...Thanks for the logic
Most Valuable Expert 2015

@nmarano - I was just going to post how you could do it with a mySQL query but I see you've solved it already :)

> SET pageNum = <cfoutput>#i#</cfoutput>

Just fyi, you don't ever need to use <cfoutput> tags within a cfquery.  Also, it's good to use cfqueryparam to help avoid sql injection nasties.


Thanks agx

Explore More ContentExplore courses, solutions, and other research materials related to this topic.