We just finished a long thread on this exact topic. I'll summarize for you:
You can show a page at a time by using the STARTROW and NUMROWS options of CFOUTPUT
like so:
--searchpage.cfm---
<CFIF NOT IsDefined("firstrow")>
<CFSET firstrow=1>
</CFIF>
<!--- do search --->
<CFQUERY NAME="searchquery"> ... SQL... </CFQUERY>
<!--- display one page --->
<CFOUTPUT STARTROW=#firstrow# NUMROWS=10>
...display a row...
</CFOUTPUT>
<!--- calculate previous page --->
<CFSET prevstart=firstrow - 10>
<CFIF prevstart LT 1>
<CFSET prevstart =1>
</CFIF>
<!--- calculate next page --->
<CFSET nextstart=firstrow + 10>
<CFIF nextstart GT searchquery.recordcount >
<CFSET nextstart = searchquery.recordcount - 10>
</CFIF>
<a href="searchpage.cfm?firstrow=1">FIRST</a>
<CFOUTPUT>
<a href="searchpage.cfm?firstrow="#prevstart#">PREV</a>
<a href="searchpage.cfm?firstrow="#nextstart#">NEXT</a>
</CFOUTPUT>
=========================
I didn't get very fancy with the calculation of the previous and next pages. You could
do a better job on the next page. And I hard coded a page size of 10, which you can
make more dynamic.
The next topic is how to make this more efficient. Each time the user click 'NEXT' the
query will reexecute.
One solution is to cache the query (in the CFQUERY tag) so that the query will NOT be
reexecuted.
The two reasons I did not choose this method are
1) The query must have the same name and the exact same SQL. My SQL is extremely long
with 20-30 items in the where clause. I didn't want to pass all that information
around.
2) There is a limit to the number of queries CF will cache. That limit is set in the
CF Administrator and is said to be 100 although someone here tested it and got over
200 before he got bored.
The other solution (which I am using) is to keep the query in a more permanent
variable like a session variable.
I am doing this:
<!--- execute query --->
cflock session
cfquery name=session.searchquery
...sql...
/cfquery
/cflock
cflock readonly session
cfoutput query=session.searchquery startrow=#firstrow# numrows=10
display rows
/cfoutput
/cflock
Hope this helps.
And I especially hope I got it all right! :-)
At 06:02 AM 8/5/99 +1000, AustralianAccommodation.com Pty. Ltd. wrote:
>can I get some definitive suggestions please on how to create working script
>to achieve the paging query results to simulate similar to what you see on
>search engines
>
>
>----- Original Message -----
>From: Peter Theobald <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>Sent: Saturday, August 05, 2000 5:45 AM
>Subject: RE: Paging query results question
>
>
>| --=====================_20677026==_.ALT
>| Content-Type: text/plain; charset="us-ascii"
>|
>| On the mailing list I *CONSTANTLY* hear people mention facts that are not
>in the Cold Fusion documentation.
>| In this example, how did you know there is a limit of 100 cached queries?
>The Cold Fusion Language Reference doesn't say that in the description of
>CFQUERY.
>|
>| Where is everybody getting this information.
>| I must say that, although I love working in Cold Fusion, the Cold Fusion
>documentation is among the WORST language documentation I have ever had the
>misfortune of struggling with.
>|
>| Even the so-called Reference manual is little more than a brief
>description of each TAG.
>| Where is the definitive definition of exactly how each element behaves?
>|
>| How can they say <CFSCRIPT> is "like" Javascript, but not exactly?! Where
>is the rigorous description of every element?
>| When I write CFSCRIPT programs that are "like" Javascript, I find out by
>trial and error just how "like" it is and isn't.
>|
>| At 07:17 AM 8/4/00 -0700, paul smith wrote:
>|
>| >I've done something similar, except I cache the big query for 5 minutes.
>| >
>| >In addition, I store the long list of IDs as a client variable (in a
>| >database). The cache thus trades the need to create sublists for a
>| >database read AND speeds up the paging queries (since the cache is in
>| >memory). Works well, but on a busy site I'm concerned I'll run up
>against
>| >the limit of 100 cached queries.
>| >
>| >I also wonder about the speed of lists, both the time it takes to create
>| >them, and the resultant use of WHERE ID IN (#ID_List#), compared to using
>a
>| >JOIN on a table that contains the IDs as a column.
>| >
>| >Anyone?
>| >
>| >best, paul
>| >
>| >At 10:14 AM 8/4/00 +0100, you wrote:
>| >>Ok here's my take on the problem, and actually what I use.
>| >>
>| >>Notes, we are always reloading the the same page results.cfm (ie. next
>| >>& prev links link right back to results.cfm), there are three
>| >>variables which will get passed, startRow, maxRows & resultIDList, all
>| >>of which I assume are undefined when I first enter the page. startRow
>| >>& maxRows are passed via the URL, resultIDList is passed in some other
>| >>fashion; client.resultIDList, or whatever takes your fancy (passing
>| >>all three in a hidden form is quite nice).
>| >>
>| >>Enter page.
>| >>if startRow & maxRows are undefined, give them some value.
>| >>if resultIDList is undefined then...
>| >> Do the QUERY, but return only the ID values:
>| >> SELECT id FROM somewhere WHERE somethingComplex
>| >> create resultIDList as an empty list, and then loop round the query
>| >>populating the list
>| >> resultIDList = ''
>| >> <loop query="aboveQuery">resultIDList =
>| >>listAppend(resultIDList,#id#)</loop>
>| >>endif
>| >>
>| >>What we have done here is checked to see if a list already existed, if
>| >>it didn't we run the query to get all the records which match our
>| >>search out, and only return the ID value.
>| >>Next time we enter this page, the list will already have been defined,
>| >>therefore the query will not run again (handy for those 200 record
>| >>returns)
>| >>
>| >>Now you create a subset of that list using the startRow and maxRow,
>| >>I'll leave that exercise up to the cunning of the reader. (subsetList
>| >>= from startRow to startRow+maxRows, do listGetAt etc. etc. etc. etc.)
>| >>
>| >>Finally we can pull all we really need from the database using our
>| >>subList;
>| >>SELECT * FROM somewhere WHERE id IN (#sublist#)
>| >>
>| >>So what happened here? Well we hit the database once for the big-hit
>| >>of 200 records or so, and then again (say 20 times) for our display of
>| >>results for this page. But once we move onto the next page, we don't
>| >>run that big assed query again, just the smaller 20 hit one.
>| >>
>| >>Of course we could cache the first query <CFQUERY NAME="bigassedQ"
>| >>DATASOURCE="myData" CACHEWITHING="#CreateTimeSpan(0,6,0,0)#">, so
>| >>rerunning it on each page didn't really give us a big database
>| >>slowdown, but, here's the pay off...
>| >>
>| >>You can use the above method to run a number of queries the first time
>| >>to enter the page, and append the results to the list. For example 3
>| >>queries...
>| >>
>| >>SELECT id FROM table WHERE a=x AND b=y AND c=z (exact match)
>| >> returns records 6 & 7
>| >> add results to subList
>| >>
>| >>SELECT id FROM table WHERE a=x AND (b=y OR c=z)
>| >> AND id NOT IN (#subList#) (more relaxed)
>| >> returns records 2,3 & 12
>| >> add results to subList
>| >>
>| >>SELECT id FROM table WHERE a=x OR b=y OR c=z
>| >> AND id NOT IN (#subList#) (very relaxed)
>| >> returns records 1,4,5 & 9
>| >> add results to list
>| >>
>| >>I end up with the list subList = 6,7,2,3,12,1,4,5,9
>| >>
>| >>Notice the "AND id NOT IN (#subList#)", in the above case, any records
>| >>found in the first query would also be found in the second one, unless
>| >>we tell it not to find the records we already have IDs for.
>| >>
>| >>I can now use this list to base the rest of my search on.
>| >>SELECT * FROM table WHERE id IN (#subList#)
>| >>
>| >>The records will be returned in the order that the IDs appear in the
>| >>list, that way the closest matches are returned first going to loose
>| >>matches at the end of the list.
>| >>
>| >>I suspect there are certain issues which may need looking at. The
>| >>exact way you pass the large results list from page to page will
>| >>depend on your situation, and total number of records you're dealer
>| >>with. If you're using strings instead of ints for your key IDs,
>| >>you'll need to address some single quote issues.
>| >>
>| >>I also suspect you can gain something from using an array instead of a
>| >>list, as this will possibly speed up the creation of a sub list. But
>| >>I haven't even looked into it.
>| >>
>| >>Hope this is of help to someone.
>| >>
>| >>Dan.
>| >>
>| >>
>| >>
>| >>This message is intended only for the use of the person(s) ("the
>intended
>| >>recipient(s)") to whom it is addressed.
>| >>
>| >>It may contain information which is privileged and confidential within
>the
>| >>meaning of the applicable law. If you are not the intended recipient,
>| >>please contact the sender as soon as possible.The views expressed in
>this
>| >>communication may not necessarily be the views held by Live Information
>| >>Systems Limited.
>| >>
>| >>
>|
>>>--------------------------------------------------------------------------
>----
>| >>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>| >>To Unsubscribe visit
>| >>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
>or
>| >>send a message to [EMAIL PROTECTED] with 'unsubscribe'
>in
>| >>the body.
>| >
>|
>>---------------------------------------------------------------------------
>---
>| >Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>| >To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
>|
>|
>| --------------------------------------------------------------------------
>-
>| Peter Theobald, Chief Technology Officer
>| LiquidStreaming http://www.liquidstreaming.com
>| [EMAIL PROTECTED]
>| Phone 1.212.545.1232 Fax 1.212.679.8032
>|
>| --=====================_20677026==_.ALT
>| Content-Type: text/html; charset="us-ascii"
>|
>| <html>
>| <font size=3>On the mailing list I *CONSTANTLY* hear people mention
>| facts that are not in the Cold Fusion documentation.<br>
>| In this example, how did you know there is a limit of 100 cached queries?
>| The Cold Fusion Language Reference doesn't say that in the description of
>| CFQUERY.<br>
>| <br>
>| Where is everybody getting this information.<br>
>| I must say that, although I love working in Cold Fusion, the Cold Fusion
>| documentation is among the WORST language documentation I have ever had
>| the misfortune of struggling with.<br>
>| <br>
>| Even the so-called Reference manual is little more than a brief
>| description of each TAG.<br>
>| Where is the definitive definition of exactly how each element
>| behaves?<br>
>| <br>
>| How can they say <CFSCRIPT> is "like" Javascript, but not
>| exactly?! Where is the rigorous description of every element?<br>
>| When I write CFSCRIPT programs that are "like" Javascript, I
>| find out by trial and error just how "like" it is and
>| isn't.<br>
>| <br>
>| At 07:17 AM 8/4/00 -0700, paul smith wrote:<br>
>| <br>
>| <blockquote type=cite cite>I've done something similar, except I cache
>| the big query for 5 minutes.<br>
>| <br>
>| In addition, I store the long list of IDs as a client variable (in a
>| <br>
>| database). The cache thus trades the need to create sublists for a
>| <br>
>| database read AND speeds up the paging queries (since the cache is in
>| <br>
>| memory). Works well, but on a busy site I'm concerned I'll run up
>| against <br>
>| the limit of 100 cached queries.<br>
>| <br>
>| I also wonder about the speed of lists, both the time it takes to create
>| <br>
>| them, and the resultant use of WHERE ID IN (#ID_List#), compared to using
>| a <br>
>| JOIN on a table that contains the IDs as a column.<br>
>| <br>
>| Anyone?<br>
>| <br>
>| best, paul<br>
>| <br>
>| At 10:14 AM 8/4/00 +0100, you wrote:<br>
>| >Ok here's my take on the problem, and actually what I use.<br>
>| ><br>
>| >Notes, we are always reloading the the same page results.cfm (ie.
>| next<br>
>| >& prev links link right back to results.cfm), there are
>| three<br>
>| >variables which will get passed, startRow, maxRows &
>| resultIDList, all<br>
>| >of which I assume are undefined when I first enter the page.
>| startRow<br>
>| >& maxRows are passed via the URL, resultIDList is passed in some
>| other<br>
>| >fashion; client.resultIDList, or whatever takes your fancy
>| (passing<br>
>| >all three in a hidden form is quite nice).<br>
>| ><br>
>| >Enter page.<br>
>| >if startRow & maxRows are undefined, give them some value.<br>
>| >if resultIDList is undefined then...<br>
>| > Do the QUERY, but return only the ID values:<br>
>| > SELECT id FROM somewhere WHERE
>| somethingComplex<br>
>| > create resultIDList as an empty list, and then loop
>| round the query<br>
>| >populating the list<br>
>| > resultIDList = ''<br>
>| > <loop query="aboveQuery">resultIDList
>| =<br>
>| >listAppend(resultIDList,#id#)</loop><br>
>| >endif<br>
>| ><br>
>| >What we have done here is checked to see if a list already existed,
>| if<br>
>| >it didn't we run the query to get all the records which match
>| our<br>
>| >search out, and only return the ID value.<br>
>| >Next time we enter this page, the list will already have been
>| defined,<br>
>| >therefore the query will not run again (handy for those 200
>| record<br>
>| >returns)<br>
>| ><br>
>| >Now you create a subset of that list using the startRow and
>| maxRow,<br>
>| >I'll leave that exercise up to the cunning of the reader.
>| (subsetList<br>
>| >= from startRow to startRow+maxRows, do listGetAt etc. etc. etc.
>| etc.)<br>
>| ><br>
>| >Finally we can pull all we really need from the database using
>| our<br>
>| >subList;<br>
>| >SELECT * FROM somewhere WHERE id IN (#sublist#)<br>
>| ><br>
>| >So what happened here? Well we hit the database once for the
>| big-hit<br>
>| >of 200 records or so, and then again (say 20 times) for our display
>| of<br>
>| >results for this page. But once we move onto the next page, we
>| don't<br>
>| >run that big assed query again, just the smaller 20 hit one.<br>
>| ><br>
>| >Of course we could cache the first query <CFQUERY
>| NAME="bigassedQ"<br>
>| >DATASOURCE="myData"
>| CACHEWITHING="#CreateTimeSpan(0,6,0,0)#">, so<br>
>| >rerunning it on each page didn't really give us a big database<br>
>| >slowdown, but, here's the pay off...<br>
>| ><br>
>| >You can use the above method to run a number of queries the first
>| time<br>
>| >to enter the page, and append the results to the list. For
>| example 3<br>
>| >queries...<br>
>| ><br>
>| >SELECT id FROM table WHERE a=x AND b=y AND c=z (exact
>| match)<br>
>| > returns records 6 & 7<br>
>| > add results to subList<br>
>| ><br>
>| >SELECT id FROM table WHERE a=x AND (b=y OR c=z)<br>
>| > AND id NOT IN
>|
>(#subList#)  
>;
>| (more relaxed)<br>
>| > returns records 2,3 & 12<br>
>| > add results to subList<br>
>| ><br>
>| >SELECT id FROM table WHERE a=x OR b=y OR c=z<br>
>| > AND id NOT IN
>|
>(#subList#)  
>;
>| (very relaxed)<br>
>| > returns records 1,4,5 & 9<br>
>| > add results to list<br>
>| ><br>
>| >I end up with the list subList = 6,7,2,3,12,1,4,5,9<br>
>| ><br>
>| >Notice the "AND id NOT IN (#subList#)", in the above case,
>| any records<br>
>| >found in the first query would also be found in the second one,
>| unless<br>
>| >we tell it not to find the records we already have IDs for.<br>
>| ><br>
>| >I can now use this list to base the rest of my search on.<br>
>| >SELECT * FROM table WHERE id IN (#subList#)<br>
>| ><br>
>| >The records will be returned in the order that the IDs appear in
>| the<br>
>| >list, that way the closest matches are returned first going to
>| loose<br>
>| >matches at the end of the list.<br>
>| ><br>
>| >I suspect there are certain issues which may need looking at.
>| The<br>
>| >exact way you pass the large results list from page to page
>| will<br>
>| >depend on your situation, and total number of records you're
>| dealer<br>
>| >with. If you're using strings instead of ints for your key
>| IDs,<br>
>| >you'll need to address some single quote issues.<br>
>| ><br>
>| >I also suspect you can gain something from using an array instead of
>| a<br>
>| >list, as this will possibly speed up the creation of a sub
>| list. But<br>
>| >I haven't even looked into it.<br>
>| ><br>
>| >Hope this is of help to someone.<br>
>| ><br>
>| >Dan.<br>
>| ><br>
>| ><br>
>| ><br>
>| >This message is intended only for the use of the person(s) ("the
>| intended <br>
>| >recipient(s)") to whom it is addressed.<br>
>| ><br>
>| >It may contain information which is privileged and confidential
>| within the <br>
>| >meaning of the applicable law. If you are not the intended recipient,
>| <br>
>| >please contact the sender as soon as possible.The views expressed in
>| this <br>
>| >communication may not necessarily be the views held by Live
>| Information <br>
>| >Systems Limited.<br>
>| ><br>
>| ><br>
>|
>>------------------------------------------------------------------------
>------<br>
>| >Archives:
>| <a href="http://www.mail-archive.com/cf-talk@houseoffusion.com/"
>eudora="autourl">http://www.mail-archive.com/cf-talk@houseoffusion.com/</a><
>br>
>| >To Unsubscribe visit <br>
>| ><a
>href="http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
>_talk"
>eudora="autourl">http://www.houseoffusion.com/index.cfm?sidebar=lists&bo
>dy=lists/cf_talk</a>
>| or <br>
>| >send a message to [EMAIL PROTECTED] with
>| 'unsubscribe' in <br>
>| >the body.<br>
>| <br>
>| --------------------------------------------------------------------------
>----<br>
>| Archives:
>| <a href="http://www.mail-archive.com/cf-talk@houseoffusion.com/"
>eudora="autourl">http://www.mail-archive.com/cf-talk@houseoffusion.com/</a><
>br>
>| To Unsubscribe visit
>| <a
>href="http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
>_talk"
>eudora="autourl">http://www.houseoffusion.com/index.cfm?sidebar=lists&bo
>dy=lists/cf_talk</a>
>| or send a message to [EMAIL PROTECTED] with 'unsubscribe'
>in the body. </font></blockquote><br>
>|
>| <font size=2><b><br>
>| --------------------------------------------------------------------------
>-<br>
>| Peter Theobald, </b>Chief Technology Officer<br>
>| </font><font size=3 color="#0000FF"><b>LiquidStreaming </b></font><a
>href="http://www.liquidstreaming.com/" eudora="autourl"><font size=2
>color="#0000FF"><u>http://www.liquidstreaming.com</a><br>
>| </u></font><font size=2>[EMAIL PROTECTED]<br>
>| <b>Phone</b> 1.212.545.1232 <b>Fax</b> 1.212.679.8032<br>
>| </font></html>
>|
>| --=====================_20677026==_.ALT--
>|
>| --------------------------------------------------------------------------
>----
>| Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>| To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>the body.
>
>------------------------------------------------------------------------------
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
>message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
---------------------------------------------------------------------------
Peter Theobald, Chief Technology Officer
LiquidStreaming http://www.liquidstreaming.com
[EMAIL PROTECTED]
Phone 1.212.545.1232 Fax 1.212.679.8032
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
