php - Searching from comma separated value

Question

This is one of those 'oh my god' moments.

The previous programmer in our company has left behind a badly-coded application using PHP and MySQL.

One example is that he has stored options for each customer as a comma separated value in MySQL. The entire application is NOT OOP based and so there are repeated references and queries in almost every page of the 500+ pages of PHP. So it's not an easy job now to change the schema and data storage logics.

To adjust the system for a matter of six months, I am looking for a way to perform a search on those comma-separates values. Has someone got any idea about searching such CSV without much performance impact? I know it's not going to be the best, but at least I could push the application to go for another six months before the new application is ready.

Thank you for any help

Answer 1

You could use FIND_IN_SET to retrieve rows that match your criteria:

SELECT * FROM your_table WHERE FIND_IN_SET('value', field_with_comma_sep_values) > 0;

Basically, FIND_IN_SET returns the index of the found item. So this query finds all rows where it finds the matching word in the "set" of comma separated values.

Credit: I knew there was something like this, but this post is where I found the answer and the SELECT statement.