like match from a list
Yet another driving me crazy query problem. I'm showing a grid of color blocks. When the user clicks on one of them, it set a variable to look for in the database: cfset colorLIST = 'Teal', 'Turquoise', 'Mediterranean', 'Blue-Green', 'blue-green' So, here's the query cfquery name=srch datasource=phoenixart SELECT * FROM products WHERE COLOR IN (#PreserveSingleQuotes(colorLIST)#) /cfquery But, here's the problem. Database Color Field values for product 1: blue-green, purple, gold Database Color Field values for product 2: blue-green Only product 2 comes up, because the search wants to have an exact match to the entire field, right? So, how do I get like match from the list? -- --- Les Mizzell ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:20 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: like match from a list
The problem is the data structure. You should not store lists of values in a field ;-) 1) break colors out into a seperate table (color_ID and color_name fields) 2) create a table that will hold product_ID and color_ID (this matches colors to a product) 3) delete your field with lists of colors and make sure there is one record for each color/product combo in the table mentioned in #2 above Alter your query to use a table joins between product/color/product_color tables HTH Cheers Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202225 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: like match from a list
The answer is not in your SQL or your CFML, it is in your database schema. If you can have multiple values for a column that column needs to be a separate table, that's what joins are for, and that will make your SQL a whole lot simpler. --- Ben -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 2:18 PM To: CF-Talk Subject: like match from a list Yet another driving me crazy query problem. I'm showing a grid of color blocks. When the user clicks on one of them, it set a variable to look for in the database: cfset colorLIST = 'Teal', 'Turquoise', 'Mediterranean', 'Blue-Green', 'blue-green' So, here's the query cfquery name=srch datasource=phoenixart SELECT * FROM products WHERE COLOR IN (#PreserveSingleQuotes(colorLIST)#) /cfquery But, here's the problem. Database Color Field values for product 1: blue-green, purple, gold Database Color Field values for product 2: blue-green Only product 2 comes up, because the search wants to have an exact match to the entire field, right? So, how do I get like match from the list? -- --- Les Mizzell ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202225 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: like match from a list
Bryan Stevenson wrote: The problem is the data structure. You should not store lists of values in a field ;-) Pre-existing database - I've got to deal with what's there, unfortunitely. -- --- Les Mizzell ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202230 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: like match from a list
If you are using SQL Server, there is a function called PatIndex which might help you. I had a similar situation in the past and was able to solve it using the above mentioned function. On Apr 11, 2005 2:43 PM, Les Mizzell [EMAIL PROTECTED] wrote: Bryan Stevenson wrote: The problem is the data structure. You should not store lists of values in a field ;-) Pre-existing database - I've got to deal with what's there, unfortunitely. -- --- Les Mizzell ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202247 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: like match from a list
Pre-existing database - I've got to deal with what's there, unfortunitely. Well first I'd tell the client that it should really be fixed...because it will cost more in the long run for maintenanceand will continue to effect performace until fixed. When they tell you where to go and how to get there, then you can try this: ;-) 1) create a new query cfset MyQuery = QueryNew() 2) loop over the color list that you had used in your IN clause 3) run a query for each color seperately (one for each pass of the loop) 4) add the records returned from each query to MyQuery 5) run a query of a query on MyQuery so you can re-order the results It's clumsy and inefficient, but if that's what your client wants...that's what they can have ;-) Cheers Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202279 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
