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

gdemaria

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

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

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#">

gdemaria

what database are you using?

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

nmarano

ASKER

MYSQL

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmarano

ASKER

Modified it a bit...Thanks for the logic

_agx_

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

nmarano

ASKER

Thanks agx