Cfoutput and Paging

[Guest]

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.

[hlevin]

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,

Henry

[Daniel_Harvey]

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

http://blog.brianflove.com/articles/2007/09/10/efficient-paging-in-cf/

[Guest]

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.

[Guest]

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.

<cfquery name = "myquery" datasource = "mydb">
   SELECT *
   FROM mytable
   ORDER BY mydate DESC
</cfquery>
<cfparam name="URL.PageIndex" default="0">
<cfset RecordsPerPage = 5>
<cfset TotalPages = (myquery.Recordcount/RecordsPerPage)-1>
<cfset StartRow = (URL.PageIndex*RecordsPerPage)+1> 
<cfset EndRow = StartRow+RecordsPerPage-1> 
<cfoutput>
   <cfloop query="myquery">
   <cfif CurrentRow gte StartRow >
      
<p>#myquery.mydate#</p>
<p>#REReplace(myquery.field1,"((((https?:)\/\/)|(www\.))[-[:alnum:]\?%,\.\/&##!@:=\ +~_]+[A-Za-z0-9\/])", "<a href=""\1"" target=""_blank"">\1</a>", "ALL")#</p>
<p>#REReplace(myquery.field2,"((((https?:)\/\/)|(www\.))[-[:alnum:]\?%,\.\/&##!@:=\ +~_]+[A-Za-z0-9\/])", "<a href=""\1"" target=""_blank"">\1</a>", "ALL")#</p>
<hr />
      
   </cfif>
   <cfif CurrentRow eq EndRow>
      <cfbreak>
   </cfif>
   </cfloop>
</cfoutput> 
   <tr>
      <td colspan="4">
      <cfloop index="Pages" from="0" to="#TotalPages#">
      <cfoutput>
         |
         <cfset DisplayPgNo = Pages+1>
         <cfif URL.PageIndex eq pages>
            <strong>#DisplayPgNo#</strong>
         <cfelse>
            <a href="">#DisplayPgNo#</a>
         </cfif>
         |
      </cfoutput>
      </cfloop>

[Guest]

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

[WestSide]

Hi,

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.

[BKBK]

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

[TLC-IT]

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:
    RETURNS A "STRUCT" CONTAINING THE VALUES FROM A QUERY
    SOURCE: http://www.bennadel.com/blog/149-Ask-Ben-Converting-A-Query-To-A-Struct.htm
--->
<cffunction name="QueryToStruct"
            access="public"
            returntype="any"
            output="false"
            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 --->
   
    <cfscript>
        // 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 );
        }
    </cfscript>
</cffunction>

[Guest]

Thanks all, I am getting close.

[Guest]

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
</cfquery>

<!--- 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. --->
<CFELSE>
<CFSET Next = true>
<CFIF EndRow + OnEachPage GT list.RecordCount>
<CFSET NextNum = list.RecordCount - EndRow>
<CFELSE>
<CFSET NextNum = OnEachPage>
</CFIF>
<CFSET NextStart = EndRow + 1>
</CFIF>
<!--- If StartRow is 1, set Previous to false. --->
<CFIF StartRow IS 1>
<CFSET Previous = false>
<!--- Othewise, determine the previous set of records. --->
<CFELSE>
<CFSET Previous = true>
<CFSET PreviousStart = StartRow - OnEachPage>
</CFIF>

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

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

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

</TD>
</TR>
<TR>
</TABLE>

<CFOUTPUT QUERY = "list" STARTROW = "#startrow#" MAXROWS =
"#OnEachPage#">
#mytext#<BR>
</CFOUTPUT>
</BODY>
</HTML>