Solved

Fancy pagination controls and sorting functions for query results using ColdFusion

Posted on 2008-10-10
5
478 Views
Last Modified: 2012-06-21
http://www.thebookco.com/NEWSITE/COMPS/TBC_NewWebDesign_Results.jpg

The screenshot above depicts what it is I'm trying to accomplish with regards to pagination controls and sorting functions (I need to accomplish this using ColdFusion).

Here are the various different features that I need to replicate:

* The displaying of 3 records per row (and proper displaying of the last records returned from the query)
* The little "SORT BY" dropdown box that allows for the sorting based on 3 different criteria
* The "SHOW" dropdown box that allows for controlling how many items are displayed per page.
* The "<< PREV 1  2  3  4  5  6  7  8  9  NEXT >>" pagination controls.

Could someone please post an example that demonstrates how I might accomplish this using Coldfusion?

Ideally, I'd like to have something that's procedural rather than object oriented, as I am not using any kind of framework.  Also, it'd be great if the use of query string parameters could be avoided somehow --- though I'll gladly accept and use examples that require them if that makes things too complicated.

Thanks in advance,
- Yvan



0
Comment
Question by:egoselfaxis
  • 3
  • 2
5 Comments
 

Author Comment

by:egoselfaxis
ID: 22714622
chirp .. chirp .. [ sound of crickets ]

What's up folks?  Any thoughts?

- yg
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 22722848
Here's an example one method for paging through records.
It supports sorting, but doesn't really spell it out.   Let me know if you like the basic structure.


<!--- get the page number from the URL or form variable, otherwise, start at 1 ---->

<cfif isDefined('url.page') and val(url.page)>

  <cfset variables.pageNumber = url.page>

<cfelseif isDefined('form.page') and val(form.page)>

  <cfset variables.pageNumber = form.page>

<cfelse>

  <cfset variables.pageNumber = 1>

</cfif>
 
 

<cfset variables.items_per_page = 25> <!---- define how many records to show on a page ---->
 

<!--- this query fetches ALL records that match the criteria,

      alter this query with the correct where clause and order by 

      to get the right records in the right order. 

      To Keep the fetch small, we get ONLY the ID from this query

       --------->

<cfquery NAME="AllRecords" DATASOURCE="#request.datasource#">

  select ID --- fetch only the ID

  from MyTable

  where someFields = #variables.fitler#

  and   otherFields = #variables.otherFields#

  ORDER BY WHATEVER

</cfquery>
 

<!--- do a little math to figure out the max number of pages ---->

<cfset variables.numberOfPages = ceiling(AllRecords.recordCount / variables.items_per_page)>
 

<!---- make sure the page number requested is within the range of pages that we have ---->

<cfif variables.PageNumber le 0> <!---- if page number is too low, set it to first page --->

    <cfset variables.PageNumber = 1>

<cfelseif variables.pageNumber gt AllRecords.recordCount>  <!--- if page number passed is too high, set it to last page ---->

    <cfset variables.PageNumber = variables.numberOfPages>

</cfif>

<!---- based on the page number and number of items on a page, determine the start and end record for the page ---->

<!---- for example, page 3 with 25 items per page will show records 51 through 75, page 1 shows records 1-25 ---->

<cfset variables.startRecord   = ((variables.PageNumber - 1) * variables.items_per_page) + 1>

<cfset variables.endRecord     = variables.startRecord + items_per_page - 1>
 
 

<!---- Now we have the starting record number and the ending one, 

         so we are going to build a little list of the IDs for those records.

         If we show page 3, we will get the ID for records 51 through 76 ----->

<cfset variables.PageIDList = "">

<cfloop index="ii" from="#variables.startRecord#" to="#variables.endRecord#">

  <cfset variables.PageIDList = listAppend(variables.PageIDList, AllRecords['ID'][ii])>

</cfloop>
 

<!----- pageIDList now holds a list of IDS; such as 56,12,98,45,12,5,8, etc.

          These IDs are the IDs we need for this page.  Now fetch again to grab

          all the data we need about these;  So we SELECT * FROM... 

          Be sure to use the Same ORDER BY Clause as in AllRecords Query --------->

<cfquery NAME="thisPage" DATASOURCE="#request.datasource#">

  select * 

  from MyTable

  where ID in (#variables.PageIDList#)

  ORDER BY WHATEVER

</cfquery>
 
 

<!---- now you have the records you need for this page, use the thisPage

       query in a simple cfoutput to display the records ---->

<cfoutput query="thisPage">

 #thisPage.column1# #thisPage.column2# #thisPage.column3#<br>

</cfoutput>
 

<!---- now show the page list ---->

<cfloop index="ii" from="1" to="#variables.numberOfPages">

  <cfif variables.PageNumber is ii>

   #ii#

  <cfelse>

   <a href="/index.cfm?page=#ii#">#ii#</A>

  </cfif>

</cfloop>

Open in new window

0
 

Author Comment

by:egoselfaxis
ID: 22724820
Is there any way to adapt your example so that it displays the query results by displaying 3 items per row?  I can't seem to figure that part out.

Thanks,
- Yvan
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22725358
what do you mean by 3 items per row?   Are you saying a  <table> with three columns <td>  for each record of the query?
0
 

Author Comment

by:egoselfaxis
ID: 22746503
Nevermind - figured it out.  Thank you for the example you've given me.  I've awarded you the 500pts.

- Yvan
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now