Filtering query results
In the application that I'm working on, I have a need to filter the results of a query that get displayed on the screen, changing the selectable options for the user. I have a default query that populates a SELECT list, and under that list I have three hyperlinks that will allow a user to click on them to execute a different query to show different results in the list for them to choose from. I'm still pretty new to ColdFusion, and I'm not really sure what's the best way to implement this functionality, and was hoping for some ideas from anyone. The query that gets executed is basically the same, only the WHERE clause changes. I've tried just dynamically changing the WHERE clause using cfif checking based on a URL parameter, but didn't have much luck with that. Then I tried actually creating the three separate queries, and just changing the query parameter of my loop for the list values, using cfswitch checking to set the query parameter based on a URL parameter. That didn't give me much luck either. I'm just not sure how to best go about doing this. Does anyone happen to know of any articles online, or tutorials that might explain something close to how to do this? How to I create a .cfm page to accept a parameter by default to tell it which query to execute? Any help or thoughts would be greatly appreciated. Thanks. ::YEX:: /* || Robert D. Yexley || Oracle Programmer/Analyst || Easylink Services Corporation || Professional Services || Contractor - Wright Research Site MIS || Det-1 AFRL/WSI Bldg. 45 Rm. 062 || (937) 255-1984 || [EMAIL PROTECTED] || ))) */ Robert D. Yexley (E-mail).vcf __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Filtering query results
I have done this a few times with URL parameters and cfif's in the where clause and had no problems...That is about the easiest way to go about it I think. Jim Vosika http://tinyclick.com Free URL Shortening! -Original Message- From: Yexley Robert D Contr Det 1 AFRL/WSI [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 1:35 PM To: CF-Talk Subject: Filtering query results In the application that I'm working on, I have a need to filter the results of a query that get displayed on the screen, changing the selectable options for the user. I have a default query that populates a SELECT list, and under that list I have three hyperlinks that will allow a user to click on them to execute a different query to show different results in the list for them to choose from. I'm still pretty new to ColdFusion, and I'm not really sure what's the best way to implement this functionality, and was hoping for some ideas from anyone. The query that gets executed is basically the same, only the WHERE clause changes. I've tried just dynamically changing the WHERE clause using cfif checking based on a URL parameter, but didn't have much luck with that. Then I tried actually creating the three separate queries, and just changing the query parameter of my loop for the list values, using cfswitch checking to set the query parameter based on a URL parameter. That didn't give me much luck either. I'm just not sure how to best go about doing this. Does anyone happen to know of any articles online, or tutorials that might explain something close to how to do this? How to I create a .cfm page to accept a parameter by default to tell it which query to execute? Any help or thoughts would be greatly appreciated. Thanks. ::YEX:: /* || Robert D. Yexley || Oracle Programmer/Analyst || Easylink Services Corporation || Professional Services || Contractor - Wright Research Site MIS || Det-1 AFRL/WSI Bldg. 45 Rm. 062 || (937) 255-1984 || [EMAIL PROTECTED] || ))) */ Robert D. Yexley (E-mail).vcf __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Filtering Query Results
Hi all, I have a query, GetProductTypes: cfquery name=GetProductTypes datasource=#request.dsn# dbtype=ODBC username=#username# password=#password# SELECT DISTINCT producttypes.Code, producttypes.ID, producttypes.SortOrder, products.ProductCategoryID, products.ProductTypeID, products.OrderOnlineStatus FROM producttypes, products WHERE products.ProductCategoryID = 28 AND producttypes.ID = products.ProductTypeID AND products.OrderOnlineStatus = 1 ORDER BY producttypes.SortOrder /cfquery that returns a list of product types within category 28; say ASO (black) ASO (white) ASO Speed Lacer Air-Gel The products returned from the query are correct. I need to look at this list of returned products and display only those that have ASO in the Code field. I don't want to display the Air-Gel. Is there a way to filter the query results before displaying them? I tried this: cfif GetProductTypes.Code CONTAINS ASO cfoutput query=GetProductTypes #Code#br /cfoutput cfelse error message /cfif ..but it always returns false, even when GetProductTypes.Code contains ASO. I bet I am missing something really simple. :( Any help would be greatly appreciated. I am using CF 4.5 on a Win NT box. Thank you, Mike Michael Wilson - 961 Media, Inc [EMAIL PROTECTED] http://www.961media.com http://www.961media.com/ Phone 704.736.9009 Mobile 704.281.7616 Don't let anyone ever make you feel like you don't deserve what you want. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Filtering Query Results
why not do it at the DB level? cfquery name=GetProductTypes datasource=#request.dsn# dbtype=ODBC username=#username# password=#password# SELECT DISTINCT producttypes.Code, producttypes.ID, producttypes.SortOrder, products.ProductCategoryID, products.ProductTypeID, products.OrderOnlineStatus FROM producttypes, products WHERE products.ProductCategoryID = 28 AND producttypes.ID = products.ProductTypeID AND products.OrderOnlineStatus = 1 AND producttypes.code LIKE '%ASO%' ORDER BY producttypes.SortOrder /cfquery as a side note, the example you have cfif GetProductTypes.Code CONTAINS ASO cfoutput query=GetProductTypes #Code#br /cfoutput cfelse error message /cfif is not working because the CFIF is outside the cfoutput. that means it's looking at the value in the first row returned, not each value. if you must do it that way for some reason, move the CFIF inside the OUTPUTs. christopher olive, cto, vp of web development cresco technologies, inc 410.931.4092 http://www.crescotech.com -Original Message- From: Michael Wilson [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 5:22 PM To: CF-Talk Subject: Filtering Query Results Hi all, I have a query, GetProductTypes: cfquery name=GetProductTypes datasource=#request.dsn# dbtype=ODBC username=#username# password=#password# SELECT DISTINCT producttypes.Code, producttypes.ID, producttypes.SortOrder, products.ProductCategoryID, products.ProductTypeID, products.OrderOnlineStatus FROM producttypes, products WHERE products.ProductCategoryID = 28 AND producttypes.ID = products.ProductTypeID AND products.OrderOnlineStatus = 1 ORDER BY producttypes.SortOrder /cfquery that returns a list of product types within category 28; say ASO (black) ASO (white) ASO Speed Lacer Air-Gel The products returned from the query are correct. I need to look at this list of returned products and display only those that have ASO in the Code field. I don't want to display the Air-Gel. Is there a way to filter the query results before displaying them? I tried this: cfif GetProductTypes.Code CONTAINS ASO cfoutput query=GetProductTypes #Code#br /cfoutput cfelse error message /cfif .but it always returns false, even when GetProductTypes.Code contains ASO. I bet I am missing something really simple. :( Any help would be greatly appreciated. I am using CF 4.5 on a Win NT box. Thank you, Mike Michael Wilson - 961 Media, Inc=== [EMAIL PROTECTED] http://www.961media.com http://www.961media.com/ === Phone 704.736.9009 Mobile 704.281.7616=== Don't let anyone ever make you feel like you don't deserve what you want. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Filtering Query Results
Did you try: trim(GetProductTypes.Code) I sometimes run into this problem where the db will pad the field with spaces and when I run a query, the spaces are returned and my if logic craps out. Almost always, a trim will do the trick. Mark -Original Message- From: Michael Wilson [mailto:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 5:22 PM To: CF-Talk Subject: Filtering Query Results Hi all, I have a query, GetProductTypes: cfquery name=GetProductTypes datasource=#request.dsn# dbtype=ODBC username=#username# password=#password# SELECT DISTINCT producttypes.Code, producttypes.ID, producttypes.SortOrder, products.ProductCategoryID, products.ProductTypeID, products.OrderOnlineStatus FROM producttypes, products WHERE products.ProductCategoryID = 28 AND producttypes.ID = products.ProductTypeID AND products.OrderOnlineStatus = 1 ORDER BY producttypes.SortOrder /cfquery that returns a list of product types within category 28; say ASO (black) ASO (white) ASO Speed Lacer Air-Gel The products returned from the query are correct. I need to look at this list of returned products and display only those that have ASO in the Code field. I don't want to display the Air-Gel. Is there a way to filter the query results before displaying them? I tried this: cfif GetProductTypes.Code CONTAINS ASO cfoutput query=GetProductTypes #Code#br /cfoutput cfelse error message /cfif .but it always returns false, even when GetProductTypes.Code contains ASO. I bet I am missing something really simple. :( Any help would be greatly appreciated. I am using CF 4.5 on a Win NT box. Thank you, Mike Michael Wilson - 961 Media, Inc=== [EMAIL PROTECTED] http://www.961media.com http://www.961media.com/ === Phone 704.736.9009 Mobile 704.281.7616=== Don't let anyone ever make you feel like you don't deserve what you want. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Filtering Query Results
Doh! See... I knew it'd be something simple. Thanks for the assist. Thanks, Mike -Original Message- why not do it at the DB level? AND producttypes.code LIKE '%ASO%' __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
