• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Breaking down your query results into pages (Paging Tutorial) Part-II
This is an extension to my last tutorial "Breaking down your query results into pages (Paging Tutorial)" which is posted here on www.easycfm.com
In the last tutorial, you could retrieve a dataset with a <cfquery>, then use a technique to seperate the results over several pages. It simply ;
- took the total "recordCount"
- divided that with the "number of records per page"
- then displayed the number of pages at the bottom of the table.
It was lacking in some basics, like what would you do if you had 25 records and wanted to display 3 per page. You would then have to modify the script with some kind of a rounding function (rnd() or ceil()). It also used <cfloop> instead of using <cfoutput query="" maxrows="" startrow="">. It was a basic tutorial.

I have tried something a little more sophisticated here. I needed paging in almost every part of my application, so I created a custom tag. I would like to share that information, perhaps you may find it useful....

Just follow along...

----------------------------------------------------------------------------------------------------
Create two files 1. paging.cfm and 2. paging_tester.cfm (or whatever.cfm)
----------------------------------------------------------------------------------------------------
PAGING.CFM
----------------------------------------------------------------------------------------------------
<!--- The following attributes are needed. If you see a value for "default=", then that is an option part of our tag --->

<cfparam name="attributes.QueryName" default="">
<!--- REQUIRED : Name of the query that is used in the calling page--->

<cfparam name="attributes.SelectedColumns" default="">
<!--- REQUIRED : List of columns to be shown --->
<!--- if column list is not given, or cfquery.columnlist is used, then the columns will appear, ordered by their names --->

<cfparam name="attributes.ShowRowCount" type="boolean" default="false">
<!--- OPTIONAL : Shows row number in the first column --->

<cfparam name="attributes.ReplaceChar" default="_">
<cfparam name="attributes.ReplaceWith" default=" ">
<!--- OPTIONAL : Column Names are like "emp_name" then the underscore can be replaced with a "space" --->

<cfparam name="attributes.ShowDiagnostics" type="boolean" default="false">
<!--- OPTIONAL : Diagnostic information at the end of query --->

<cfparam name="attributes.RecordsPerPage" default="25">
<!--- OPTIONAL : This is used for paging --->

<!--- BEGIN GENERAL SETUP : ----------------------------------------------- --->
Lets try to forsee everything we might need during the processing of this custom tag ...
--->

<!---
This is used at the end, while showing the diagnostics row. cfquery provides 4 diagnostic variables like recordcount, currentrow, executiontime and columnlist.
- If we want to display the diagnostic information, (see <cfparam> tag above) we will do so in the same table as the query result.
- If we want to display the query_name.currentrow (i.e. serial number of the rows), we need to add that column so we can format the table correctly.
Thats exactly what the following does...
--->

<cfif attributes.ShowRowCount>
    <cfset diagColumnWidth = ListLen(attributes.SelectedColumns)+1>
<cfelse>
    <cfset diagColumnWidth = ListLen(attributes.SelectedColumns)>
</cfif>

<!---
Set up the variables so that we can evaluate them later, if they ask...
--->

<cfset ExecutionTime = "caller.cfquery.ExecutionTime">
<cfset RecordsReturned = "caller.#attributes.QueryName#.recordcount">

<!---
Since we want "page numbers" and "next" & "previous" links at the bottom of the table, lets setup some variables for that
--->

<cfif attributes.RecordsPerPage neq "">
    <!--- START Setup Paging --->
        <cfset TotalRecords = evaluate(RecordsReturned)>
        <cfset TotalPages = Ceiling(TotalRecords/attributes.RecordsPerPage)>
        <cfparam name="URL.ThisPage" default="0">
        <cfset StartRow = (URL.ThisPage*attributes.RecordsPerPage)+1>
        <cfset NextPage = URL.ThisPage+1>
        <cfset PreviousPage = URL.ThisPage-1>
    <!--- END setup for paging --->
</cfif>

<!--- END GENERAL SETUP : ----------------------------------------------- --->

<!--- Tag Start: --->

<!---
When a custom tag has an end tag, coldfusion calls it twice. Once when <cf_custom_tag ...> is encountered/opened and second when </cf_custom_tag> is encountered/closed. We should be able to use this to our advantage...
If you ask for the variable value of "thisTag.ExecutionMode" it will give you either "Start" or "End". This is how we know when the custom tag started processing and when it ended.
--->

<cfif ThisTag.ExecutionMode eq 'Start'>

<!--- check to see if user provided an end tag --->
<cfif not ThisTag.HasEndTag>
    <!--- Show message on error --->
    <h3>An error occurred ...</h3>
    <pre>
        The cf_paging tag requires an end tag. Please make sure that it exists and try again.
        The tag processing aborted...
    </pre>
    <cfexit method="exittag">
    <!--- If no end tags are found "exit" tag processing --->
</cfif>

<!--- if end tag was found, start processing table for query output --->

<table>
    <tr bgcolor="beige">

    <!---
        check if the user wants serial number as the first column. Remember we setup all this information before hand.
    --->

    <cfif attributes.ShowRowCount>
        <th>Record Count</th>
        </cfif>

    <!--- Now lets render the column headers from the query --->
    <cfloop index="ColHeaders" list="#attributes.SelectedColumns#">
        <cfoutput>
            <th>#ReReplace(ColHeaders, attributes.ReplaceChar, attributes.ReplaceWith, "All")#</th>
        </cfoutput>
    </cfloop>

    </tr>
    <!--- // ------------------------------------------------------------------------ // --->
    <!--- start populating the table values --->

    <!--- You can use the "caller" scope, so you can call/reference the set variables in the calling template --->
    <cfoutput query="caller.#attributes.QueryName#" maxrows="#attributes.RecordsPerPage#" startrow="#StartRow#">
    <tr>
        <!--- check to see if user wants serial number column. if "yes" use current row attribute of cfquery --->
        <cfif attributes.ShowRowCount>
            <cfset getRecordCount = "caller.#attributes.QueryName#.currentrow">
            <!--- Again, we set this up before --->
            <td>#evaluate(getRecordCount)#</td>
        </cfif>
        <!--- Render the query output here --->
        <cfloop index="ColValue" list="#attributes.SelectedColumns#">
            <td>#evaluate(ColValue)#</td>
        </cfloop>
        </tr>
    </cfoutput>
    <!--- // ------------------------------------------------------------------------ // --->

    <!--- This is the first part of our tag. Now we want to do stuff at the end. Like showing diagnostics, showing page navigation menu... Execute this part when the     custom tag is called again. The following <cfelse> tag is really saying, <cfif thisTag.ExecutionMode eq "end"> as we discussed above
    --->

<cfelse>

    <!--- BEGIN DIAGNOSTIC SETUP: ----------------------------------------------------------------- --->
    <!--- if user wants to see diagnostics, show the following below --->
    <cfif attributes.ShowDiagnostics>
        <tr>
            <td bgcolor="#CCCCCC" colspan="<cfoutput>#diagColumnWidth#</cfoutput>">
            <cfoutput>

            <div style="font-family:monospace;font-size:8pt;text-align:left;">
                Query Execution Time: #evaluate(ExecutionTime)# ms<br>
                Records Returned : #evaluate(RecordsReturned)#
            </div>
            
            </cfoutput>
            </td>
        </tr>
    </cfif>
    <!--- END DIAGNOSTIC SETUP: ----------------------------------------------------------------- --->

    <!--- BEGIN PAGING SETUP: ----------------------------------------------------------------- --->

    <cfif attributes.RecordsPerPage neq "">
    <tr>
        <td colspan="<cfoutput>#diagColumnWidth#</cfoutput>">
            <!--- // ----------------------------------------------------------------- // --->
            <!--- A small internal table to keep our formatting intact --->
            <table width="100%" align="center">
                <tr>
                    <td align="left">
                    <cfif PreviousPage gte 0>
                        <a href="?ThisPage=<cfoutput>#PreviousPage#</cfoutput>">Previous</a>
                    <cfelse>
                        Previous
                    </cfif>
                    </td>
                    <!--- // ----------------------------------------------------------------- // --->
                    <td align="center">
                        Pages :
                        <cfloop index="pages" from="0" to="#TotalPages-1#">
                            <cfoutput>
                                <cfif URL.ThisPage eq pages>#pages+1#<cfelse><a href="?ThisPage=#pages#">#pages+1#</a></cfif>
                            </cfoutput>
                        </cfloop>
                    </td>
                    <!--- // ----------------------------------------------------------------- // --->
                    <td align="right">
                        <cfif (TotalPages-1) gt URL.ThisPage>
                            <a href="?ThisPage=<cfoutput>#NextPage#</cfoutput>">Next</a>
                        <cfelse>
                            Next
                        </cfif>
                    </td>
                    <!--- // ----------------------------------------------------------------- // --->
                </tr>
            </table>

            <!--- // ----------------------------------------------------------------- // --->
            </td>
        </tr>
    </cfif>
</table>
<!--- End of ExecutionMode for this custom tag --->
</cfif>

<!---
Now that you have most of the work done you can save this file in the same folder or coldfusion's custom tag folder.
If you find this useful and want to use in other applications, use the coldfusion's custom tag folder
--->

-----------------------------------------------------------------------------------
PAGING_TESTER.CFM
-----------------------------------------------------------------------------------
<!---
This is the calling page. All you need to do here is query the database as usual, then call our custom tag.
Technically, for basic functionality, thats about 5 lines of code that you will write from this page on and every other template to render a query output...
Thats it.
Follow along.
--->

<!--- QUERY: --->
<cfquery name="qtest" datasource="SpacePro">
    select * from mytable
</cfquery>
<!--- QUERY: --->

<!---
NOTE: since we are using "select *" in our query, the resulting column list is going to be ordered by column names. If you do not want that, a simple workaround is to setup columnlist yourself, like;
<cfset myColumnList = "col5, col4, col10, col9, col11,"> (so 6 lines of code, thats not so bad...)
The you can use "SelectedColumns="#myColumnList#" " as the custom tag attribute
--->

<!--- call the custom tag here --->

<cf_paging QueryName="qTest" SelectedColumns="#qTest.columnList#" ShowDiagnostics="false" ShowRowCount="true" RecordsPerPage="25">
    <!--- All code inserted here will go on top of the table displayed --->
    <!--- For example Style sheet for the navigation pane can be inserted here --->