Server Paging in Coldfusion 8
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
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
Do more with
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#
<cfif variables.numrec>
<tr>
<td>
<cfif url.pageStart gt 1>
<a href="index.cfm?pageStart=
</cfif>
</td>
<td>
<cfif variables.numrec gt url.pageStart + url.perPage>
<a href="index.cfm?pageStart=
</cfif>
</td>
</tr>
</cfif>
<cfloop query="x">
<tr>
<td>
#x.firstname#
</td>
<td>
#x.lastname#
</td>
</tr>
</cfloop>
</table>
</cfoutput>
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#
<cfif variables.numrec>
<tr>
<td>
<cfif url.pageStart gt 1>
<a href="index.cfm?pageStart=
</cfif>
</td>
<td>
<cfif variables.numrec gt url.pageStart + url.perPage>
<a href="index.cfm?pageStart=
</cfif>
</td>
</tr>
</cfif>
<cfloop query="x">
<tr>
<td>
#x.firstname#
</td>
<td>
#x.lastname#
</td>
</tr>
</cfloop>
</table>
</cfoutput>
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
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,checke
values
('test','one',0)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','two',1)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','three',0)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','four',0)
INSERT INTO _testtable
(firstname,lastname,checke
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"
userid = $(this).attr('val');
ck = $(this).attr('checked') ? 1 : 0;
$.ajax({
type: "get",
url: "testaction.cfm?UserID="+u
datatype: "html"
});
});
});
</script>
<cfquery datasource="yourDatasource
SELECT UserID,firstname,lastname,
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
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,checke
values
('test','one',0)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','two',1)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','three',0)
INSERT INTO _testtable
(firstname,lastname,checke
values
('test','four',0)
INSERT INTO _testtable
(firstname,lastname,checke
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"
userid = $(this).attr('val');
ck = $(this).attr('checked') ? 1 : 0;
$.ajax({
type: "get",
url: "testaction.cfm?UserID="+u
datatype: "html"
});
});
});
</script>
<cfquery datasource="yourDatasource
SELECT UserID,firstname,lastname,
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial