Server Paging in Coldfusion 8

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

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

Thanks for your help!

thanks for the points and i hope it all works out for you ...

Thanks for your time and expertise!