[cfaussie] Re: Logic help

[Ron Ziemiecki]

Thnx Phil, I'll give that whirl right now and see how it goes and yes I agree that the select* is not efficient, you are correct!   Ron...   -------Original Message-------   From: CFAussie Mailing List Date: Saturday, February 22, 2003 11:42:17 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Logic help   Hi Ron,   Isn't it great to be able to work on the weekend (not).   There are a number of options, with various trade-offs for complexity and efficiency.   An easy, less efficient option is to use the startrow and maxrows options of the cfoutput.   e.g. <cfquery name="BODYBUILDING" datasource="#db_datasource#" username="#db_username#">        select *        FROM athlete        WHERE  athlete_sport = 'BODY BUILDING'        order by athlete_surname ASC  </cfquery>   <cfoutput query="BODYBUILDING" startrow=#startat# maxrows=20> ... </cfoutput>   initialise startat to 1, if they click on next, add 20, if they click on previous, subtract 20. Don't let it go negative. You can use the bodybuilding.recordcount field to ensure they don't go past the end.   This technique means that every row is retrieved every time, but only the 20 you want are displayed. Not very efficient, but OK for retrievals of low hundreds. If your talking in the thousands, you probably want to try and keep track of a key value that you can use in the where statement.   Speaking of efficiency, it is more efficient to list the field names that you want, rather than the Select *.   Cheers, Phil.   ----- Original Message ----- From: Ron Ziemiecki To: CFAussie Mailing List Sent: Saturday, February 22, 2003 11:07 AM Subject: [cfaussie] Logic help Hi again.   I have a database with more than 20 results and am limiting the output to a page to 20. I can't get the logic right to make the second display page show the next 20 items because I can't use the _ID because the entries go into the database in a random number fashion. Here is the current code to display 20 items.   <cfquery name="BODYBUILDING" datasource="#db_datasource#" username="#db_username#" maxrows="20">        select *        FROM athlete        WHERE  athlete_sport = 'BODY BUILDING'        order by athlete_surname ASC  </cfquery>   It seems to me that I might have to make another query that gathers all the _ID's from the database, and assigns them numbers and then get them to display in numerical order 20 per page.   Trouble with that is, how do I get the numbers in the first place, bearing in mind entries in Body Building may have _ID's like: 23, 47, 125, 228, 229, 314 and so on. The _ID's are given as people register which is totally at random. There are other sports also which have _ID's given to them, so my initial query must contain the name of the sport, in this case Body Building.   Any ideas how to get the next twenty ( a different 20) athletes, who are body builders to display?   Thnx, send any suggestion, I have the whole weekend to try and figure this out.   Ron... ____________________________________________________   IncrediMail - Email has finally evolved - Click Here --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/