|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Issue: P n of x with a Table and CFQUERY search Criteria
Hello Everyone:James Blaha 12/23/02 02:20 P I don't understand what problem you're having with using dynamic queries andBen Doom 12/23/02 02:43 P Ben,James Blaha 12/23/02 03:03 P I'm still not sure that frames would be an ideal solution, unless you canBen Doom 12/23/02 04:02 P Ben,James Blaha 12/23/02 04:30 P This is an example of the type of code that Im trying to placeJames Blaha 12/23/02 03:11 P 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> I don't understand what problem you're having with using dynamic queries and next/prev links. I use forward and back links all the time when dealing with any query which returns a result set to large to be easily displayed on a single page. What issues are you having? When you talk about framed page A and framed page B, are you referring to different frames? If it were I, I'd simply create a table with an area for modifying the query and an area for display. CFparam in the default query parameters before displaying the form for modifying them, and set the display value of the form elements to the value of the existing parameter. That is, do something like: <cfparam name="name" default="bob"> <cfoutput> <form> <input type="text" name="name" value="#name#"></form> </cfoutput> That way, whatever is passed via the form will "stick". You can do similar things for checkboxes, selects, textareas, etc. This will simplify your codebase by not requiring you to jump back and forth between frames, probably via JavaScript. Anyway, HTH. --Ben Doom Programmer & General Lackey Moonbow Software : -----Original Message----- : : Sent: Monday, December 23, 2002 2:14 PM : To: CF-Talk : Subject: Issue: P n of x with a Table and CFQUERY search Criteria : : : 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 <snip /> Ben, Sorry for the confusion I hope I can clarify this better. In frame “A” there is a form with lets say 2 fields FirstName & LastName enter the information click submit and the persons record shows up on the right side “B”. At the top of the page for “B” it says: 1. FirstName xxxx 2. LastName xxx Web Page has 2 pages ******* *A | B * ******* Then below the information the table output is displayed. Let’s say the user doesn’t select anything and they get all the people in the database. When they click next the query should show the next 10 people and still show the criteria the user is searching by. 3. FirstName ALL 4. LastName ALL Does this help? I can get it to work this way at all. -JB Ben Doom wrote: ----- Excess quoted text cut - see Original Post for more ----- I'm still not sure that frames would be an ideal solution, unless you can specify a target id for a form (I don't remember that you can, but that doesn't mean you can't :-) but I'll let you design it however you want. I suspect (just from a cursory glance) that the problem is in your processing of input data and the prev/next links. It looks like you are only checking for form scoped data, where the prev/next links would probably propagate that data via url scope. Just do something like: <cfif isdefined("form.firstname")> <cfset variables.firstname = trim(form.firstname)> <cfelseif isdefined("url.firstname")> <cfset variables.firstname = trim(url.firstname)> <cfelse> <cfset variables.firstname = ""> </cfif> Or whatever you want the default to be. Or you could leave it undefined and check for its existence in your query. Or whatever. The point is that you have to be pulling the data from the URL scope for the links to work. --Ben Doom Programmer & General Lackey Moonbow Software : -----Original Message----- : : Sent: Monday, December 23, 2002 3:00 PM : To: CF-Talk : Subject: Re: Issue: P n of x with a Table and CFQUERY search Criteria : : : Ben, : : Sorry for the confusion I hope I can clarify this better. In frame “A” : there is a form with lets say 2 fields FirstName & LastName enter the : information click submit and the persons record shows up on the right : side “B”. At the top of the page for “B” it says: : : 1. FirstName xxxx : 2. LastName xxx : : Web Page has 2 pages : ******* : *A | B * : ******* : : : Then below the information the table output is displayed. Let’s say the : user doesn’t select anything and they get all the people in the : database. When they click next the query should show the next 10 people : and still show the criteria the user is searching by. : : 3. FirstName ALL : 4. LastName ALL : : Does this help? I can get it to work this way at all. : : -JB : : Ben Doom wrote: : : >I don't understand what problem you're having with using dynamic : queries and : >next/prev links. I use forward and back links all the time when dealing : >with any query which returns a result set to large to be easily : displayed on : >a single page. What issues are you having? : > : >When you talk about framed page A and framed page B, are you referring to : >different frames? If it were I, I'd simply create a table with : an area for : >modifying the query and an area for display. CFparam in the : default query : >parameters before displaying the form for modifying them, and set the : >display value of the form elements to the value of the existing : parameter. : >That is, do something like: : ><cfparam name="name" default="bob"> : ><cfoutput> : ><form> : ><input type="text" name="name" value="#name#">: ></form> : ></cfoutput> : >That way, whatever is passed via the form will "stick". You can : do similar : >things for checkboxes, selects, textareas, etc. This will simplify your : >codebase by not requiring you to jump back and forth between frames, : >probably via JavaScript. : > : >Anyway, HTH. : > : > : > : > --Ben Doom : > Programmer & General Lackey : > Moonbow Software : > : >: -----Original Message----- : >: : >: Sent: Monday, December 23, 2002 2:14 PM : >: To: CF-Talk : >: Subject: Issue: P n of x with a Table and CFQUERY search Criteria : >: : >: : >: 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 : > : ><snip /> : > : > : Ben, Thank you for your time! I need to do some thinking on this one. Have a great holiday, -JB Ben Doom wrote: ----- Excess quoted text cut - see Original Post for more ----- This is an example of the type of code that Im trying to place functionality on that will next and previous results. This code has to be updated to allow this. <!--- Req No Field: First text box ---> <CFSET "FORM.REQNO" = TRIM(#FORM.ReqNo#)> <CFIF #FORM.REQNO# EQ ""> <CFSET "Form.REQNO" = "0"> <CFELSE> </CFIF> <!--- FIRSTNAME Field: Secound text box ---> <CFSET "FORM.FIRSTNAME" = TRIM(#FORM.FIRSTNAME#)> <CFIF #FORM.FIRSTNAME# EQ ""> <CFSET "Form.FIRSTNAME" = "0"> <CFELSE> </CFIF> <!--- LASTNAME Field: Third text box ---> <CFSET "FORM.LASTNAME" = TRIM(#FORM.LASTNAME#)> <CFIF #FORM.LASTNAME# EQ ""> <CFSET "Form.LASTNAME" = "0"> <CFELSE> </CFIF> <!--- Applied x days ago Field: Fourth drop down box ---> <CFSET #back_date#= #DateAdd('D', #APPLIED_DATE#, now())#> <CFSET #back_date_listed2# = #DateFormat(back_date, "m/d/yyyy")#> <!--- This gives todays date to pass to the SQL Statment below ---> <CFSET #now_date#= #now()#><CFSET #now_date2# = #DateFormat(now_date, "m/d/yyyy")#> <!--- Desired_Position: Multiple select box, fifth drop down box. ---> <CFSET "FORM.Desired_Position" = TRIM(#FORM.Desired_Position#)> <CFIF #FORM.Desired_Position# EQ "All Area Applicants"> <CFSET "Desired_Position2" = #Form.Desired_Position#> <CFSET "Form.Desired_Position" = "0"> <CFELSE> <CFSET "Desired_Position2" = #Form.Desired_Position#> </CFIF> <FONT color="black"> Your Selected Search Criteria:<BR> <!--- _1_ ---> <CFIF #form.REQNO# EQ "0"> <CFOUTPUT> 1. REQ No. : <strong>All REQ Numbers Selected</strong><BR> </CFOUTPUT> <CFELSE> <CFOUTPUT> 1. REQ No. : <strong>#REQNO#</strong><BR> </CFOUTPUT> </CFIF> <!--- _2_ ---> <CFIF #form.FIRSTNAME# EQ "0"> <CFOUTPUT> 2. First Name: <strong>First name is not specified for search!</strong><BR> </CFOUTPUT> <CFELSE> <CFOUTPUT> 2. First Name: <strong>#FIRSTNAME#</strong><BR> </CFOUTPUT> </CFIF> <!--- _3_ ---> <CFIF #form.LASTNAME# EQ "0"> <CFOUTPUT> 3. Last Name: <strong>Last name is not specified for search!</strong><BR> </CFOUTPUT> <CFELSE> <CFOUTPUT> 3. Last Name: <strong>#LASTNAME#</strong><BR> </CFOUTPUT> </CFIF> <!--- _4_ ---> <CFIF #form.APPLIED_DATE# EQ "0"> <CFOUTPUT> 4. <strong>Days back range has not been selected for search!</strong><BR> </CFOUTPUT> <CFELSE> <CFOUTPUT> <CFSET #back_date_listed#= #DateAdd('D', #APPLIED_DATE#, now())#> 4. Applicants who have posted since: <strong>#DateFormat(back_date_listed, "ddd, mmmm dd, yyyy")#</strong><br> </CFOUTPUT> </CFIF> <CFIF isdefined("form.Desired_Position")> <CFELSE> <CFSET "FORM.Desired_Position" = ""> </CFIF> <!--- _5_ ---> <CFIF #form.Desired_Position# EQ ""> <CFOUTPUT> 5. <strong>No employee location(s) have been selected for search!</strong><BR> </CFOUTPUT> <CFELSE> <CFOUTPUT> 5. You have selected employees wanting to be employed in the following location(s). : <strong>#Desired_Position2#</strong> </CFOUTPUT> </CFIF> </FONT> <!--- ________________________________________Part 3___________________________________________ ---> <!--- Begin Staff Rights ---> <CFIF #form.UserRights# EQ "Staff"> <CFQUERY name="JobApplicants" datasource="xxxt"> SELECT * FROM HRWebAgent.tApply WHERE ReqNO > 0 <CFIF #form.ReqNo# NEQ "0"> AND (ReqNo='#ReqNo#') </CFIF> <CFIF #form.FIRSTNAME# NEQ "0"> AND (FIRSTNAME LIKE '#FIRSTNAME#%') </CFIF> <CFIF #form.LASTNAME# NEQ "0"> AND (LASTNAME LIKE '#LASTNAME#%') </CFIF> <CFIF #form.APPLIED_DATE# NEQ "0"> <!--- Dates go in order of old:recent i.e. BETWEEN '11/4/2000' AND '6/18/2001' ---> AND (APPLIED_DATE BETWEEN '#back_date_listed2#' AND '#now_date2#') </CFIF> <CFIF #form.Desired_Position# NEQ "0"> <!--- Desired_Position IN ('Briarcliff','New York') ---> AND (Desired_Position IN (#ListQualify(Form.Desired_Position,"'")#)) </CFIF> ORDER BY APPLIED_DATE DESC, Job_Title </CFQUERY> </cfif> <HTML> <HEAD> <TITLE>Results Bottom</TITLE> <META http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="javascript"> // Window Options, top and left (IE) uses screenY and screenX for (NS) // This allows you to position the window location on the screen in pixels. function BoogieOn(aURL) { NextWindow=window.open("", "NextWindow", 'width=700,height=500,left=1,top=100,screenX=1,screenY=100,toolbar=no,location=no,menubar=no,scrollbars=yes,resizable=yes,') NextWindow.document.location=aURL; } </script> </HEAD> <BODY topmargin="0" leftmargin="0" bgcolor="#000080" background="../../background/background.gif"> <FORM action="AppInfoRpt.cfm" method="post"> <TABLE border="1" width="650" align="center" cellpadding="5"> <TR bgcolor="#000066" align="left" valign="top"> <TD colspan="6"> <CENTER> <FONT color="#FFFFFF" face="Arial Black" size="3">AVAILABLE APPLICANTS</FONT> </CENTER> </TD> </TR> <TR bgcolor="#000066" align="left" valign="top"> <TD> <div align="center"><b><font color="#FFFFFF">REQ. NO.</font></b> </div> </TD> <td width="9%"> <div align="center"><b><font color="#FFFFFF">POSITION<br> POSTED</font></b></div> </td> <TD> <div align="center"><b><font color="#FFFFFF">APPLIED<br> DATE</font></b> </div> </TD> <TD> <div align="center"><B><FONT color="#FFFFFF"> DESIRED POSITION</FONT></B> </div> </TD> <TD> <div align="center"><font color="#FFFFFF"><b>EMP.<br> STATUS</b></font></div> </TD> <TD> <div align="center"><font color="#FFFFFF"><b>NAME<br> FIRST/LAST</b></font></div> </TD> </TR> <CFOUTPUT query="JobApplicants"> <TR align="left" valign="top" <CFSET Today = #CreateODBCDateTime(NOW())#> <CFIF ApplicationType Contains "Internal"> <cfif POSTING NEQ ""> <cfif (DateDiff('d', POSTING, Today)) LTE 5> bgcolor=808080 <cfelse> <CFIF ApplicationType Contains "Internal"> BGCOLOR=CCCCCC <cfelse> </CFIF></CFIF></CFIF></CFIF> > <TD> <div align="center">#ReqNo#</div> </TD> <td width="9%"> <div align="center">#DateFormat((POSTING), "M/D/YY")# </div> </td> <TD> <div align="center">#DateFormat((Applied_Date), "M/D/YY")# </div> </TD> <TD> #Job_Title#<br> <!--- Testing Code for Color Coding Rows ---> <CFIF ApplicationType Contains "Internal"> <cfif POSTING NEQ ""> <cfif (DateDiff('d', POSTING, Today)) LTE 5> <font color="##FFFFFF"><b>*Internal applicant who posted within the first five days of position being posted!</b> </font> <cfelse> <CFIF ApplicationType Contains "Internal"> <font color="##FFFFFF"><b>*Internal Applicant!</b> </font> </CFIF> <CFIF ApplicationType Contains "Internal"> </CFIF> <CFIF ApplicationType Contains "Internal"> <cfelse> </CFIF> </CFIF> </CFIF> </CFIF> </TD> <TD> <div align="center">#Status#</div> </TD> <td> <div align="center"> <cfset Identification = CFusion_encrypt(IDENTITY,"xxx")> <a href="##" onclick="javascript:BoogieOn('<CFIF ApplicationType Contains "Internal">AppIntInfoRpt.cfm</CFIF><CFIF ApplicationType Contains "External">AppInfoRpt.cfm</CFIF>?BlueBird=#jsStringFormat(urlencodedformat(Identification))#');"> <b><font size="3">#FirstName#<br>#LastName#</font></b></a> </div> </td> </TR> </CFOUTPUT> </TABLE> </FORM> </BODY> </HTML> Ben Doom wrote: ----- Excess quoted text cut - see Original Post for more -----
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
