Link to home
Create AccountLog in
Avatar of nmarano
nmarano

asked on

looping and updating

Experts-

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>
            </cfquery>  
            </cfoutput>
</cfloop>
</cfloop>
Avatar of gdemaria
gdemaria
Flag of United States of America image

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

Avatar of nmarano
nmarano

ASKER

Maria-

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


Avatar of nmarano

ASKER

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..
Avatar of nmarano

ASKER

MYSQL
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of nmarano

ASKER

Modified it a bit...Thanks for the logic
@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.
Avatar of nmarano

ASKER

Thanks agx