karenn1 Posted September 9, 2011 Share Posted September 9, 2011 Hey everyone, I have a page where a user can select one or more project types from a multiple select box. When they click on Search, I would like to query a field in the database and return the values that match the search. This is my code: SELECT * FROM projects WHERE FIND_IN_SET('Agriculture,Environment', project_type) If I put in Agriculture on its own, it returns rows. Likewise with Environment so I know there's data for both. but it's not working together. Here's my data structure: id name project_type 1 Project 1 Agriculture 2 Project 2 Agriculture;Environment 3 Project 3 Environment This is how I inherited this database. The project type data is delimited with a semi-colon. Can someone please help? Thanks in advance! Karen Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/ Share on other sites More sharing options...
Muddy_Funster Posted September 9, 2011 Share Posted September 9, 2011 I have to ask, if you inherited the DB, why can't you now change it? It's becoming evident that the original design had no consideration for the current requirements. Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1267269 Share on other sites More sharing options...
fenway Posted September 9, 2011 Share Posted September 9, 2011 Two problems. One, FIND_IN_SET() can only find one value at a time. Second, it needs to be comma separated, so if you're really going to hack it to make it work, you'll need to use REPLACE() to swap colons for commas. Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1267320 Share on other sites More sharing options...
ManiacDan Posted September 9, 2011 Share Posted September 9, 2011 Fenway's comment leads directly to a repeat of Funster's question: Why aren't you redesigning this? How much code could possibly be touching this table? Breaking this list out into its own table properly will give you a good database design that can be used for real tasks. -Dan Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1267330 Share on other sites More sharing options...
karenn1 Posted September 12, 2011 Author Share Posted September 12, 2011 Hi guys, Thanks for the reply. To answer your question Dan, I quoted a certain amount of time to do a few specific things on this site. Unfortunately, I didn't quote extra to redesign the database and it will take me some time to do. I won't get paid for that time. I understand what you are saying though but in this case, I just can't do it that way. I need to make it work in it's current format. I'll try the REPLACE() and use commas instead and see if that works. Karen Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1268226 Share on other sites More sharing options...
karenn1 Posted September 12, 2011 Author Share Posted September 12, 2011 Ok, so I've replaced the semi-colons with commas. @fenway, how can I hack it to make it work? Karen Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1268229 Share on other sites More sharing options...
karenn1 Posted September 12, 2011 Author Share Posted September 12, 2011 I actually found something that works well: SELECT * FROM projects WHERE MATCH (project_type) AGAINST ('Agriculture, Environment') This finds all the rows where the project type is Agriculture and/or Environment. Are there any draw backs in using this? Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1268245 Share on other sites More sharing options...
fenway Posted September 12, 2011 Share Posted September 12, 2011 That's over kill -- and isn't really doing what it says it's doing. Oh -- just run find in set twice. Link to comment https://forums.phpfreaks.com/topic/246770-find-in-set/#findComment-1268342 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.