D I'm not sure if I 100% understand what you need to do but I think that based on an id of a variable you need to: - find the 10 integer range that it is in and calculate the start and end values for that range - based on the range you need to work out a current page number in a set of pages and the next and previous page numbers - find the record in a table that matches your id and find out it's recordnumber in the table - based on the record position, find the 9 other records in the range that the recordnumber falls in as calculated above. based on the above assumptions, you might find the following useful:
<!--- create testing id if not using one from the URL string---> <cfparam name="url.video_id" default="14"> <cfoutput> URL.Video_ID: #url.video_id#<br><br> </cfoutput> <!--- ***** non DB related value calcualations ***** ---> <!--- calculate range start ---> <cfset startRange = (int(url.video_id/10) * 10) + 1> <!--- calculate range end ---> <cfset endRange =(int(url.video_id/10)+1)*10> <!--- calculate the on page for the current range using the end range as a basis for calculation ---> <cfset onPage = mid(endRange, 1, len(trim(endRange)) - 1)> <!--- next page ---> <cfset nextPage = onPage + 1> <!--- previous page ---> <cfset prevPage = onPage - 1> <!--- output variables to check ok ---> <cfoutput> startRange: #startRange#, <br> endRange: #endRange#, <br> onPage: #onpage#, <br> nextPage: #nextpage#, <br> prevPage: #prevpage#<br> </cfoutput> <!--- **** DB related calculations ***** ---> <!--- query the video table to get all records, one of which will match the url.video_id---> <cfquery datasource="#request.dsn#" name="video" cachedwithin="#request.timespan#"> select * from tblbcplayer order by id </cfquery> <!--- loop through recordset until we find the matching record for url.video_id ---> <cfloop from="1" to="#video.recordcount#" index="i"> <!--- when we have found the matching record .... ---> <cfif video.id[i] eq url.video_id> <!--- set a variable equal to the record number of the matching record ---> <cfset video_recnumb = i> <cfset startDBrange = (int(video_recnumb/10) *10) + 1> </cfif> </cfloop> <!--- out put the necessary data from the query data from the query beginning at the appropriate row for 10 iterations ---> <cfoutput query="video" startrow="#startDBrange#" maxrows="10"> <br>#id#, #surname# </cfoutput> You may want to try and cache the initial query to retrieve all records depending how big the table is and how often the data will change in it. You may need to fiddle around with it a little to get it to meet your exact needs but hopefully it will give you a good point to work from. You can have a look at it using some different URL values here if you like: http://www.barchameleon.co.uk/index.cfm?fuseaction=test&site=old&video_id=5 (will only work up to value of 26 as that is how many records I have in that test table) It may also be worth noting that it is probably best if can be avoided not to hardcode value tests as you have. A majority of the time (but not always) you can do these kind of checks dynamically based on the data that you are working with in terms of what is on the database and id type values being used as looks ups such as in this case. Loops, indexes, counters, and min/max values combined with if statements are often good candidates for this kind of conditional logic. Hope that helps Rich On 8/26/06, Denny Valliant <[EMAIL PROTECTED]> wrote: > First off, this isn't going to help much :-) > > Second off, I think what you'd want to do is grab a query of all your > videos, and > put it in memory somewhere. Then do a QoQ for the records in the group you > want, and then, instead of using the actual video ID's to do your math, use > the > recordcount and currentrow attributes, so your always working with 1-10 > instead > of a random amount of incrementation per record, so to speak. > > See, that probably doesn't help much. :-/ Real general. But the key point > is that > you don't have to use the videoID to keep track of where you are, > numerically > speaking, in the set. Sorta. Startrow/maxrows,currentRow, etc. are all > good for > this stuff. > > There may be some good resources out there under "coldfusion pagination", > via > google, to help illustrate what I'm talking about. > > May the Force be with you! > :Denny > > On 8/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED] > > wrote: > > > > I'm passing an id, a numeric value in a URL.value. That id value > > indentifies the > > primary key id of a record in my db. Based on that value, I want to > > get the group of records in the db that surround that value in groups of > > 10. > > This is used to display other hyperlinked items in the same group. I'm > > basically > > creating page views showing links of these items in these groups. I want > > to display > > the groups of the first 1-10, 11-20, 21-30 etc entries in the db. However > > some > > records have been deleted, so some of the existing primary key ids are not > > sequntial, > > the second group of 10 id values might be 11,13,14,16,17,19,22,23,24,25. > > I've > > got the code that will idenifiy what group level I am in when the URL.IDis > > passed. > > > > QUESTION: Now I need to figure out how to extract the group of > > non-sequential ids > > to extract from the db. Hope this makes sense. > > > > Here's what I have, > > > > <cfset start = (int(url.video_id/10) * 10) + 1> > > <CFSET end=(int(url.video_id/10)+1)*10> > > > > <CFIF start EQ 1 AND end EQ 10> > > <CFSET on_page = 1> > > <CFSET next_page = 2> > > <CFELSEIF start EQ 11 AND end EQ 20> > > <CFSET on_page = 2> > > <CFSET next_page = 3> > > <CFSET previous_page = 1> > > <CFELSEIF start EQ 21 AND end EQ 30> > > <CFSET on_page = 3> > > <CFSET next_page = 4> > > <CFSET previous_page = 2> > > and so on ... > > > > TO extract the set of rows, I need to take the id and indentify where I am > > in these > > groups of 10. So id 43 would be 2 away from 41 and 7 away from 50. How do > > I implement that into a query? Or should I be using cf logic first? > > > > <!--- TELLS ME HOW MANY ROWS I AM AWAY FROM MY STARTINIG ROW---> > > <CFSET from_1 = URL.VIDEO_ID - start> > > <!--- TELLS ME HOW MANY ROWS I AM AWAY FROM MY STARTINIG ROW ---> > > <CFSET from_2 = end - URL.VIDEO_ID> > > > > How do I write this query? > > > > D- > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251358 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
