10 Replies Latest reply on Jan 16, 2010 9:52 AM by hermes980

    Cfoutput and Paging


      Hi all,


      I am trying to setup paging for a product catalog. I have setup startrows and maxrows in my cfoutput query and it works great, but how do I make it so it pages? (i.e. paging through with a next, back or even numbered list).


      I am looking for a clean, not complicated solution.


      Thanks in advance.

        • 1. Re: Cfoutput and Paging
          hlevin Level 1

          The only option that I am aware of is using Top-N query approach. Of course it depends on what database you are using and if that database supports nesting subqueries.


          If you are on MySQL, it's very easy as it has LIMIT clause. select * from tablename limit 10,10 (get 10 records starting with 11th)


          If you are using Oracle, Top-N query would be your solution. Here is the URL that talks about Top-N query solution: http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html


          I am not sure about other databases but Top-N approach is pretty generic.


          Good luck,


          • 2. Re: Cfoutput and Paging
            Daniel Harvey Level 1

            Here I googled pagin in cf and this page came up.  Might be of help to you.



            1 person found this helpful
            • 3. Re: Cfoutput and Paging
              hermes980 Level 1

              Thanks, I will give that a try later tonight. I saw that article in my searching, but wanted to make sure I am not missing something more obvious.

              • 4. Re: Cfoutput and Paging
                hermes980 Level 1

                Thanks for that, but its not quite working. What have others used for paging? I am trying to use this, but it won't send me to the next page.



                • 5. Re: Cfoutput and Paging
                  hermes980 Level 1

                  What do others use for paging? Is there something more straightfoward like how .NET can enable paging?

                  • 6. Re: Cfoutput and Paging
                    WestSide Level 1



                    You could use jquery for a data grid and or ExtJS and populate that grid using ColdFusion.  Most of these JS libraries have UI widgets that have paging built in.  You can also roll your own in CF.

                    • 7. Re: Cfoutput and Paging
                      BKBK Adobe Community Professional & MVP

                      I am trying to setup paging for a product catalog. I have setup startrows and maxrows in my cfoutput query and it works great, but how do I make it so it pages? (i.e. paging through with a next, back or even numbered list).


                      I am looking for a clean, not complicated solution.

                      Coldfusion has a near-perfect, if not perfect, solution, namely, cfgrid plus bind attribute. Watch the demo video: Ajax controls, data grid

                      • 8. Re: Cfoutput and Paging
                        TLC-IT Level 3

                        Actually, you can reference a query-result object as though it were an array.


                        Munch on this function a little bit...  notice how it accesses the query-result by row-number and field-names:


                        <!--- QueryToStruct:
                            SOURCE: http://www.bennadel.com/blog/149-Ask-Ben-Converting-A-Query-To-A-Struct.htm
                        <cffunction name="QueryToStruct"
                                    hint="Converts an entire query or the given record to a struct. This might return a structure or an array of structures.">

                            <!--- Define arguments.
                                Query:      the query to be processed.
                                Row:      the index of a single row to be returned (as a single structure), or '0' to specify that
                                            all rows are to be returned as an array of structures.  Row-numbers start at '1.'
                                (While it is certainly possible in ColdFusion to extract an arbitrary range of rows from a query
                                  object, which CF sees as a "collection," this particular function simply has no need to support
                                  that "feechur.")
                            <cfargument name="Query" type="query"   required="true" />
                            <cfargument name="Row"   type="numeric" required="false" default="1" />    <!--- DEFAULT IS TO RETURN A STRUCT CONTAINING ONE ROW --->
                                // Define the local scope.
                                var LOCAL = StructNew();
                                // Determine the indexes that we will need to loop over:  either a given row, or all of them.
                                if (ARGUMENTS.Row){
                                    // We are only looping over one row.
                                    LOCAL.FromIndex = ARGUMENTS.Row;
                                    LOCAL.ToIndex   = ARGUMENTS.Row;
                                } else {
                                    // We are looping over the entire query.
                                    LOCAL.FromIndex = 1;
                                    LOCAL.ToIndex   = ARGUMENTS.Query.RecordCount;
                                // Get the list of columns as an array and the column count.
                                LOCAL.Columns     = ListToArray( ARGUMENTS.Query.ColumnList );
                                LOCAL.ColumnCount = ArrayLen( LOCAL.Columns );
                                // Create an array to keep all the objects.
                                LOCAL.DataArray = ArrayNew( 1 );
                                // Loop over the rows to create a structure for each row.
                                for (LOCAL.RowIndex = LOCAL.FromIndex; LOCAL.RowIndex LTE LOCAL.ToIndex; LOCAL.RowIndex++)
                                    // Create a new structure for this row.
                                    ArrayAppend( LOCAL.DataArray, StructNew() );
                                    // Get the index of the current data array object.
                                    LOCAL.DataArrayIndex = ArrayLen( LOCAL.DataArray );
                                    // Loop over the columns to set the structure values.
                                    for (LOCAL.ColumnIndex = 1; LOCAL.ColumnIndex LTE LOCAL.ColumnCount; LOCAL.ColumnIndex++)
                                        // Get the column value.
                                        LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
                                        // Set column value into the structure.
                                        LOCAL.DataArray[LOCAL.DataArrayIndex][LOCAL.ColumnName] = ARGUMENTS.Query[LOCAL.ColumnName][LOCAL.RowIndex];
                                // At this point, we have an array of structure objects that represent the rows in the query
                                //    over the indexes that we wanted to convert.
                                // If we did not want to convert a specific record, return the array.
                                // If we wanted to convert a single row, then return the just that STRUCTURE, not the array.
                                if (ARGUMENTS.Row){
                                    // Return the first array item.
                                    return( LOCAL.DataArray[ 1 ] );
                                } else {
                                    // Return the entire array.
                                    return( LOCAL.DataArray );

                        • 9. Re: Cfoutput and Paging
                          hermes980 Level 1

                          Thanks all, I am getting close.

                          • 10. Re: Cfoutput and Paging
                            hermes980 Level 1

                            I found a posting online and was able to adjust it. This worked perfectly! Thanks for all of your help everyone. Without your help, I would not have known what to look for!


                            <cfquery name = "list" datasource = "mydsn">
                               SELECT *
                               FROM mytable
                               ORDER BY mydate DESC

                            <!--- Set the number of records to display on each page. --->
                            <CFSET OnEachPage = 1>

                            <!--- Set the default startrow to 1 if a value was not passed. --->
                            <!--- Determine whether or not to show the previous or next links. --->
                            <CFPARAM NAME = "StartRow" DEFAULT = "1">
                            <!--- Set the value of endrow to the maxrows + startrow - 1 --->
                            <CFSET EndRow = StartRow + OnEachPage - 1>
                            <!--- If the end row is greater than the recordcount, determine how many
                            records are left. --->
                            <CFIF EndRow GTE list.RecordCount>
                            <CFSET EndRow = list.RecordCount>
                            <CFSET Next = false>
                            <!--- Othereise, set Next to true and determine the next set of records. --->
                            <CFSET Next = true>
                            <CFIF EndRow + OnEachPage GT list.RecordCount>
                            <CFSET NextNum = list.RecordCount - EndRow>
                            <CFSET NextNum = OnEachPage>
                            <CFSET NextStart = EndRow + 1>
                            <!--- If StartRow is 1, set Previous to false. --->
                            <CFIF StartRow IS 1>
                            <CFSET Previous = false>
                            <!--- Othewise, determine the previous set of records. --->
                            <CFSET Previous = true>
                            <CFSET PreviousStart = StartRow - OnEachPage>

                            <!--- Determine how many pages will be displayed. --->
                            <CFSET NumPages = Ceiling(list.RecordCount / OnEachPage)>
                            <CFPARAM NAME = "PageNum" DEFAULT = "1">

                            Now displaying records #StartRow# to #EndRow# of #list.RecordCount#.<P>

                            <TABLE BORDER = "0">
                            <TR><TD VALIGN = "top">
                            <!--- If Previous is true, display the previous link. --->
                            <CFIF Previous>
                            <A HREF =
                            &lt;&lt; Previous</A>
                            <CFLOOP FROM = "1" TO = "#NumPages#" INDEX = "ThisPage">
                            <CFIF ThisPage IS PageNum>
                            <CFSET PageNumStart = (((ThisPage - 1) *
                            OnEachPage) + 1)>
                            <TD><A HREF =
                            <TD VALIGN = "top">
                            <!--- If Next is true, display the previous link. --->
                            <CFIF Next>
                            <A HREF =
                            Next &gt;&gt;</A>
                            <TR><TD VALIGN = "top" COLSPAN = "<CFOUTPUT>#Evaluate(NumPages +


                            <CFOUTPUT QUERY = "list" STARTROW = "#startrow#" MAXROWS =