SfC Home > Web Design > SQL > ColdFusion >
List Ten-at-a-Time from a ColdFusion Query
by Ron Kurtus
You can list items from a database table with a common query. Even if you filter the results, you may get a long list of items. There is a method to list a select number of items at a time.
Questions you may have include:
- What is a simple way of doing it?
- What code is required?
- How does it work?
This lesson will answer those questions.
First 10 items
Suppose you make a simple query.
<cfquery name="list_items" datasource="db_name">
select item
from my_tables
orderby itemID
</cfquery>
To get the first 10 items, you set boundaries in your output.
<cfoutput query="list_items" startrow="1" maxrows="10">
<P>#item#</P>
</cfoutput>
Repeated method
To list the first 10 items and then be able to click on a link to repeatedly show the next 10, you could use the following code:
<cfapplication name="Next10" sessionmanagment="Yes">
<cfquery name="list_items" datasource="db_name">
select item
from my_tables
orderby itemID
</cfquery><cfif IsDefined("url.next")>
<cfset session.start = #session.start# + 10>
<p>List of next 10 items</p>
<cfoutput query="list_items" startrow="#session.start#" maxrows="10">
<p>#item#</p>
</cfoutput><p><a href="this_page.cfm?next=10">Next 10</a></p>
<cfelse>
<p>List of first 10 items</p>
<cfset start = 1>
<cfset session.start = #start#>
<cfoutput query="list_items" startrow="#session.start#" maxrows="10">
<p>#item#</p>
</cfoutput><p><a href="this_page.cfm?next=10">Next 10</a></p>
</cfif>
Explanation
You really want the whole application to be on one page. When you open the page, url.next has not been defined, so you start at the <CFELSE> term.
Session management is used so that the variables you set will be carried forward, when you click the link to go to the next view of this page.
The expression in the link: ?next=10 will send url.next to the <CFIF> tag. "Next" could be set to equal anything. I used 10 just to indicate a list of 10 items.
In the <CFIF> section, the STARTROW is now the previous one plus 10.
To list a items other than 10 at a time, change the value of X in MAXROWS="X" and <CFSET session.start = #session.start# + X>.
Summary
This is an easy way to list the output of your query 10 items at a time. You can adjust it to list any number you wish.
Programming rocks
Resources and references
Websites
Books
(Notice: The School for Champions may earn commissions from book purchases)
Questions and comments
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. I will try to get back to you as soon as possible.
Share this page
Click on a button to bookmark or share this page through Twitter, Facebook, email, or other services:
Students and researchers
The Web address of this page is:
www.school-for-champions.com/coldfusion/
next10.htm
Please include it as a link on your website or as a reference in your report, document, or thesis.
Where are you now?
List Ten-at-a-Time from a ColdFusion Query