Hello Everyone:
Issue: P n of x with a Table and CFQUERY search Criteria
I don't really know where to start on this one. In both beginner Ben
Forta CF 5 and MX books there is a great example to output a table
displaying 10 rows and it provides you a next and previous button to
view the rest of the results, in the MX book its in chapter 21.
I want to use this code but I would like to make a dynamic query that
will be populated by a form from. When I use the code it only works with
a hard coded query how can I get it to work the way I want. My ideal
situation is using a framed page where the user will enter their
criteria in framed page A and click submit which will pass the form
variables to framed page B which will in turn show the results along
with the variables that are being passed to populate the table and still
show the searched criteria as the user click next and previous.
Help Help Help
Thanks in advance,
James Blaha
<!---
Filename: NextN5.cfm
Created by: Nate Weiss (NMW)
Purpose: Displays Next N record-navigation interface
Please Note: Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks
--->
<!--- Retrieve expense records from database --->
<CFQUERY NAME="GetExp" DATASOURCE="#REQUEST.DataSource#">
SELECT
f.FilmID, f.MovieTitle,
e.Description, e.ExpenseAmount, e.ExpenseDate
FROM
Expenses e INNER JOIN Films f
ON e.FilmID = f.FilmID
ORDER BY
e.ExpenseDate DESC
</CFQUERY>
<!--- Number of rows to display per Next/Back page --->
<CFSET RowsPerPage = 10>
<!--- What row to start at? Assume first by default --->
<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">
<!--- Allow for Show All parameter in the URL --->
<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">
<!--- We know the total number of rows from query --->
<CFSET TotalRows = GetExp.RecordCount>
<!--- Show all on page if ShowAll passed in URL --->
<CFIF URL.ShowAll>
<CFSET RowsPerPage = TotalRows>
</CFIF>
<!--- Last row is 10 rows past the starting row, or --->
<!--- total number of query rows, whichever is less --->
<CFSET EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Next button goes to 1 past current end row --->
<CFSET StartRowNext = EndRow + 1>
<!--- Back button goes back N rows from start row --->
<CFSET StartRowBack = URL.StartRow - RowsPerPage>
<!--- Page Title --->
<HTML>
<HEAD><TITLE>Expense Browser</TITLE></HEAD>
<BODY>
<CFOUTPUT><H2>#REQUEST.CompanyName# Expense Report</H2></CFOUTPUT>
<!--- simple style sheet for formatting --->
<STYLE>
TH {font-family:sans-serif;font-size:smaller;
background:navy;color:white}
TD {font-family:sans-serif;font-size:smaller}
TD.DataA {background:silver;color:black}
TD.DataB {background:lightgrey;color:black}
</STYLE>
<TABLE WIDTH="600" BORDER="0" CELLSPACING="0" CELLPADDING="1">
<!--- Row at top of table, above column headers --->
<TR>
<TD WIDTH="500" COLSPAN="3">
<!--- Message about which rows are being displayed --->
<CFOUTPUT>
Displaying <B>#URL.StartRow#</B> to <B>#EndRow#</B>
of <B>#TotalRows#</B> Records<BR>
</CFOUTPUT>
</TD>
<TD WIDTH="100" ALIGN="right">
<CFIF NOT URL.ShowAll>
<!--- Provide Next/Back links --->
<CFINCLUDE TEMPLATE="NextNIncludeBackNext.cfm">
</CFIF>
</TD>
</TR>
<!--- Row for column headers --->
<TR>
<TH WIDTH="100">Date</TH>
<TH WIDTH="250">Film</TH>
<TH WIDTH="150">Expense</TH>
<TH WIDTH="100">Amount</TH>
</TR>
<!--- For each query row that should be shown now --->
<CFLOOP QUERY="GetExp" StartRow="#URL.StartRow#" ENDROW="#EndRow#">
<!--- Use class "DataA" or "DataB" for alternate rows --->
<CFSET Class = IIF(GetExp.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'")>
<!--- Actual data display --->
<CFOUTPUT>
<TR VALIGN="baseline">
<TD CLASS="#Class#" WIDTH="100">#LSDateFormat(ExpenseDate)#</TD>
<TD CLASS="#Class#" WIDTH="250">#MovieTitle#</TD>
<TD CLASS="#Class#" WIDTH="150"><I>#Description#</I></TD>
<TD CLASS="#Class#" WIDTH="100">#LSCurrencyFormat(ExpenseAmount)#</TD>
</TR>
</CFOUTPUT>
<!--- If showing all records, flush the page buffer after every 5th row
--->
<CFIF URL.ShowAll>
<CFIF GetExp.CurrentRow MOD 5 EQ 0>
<!--- End the current table --->
</TABLE>
<!--- Flush the page buffer --->
<CFFLUSH>
<!--- Start a new table --->
<TABLE WIDTH="600" BORDER="0" CELLSPACING="0" CELLPADDING="1">
<!--- Simulate a time-intensive process --->
<CFSET InitialTime = Now()>
<CFLOOP CONDITION="DateDiff('s', InitialTime, Now()) LT 1"></CFLOOP>
</CFIF>
</CFIF>
</CFLOOP>
<!--- Row at bottom of table, after rows of data --->
<TR>
<TD WIDTH="500" COLSPAN="3">
<CFIF NOT URL.ShowAll AND TotalRows GT RowsPerPage>
<!--- Shortcut links for "Pages" of search results --->
Page <CFINCLUDE TEMPLATE="NextNIncludePageLinks.cfm">
<!--- Show All Link --->
<CFOUTPUT>
<A HREF="#CGI.SCRIPT_NAME#?ShowAll=Yes">Show All</A>
</CFOUTPUT>
</CFIF>
</TD>
<TD WIDTH="100" ALIGN="right">
<CFIF NOT URL.ShowAll>
<!--- Provide Next/Back links --->
<CFINCLUDE TEMPLATE="NextNIncludeBackNext.cfm">
</CFIF>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
