Pagination code is not preserving additional filters

Question

I'm very new to ColdFusion and I would like to ask about my code. I am trying to create a page with filtering and pagination in ColdFusion. I have already made some progress with it, but unfortunately when my page refresh all the filtering made using text fields are not carried over to the next pages.

Here my code for filtering:

<cfparam name = "site_name" default = ""/>
<cfparam name = "site_cat" default = ""/>
<cfparam name = "site_region" default = ""/>
<cfparam name = "site_area" default = ""/>
<cfparam name = "query_condition" default = ""/>

<cfset flcr = StructNew()/>
<cfset flcr.site_name = "#site_name#"/> 
<cfset flcr.site_cat = "#site_cat#"/> 
<cfset flcr.site_region = "#site_region#"/> 
<cfset flcr.site_area = "#site_area#"/> 

<cfif StructKeyExists(Form,"btn_search")>
    <cfloop collection = "#flcr#" item = "key">
        <cfif #len(trim(flcr[key]))# gt 0>
            <cfset queryCases = ["SITE_NAME","SITE_CAT","SITE_REGION","SITE_AREA"]/>

            <cfif #arraycontains(queryCases,key)# eq "yes">
                <cfif #len(trim(query_condition))# gt 0>
                    <cfset query_condition &= " AND "/>
                <cfelse>
                    <cfset query_condition &= " WHERE "/>
                </cfif>
            </cfif>

            <cfswitch expression = #key#>
                <cfcase value = "SITE_NAME">
                    <cfset site_name = "#flcr[key]#"/>
                    <cfset query_condition &= "site_name LIKE '#flcr[key]#%'" />
                </cfcase>

                <cfcase value = "SITE_CAT">
                    <cfset site_cat = "#flcr[key]#"/>
                    <cfset query_condition &= "site_type LIKE '#flcr[key]#%'"/>
                </cfcase>                

                <cfcase value = "SITE_REGION">
                    <cfset site_region = "#flcr[key]#"/>
                    <cfset query_condition &= "region = '#flcr[key]#'"/>
                </cfcase>                

                <cfcase value = "SITE_AREA">
                    <cfset site_area = "#flcr[key]#"/>
                    <cfset query_condition &= "area = '#flcr[key]#'"/>
                </cfcase>
            </cfswitch>
        </cfif>
    </cfloop>     
</cfif>
<cfset viewAll = "SELECT * FROM tblcellsites" & #query_condition#/>
<cfset orderBy = " ORDER BY site_count"/>
<cfset final_sql = #viewAll# & #orderBy#/>
<cfset final_final_sql = #replace(final_sql,"'","''","all")#/>
<cfset filterQuery = #site.filterQuery(final_sql)#/>

Pagination

    <table class="table">
        <thead class="thead-light">
            <tr>
                <th scope="col">Site Count</th>
                <th scope="col">Site Name</th>
                <th scope="col">Region</th>
                <th scope="col">Area</th>
                <th scope="col">Site Type</th>
            </tr>
        </thead>

        <tbody>
            <cfparam name = "pageNum" default = "1"/>
            <cfset viewAllSite = #filterQuery#/>
            <cfset maxRows = 10/>
            <cfset startRow = min( ( pageNum-1 ) * maxRows+1, max( viewAllSite.recordCount,1 ) )/>
            <cfset endRow = min( startRow + maxRows-1, viewAllSite.recordCount )/>
            <cfset totalPages = ceiling( viewAllSite.recordCount/maxRows )/>

            <cfset loopercount = round( viewAllSite.recordCount/10 )/>

            <cfoutput query="viewAllSite" startrow="#startRow#" maxrows="#maxRows#">
                <tr>
                    <td>#site_count#</td>
                    <td>#site_name#</td>
                    <td>#region#</td>
                    <td>#area#</td>
                    <td>#site_type#</td>
                </tr>
            </cfoutput>
        </tbody>

    </table>
    <!-- Table Content -->
</div>

<!-- Pagination -->
<div class = "row" align = "center">
    <div class = "col-md-12">
    <cfoutput>
        <cfloop from="1" to="#looperCount#" index="i">
            <span id="#i#" class="current-page"><input type ="submit" name = "btn_page" value ="#i#"></span>
        </cfloop>
    </cfoutput> 
        </form>
    </div>
</div>
<!-- Pagination -->

I expected the filter to be carried over to the next pages but it seems the variables for filtering and query conditions are being cleared when I append the pageNum to my URL.

Thank you in advance.

Answer 1

I am just going to focus on the link part.

<!-- Pagination -->
<cfoutput>
<div class = "row" align = "center">
    <div class = "col-md-12">
        <form action="?" method="post">
           <input type="hidden" name="site_name" value="#EncodeForHTMLAttribute(site_name)#" />
           <input type="hidden" name="site_cat" value="#EncodeForHTMLAttribute(site_cat)#" />
           <input type="hidden" name="site_region" value="#EncodeForHTMLAttribute(site_region)#" />
           <input type="hidden" name="site_area" value="#EncodeForHTMLAttribute(site_area)#" />


           <cfloop from="1" to="#looperCount#" index="i">
             <input type ="submit" name = "btn_page" value ="#i#" />
           </cfloop>

        </form>
    </div>
</div>
</cfoutput> 
<!--/ Pagination -->

After doing post

After the data is posted, Check to make sure you are getting the fields you are expecting.

 <cfdump var="#form#">

Rather than building a new structure called flcr = StructNew(), just use the form structure.

OT

The way you are building the SQL is really odd. There are cleaner approaches to it. I would recommend creating a question on how to clean it up