Server Paging in Coldfusion 8

hefterr
hefterr used Ask the Experts™
on
Hi,
I work for a small company  and I need advice on a common technique in ColdFusion 8.  Perhaps you can point me to a tutorial on the web and/or sketch out the approach:

I need to display the results of a query set (say list of companies) along with a checkbox next to each listing.

I want to do some standard paging controls using Coldfusion server side processing (I don't know Javascript/Jquery/Ajax).

So I'd like to list the first 25 entries and give the user the controls:
-  next 25
-  prev 25
-  last 25
-  first 25

Whenerver the user hits one of the control images the page would be submited and the appropriate page displayed from the server.

I also need to keep track of the items selected (via the checkbox) throughout the different pages (array as a session variable?).

I imaging I could hack out an approach and come up with a "square wheel" - but I imagine this is done commonly.

Thanks much in advance,
hefterr
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Just as an example and am assuming you are on Sql Server.
This has NOT been tested but I have used something very similar.
Was just trying to simplify it.
NOTE that this does not exactly answer your question but gets you started understanding how to paginate. THEN you can fairly easily throw in your exact specifications ...

<cfparam name="url.pageStart" default="1">
<cfif Not isNumeric(url.pageStart) or url.pageStart lt 1>
      <cfset url.pageStart=1>
</cfif>
<cfparam name="url.perPage" default="25">
<cfif Not isNumeric(url.perPage)>
      <cfset url.perPage=25>
</cfif>

<cfquery datasource="xxx" name="x">
Declare @pageStart int, @perPage int
SET @pageStart = #url.pageStart#
SET @perPage = #url.perPage#
      WITH myQuery
         AS (
                         SELECT
                        Row_number() OVER (ORDER BY UserID) as rownum,
                        Row_number() OVER (ORDER BY UserID DESC) as rownum2,
                        firstname, lastname
               
                   FROM sometable WITH (NOLOCK)
                        WHERE whatever ...
                   )
    SELECT firstname, lastname, rownum + rownum2 - 1 as numrec
    FROM   myQuery
    WHERE  rownum >= @pageStart AND rownum <= @pageStart + @perPage
</cfquery>
<cfset variables.numrec=x.numrec>
<cfif Not isNumeric(variables.numrec)>
      <cfset variables.numrec=0>
</cfif>
      
      Just plugin your table and columns to test
      and in your front end code just do previous and next links:
<cfoutput>
<table>
<tr><td>#variables.numrec# records</td></tr>
<cfif variables.numrec>
<tr>
      <td>
      <cfif url.pageStart gt 1>
      <a href="index.cfm?pageStart=#(url.pageStart - url.perPage)#">Previous</a>
      </cfif>
      &nbsp;
      </td>
      <td>
      <cfif variables.numrec gt url.pageStart + url.perPage>
      <a href="index.cfm?pageStart=#(url.pageStart + url.perPage)#">Next</a>
      </cfif>
      &nbsp;
      </td>
</tr>
</cfif>
<cfloop query="x">
<tr>
      <td>
      #x.firstname#
      </td>
      <td>
      #x.lastname#
      </td>
</tr>
</cfloop>
</table>
</cfoutput>

Author

Commented:
Hi dgrafx,
Thanks for getting back to me. Yes, I am using SQL Server 2005.  But could you explain what is happening in the Query :
<cfquery datasource="xxx" name="x">

It's a little over my head.  Are you doing a query of queries?  What is "myQuery" in this?  Could you give a little over view on what is going on there?  That might help me understand the code.

As part 2 to my original question, How do you keep track of the selected items from page to page?  Do I keep an array as a session variable.  When I redisplay a previous page, I need to indicate as checked the items that were previously checked by the user.  And update at the end of the process all checked items.

Thanks again for you help.

hefterr
"keep track of selected items"
I would update a database table via ajax

The query tag: <cfquery datasource="xxx" name="x">
xxx is your registered dsn name - in cfadministrator - your list of dsn's
yes it is a standard query Not a query of queries
myQuery is the "cte" within the query and does not exist outside the sql code.

does that help?

see below
===========================================================



ok - lets start a simple demo ...

================================================================================================================
create a table so we are on the same page:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_testtable](
      [UserID] [int] IDENTITY(1,1) NOT NULL,
      [firstname] [nvarchar](50) NULL,
      [lastname] [nvarchar](50) NULL,
      [checker] [int] NULL,
 CONSTRAINT [PK__testtable] PRIMARY KEY CLUSTERED
(
      [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[_testtable] ADD  CONSTRAINT [DF__testtable_checked]  DEFAULT ((0)) FOR [checker]
GO

================================================================================================================

give it some data

INSERT INTO _testtable
(firstname,lastname,checker)
values
('test','one',0)

INSERT INTO _testtable
(firstname,lastname,checker)
values
('test','two',1)

INSERT INTO _testtable
(firstname,lastname,checker)
values
('test','three',0)

INSERT INTO _testtable
(firstname,lastname,checker)
values
('test','four',0)

INSERT INTO _testtable
(firstname,lastname,checker)
values
('test','five',1)

================================================================================================================

this is your display page - a simple demo of what i posted before to help you learn ajax ...
don't forget to create testaction.cfm below!

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script>
$(document).ready(function() {
      $(".clicker").live("click", function() {      
      userid = $(this).attr('val');
      ck = $(this).attr('checked') ? 1 : 0;      
      $.ajax({
            type: "get",            
            url: "testaction.cfm?UserID="+userid+"&checker="+ck,                  
            datatype: "html"                  
                  });
      });
});
</script>

<cfquery datasource="yourDatasource" name="x">
SELECT UserID,firstname,lastname,checker
from _testtable
</cfquery>
<table>
      <cfloop query="x">
      <tr>
            <td>
            #x.firstname#
            </td>
            <td>
            #x.lastname#
            </td>
            <td>
            <input type="checkbox" name="checker" class="clicker" val="#x.UserID#"
                  <cfif x.checker>checked</cfif>>
            </td>
      </tr>
      </cfloop>
</table>

================================================================================================================

here is your testaction.cfm page:

<cfparam name="url.checker" default="0">
<cftry>
      <cfquery datasource="yourDatasource">
      UPDATE _testtable
      SET checker = #url.checker#
      WHERE UserID = #url.UserID#
      </cfquery>
      Success
<cfcatch>
Failure
</cfcatch></cftry>

================================================================================================================

now run your display page in your browser and after checking a box you can test to see that it did update the db by either refreshing the page or by looking directly at the db table values

Author

Commented:
Hi dgrafx,
Woooaaaa.

Could you just go back to your original answer and review the SQL you had in your
<cfquery datasource="xxx" name="x"> query.  I don't mean the tag itself, I meant the SQL within the tag (sorry, my bad).

I can't really use the Ajax case because
a) I don't understand it.
b) This would be a temp table for this session?  I just want to track what "values" were checked across pages when the "next" or Previous" is hit.  I would like this to submit the form and I would control the redisplay of the next/previous page.

I think I would need to keep either an array of keys as a session variable or a "temporary" type table as you are suggesting - as the results will be discarded if the process is not completed by a user during this session.

Sorry to bother you.  Your ideas a very good.  I just have not explained myself very well.

hefterr
yes - i get it - it's just a tutorial if you will ...
the ajax stuff i showed you is entirely self contained - you can just use it as is to update your table.

the sql within the tag itself - this is pagination on the server
much better than paging in the front end

if you want to check some boxes then submit a form then i guess you could do a structure stored in a session. on the action page just add values that are checked and remove those that aren't.
btw - my proposal was not a temp table but to use existing table - create a column that tracks if checked or not - this is the logical approach as you are already querying.
why bring something else into the mix?

i hope this clears things up a bit
i think you should try to set up the demo i proposed (remember its just a demo - not a temp table solution) and see how slick and how easy it is ...

later

Author

Commented:
Thanks for your help!
thanks for the points and i hope it all works out for you ...

Author

Commented:
Thanks for your time and expertise!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial