Find in set

[karenn1]

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

[Muddy_Funster]

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.

[fenway]

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.

[ManiacDan]

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

[karenn1]

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

[karenn1]

Ok, so I've replaced the semi-colons with commas. @fenway, how can I hack it to make it work?

 

 

Karen

[karenn1]

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?

[fenway]

That's over kill -- and isn't really doing what it says it's doing.

 

Oh -- just run find in set twice.