Hi Ron / Vik, I think you'll find the 'previous' logic won't work - I suspect it will always give the first 20 records, regardless of how many times they have pressed 'Next'. It would work if you changed the order by to DESC, but then the records would be displayed in reverse alphabetical order.
Phil. ----- Original Message ----- From: "Viktor Radnai" <[EMAIL PROTECTED]> Newsgroups: cfaussie To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, February 22, 2003 11:00 PM Subject: [cfaussie] Re: Logic help > Hi Ron, > > I've seen you have solved the problem, but I've figured a way to > optimise your query (ie not having to retreive everything every time): > > Set two variables, form.first_athlete_name and form.last_athlete_name. > They should be in the format "smith john" > > Then you'd use this sql for pulling the next 20: > > <cfquery name="BODYBUILDING" datasource="#db_datasource#" > username="#db_username#" maxrows="20"> > select * > FROM athlete > WHERE athlete_sport = 'BODY BUILDING' > AND athlete_surname + ' ' + athlete_firstname > <cfqueryparam > name="#form.last_athlete_name# cfsqltype="cf_sql_varchar"> > order by athlete_surname ASC > </cfquery> > > And this one for the previous 20: > > <cfquery name="BODYBUILDING" datasource="#db_datasource#" > username="#db_username#" maxrows="20"> > select * > FROM athlete > WHERE athlete_sport = 'BODY BUILDING' > AND athlete_surname + ' ' + athlete_firstname < <cfqueryparam > name="#form.first_athlete_name# cfsqltype="cf_sql_varchar"> > order by athlete_surname ASC > </cfquery> > > I haven't tested this one but once you fix up the syntax it should > work (unless I've overlooked something)... well, it's worth a try > anyway. The only bug in it is if you have two athletes with identical > first *and* last name, and they happen to be the n*20th and n*20 + 1th > record then one of them will be skipped (I think). > > Hope you still find this code useful... > > Cheers, > Vik > > Ron Ziemiecki wrote: > > You are a legend Phil, thanks, its working perfectly now, go make > > yourself a coffee and stretch your legs :-) > > > > Ron... > > > > startat will either need to be a session variable, or passed as a URL > > parameter. > > e.g. > > <cflock...> > > <cfset session.startat = 1> > > </cflock> > > ... > > > > > > > > > > ____________________________________________________ > > <http://www.incredimail.com/redir.asp?ad_id=309&lang=9> /IncrediMail/ - > > *Email has finally evolved* - *_Click Here_* > > <http://www.incredimail.com/redir.asp?ad_id=309&lang=9> > > > --- > 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/
