Take the 2-minute tour ×

I have the below code on a ColdFusion page. This page displays the data from the query below having 25 records per page with pagination. Now I need to provide a textbox and search button so that the user can enter the positionid and hit search....the problem I'm having here is how can the nth page be displayed if the positionid is in for example 7th page out of 200 pages. Please advice. Thanks

<cfquery name="qry_postn_detail" datasource="mbtran">
    select distinct position_id,schedule_group,accrual_profile,pay_rule_name,rest_days
    from kronos_if.position_detail  
    order by position_id 

<cfset perpage = 25>

<cfparam name="url.start" default="1">
<cfif not isNumeric(url.start) or url.start lt 1 or url.start gt qry_postn_detail.recordCount or round(url.start) neq url.start>
    <cfset url.start = 1>

<cfset totalPages = ceiling(qry_postn_detail.recordCount / perpage)>
<cfset thisPage = ceiling(url.start / perpage)>

<cfset thisPage = Int(start / 25) + 1>

<cfloop from="1" to="#totalPages#" index="i">
    <cfif i is thisPage>
        <cfif totalPages lte 5 or i is 1 or i is totalPages or (i gt thisPage - 3 and i lt thisPage + 3) or ((thisPage is 1 or thisPage is 2) and i lt 6) >
            <a href="?start=#(i*25)-24#">#i#</a>
            <cfif i is 2 or i is totalPages - 1>
share|improve this question
Can you provide some more detail about how you are wanting this to work? You mention adding a textbox for searching but then you say they enter a specific positionid. Are you wanting to display all results again after a search or only records matching what they search for? Or are you wanting to show clickable page numbers to go back and forth between those page's results? More details please... –  Miguel-F Oct 3 '12 at 14:53
When the user enters the positionid and hits search the results have to be displayed matching the search results....with clickable page numbers to go back and forth...sorry for being unclear.... –  user747291 Oct 3 '12 at 15:09
add comment

1 Answer

up vote 1 down vote accepted

Based on your comment, I think all you need to do is include the search parameter in your query upon search form submission. Then the query result will only contain records matching what they searched for and your pagination code will still work as before but only for the matched records this time.

So your query would look something like:

    select distinct position_id,schedule_group,accrual_profile,pay_rule_name,rest_days
    from kronos_if.position_detail 
    <cfif IsDefined("form.searchfield") AND IsValid("integer", form.searchfield)>
        where position_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.searchfield#">
    order by position_id 

NOTE: I am assuming the position_id is an integer in your table. You will need to change the cfqueryparam type and validation logic appropriately based on it's actual type.

UPDATE from the new question posted in the comments below

Here is a quick and simple example of how to use JavaScript to jump to a particular page. You mentioned that you already have a select box with the page numbers. All you need to do is add the onChange event handler to your select tag and call a JavaScript function that will reload the page with the selected value appended to the url.

Your select will look something like this:

<select name="page" onChange="jump(this.value);">
    <option value="101">101</option>
    <option value="102">102</option>
    <option value="103">103</option>
    <option value="104">104</option>

A simple JavaScript example looks like this:

<script language="JavaScript">
    function jump(pagenum) {

Remember to validate/sanitize any and all values being passed to your ColdFusion pages.

share|improve this answer
thanks a lot it worked!!! –  user747291 Oct 3 '12 at 18:14
@user747291 Great! Glad I could help. If you wouldn't mind, check this as the answer so that others finding this question can find it. Thanks. –  Miguel-F Oct 3 '12 at 20:27
How can I add a search option for the specific page i want to go to. Like i have 200 pages. In order to jump to 120th page, instead of clicking till the 120th page. Please suggest. thanks –  user747291 Mar 14 '13 at 15:07
You should be able to pass the appropriate value in the URL to jump to any page. Like <a href="?start=120">120</a>. Knowing this you could have the user enter a value, validate the value they enter, if it is okay then use javascript to navigate to the page number they entered. –  Miguel-F Mar 14 '13 at 15:35
Can you please give an example?. I have a drop down now with all the page numbers. But not able to navigate to the speicifc page onchange. –  user747291 Mar 14 '13 at 16:21
show 1 more comment

Your Answer


