how to search value inside a set or array in mysql
Sometimes we have data in a field on our table like this “1,2,3,4,5,6,7”, and we want to search for example number 2. Beside doing searching inside php, we can do it in sql query. I found one function for retrieving position of a value inside a set : FIND_IN_SET(str,strlist).
So the query will look like this :
SELECT * FROM table_name WHERE FIND_IN_SET(2, field_name) > 2;
This query will return all recordset that have number 2 in field_name set.
Another way to searching a value inside a set, is by manipulating on how we save the set in field. So if we want to save a set of “1, 2, 3, 4, 5, 6, 7”, we will save it as “<1>,<2>,<3>,<4>,<5>,<6>,<7>” in database. And doing query using this method :
SELECT * FROM table_name WHERE field_name LIKE ‘%<2>%’;
but of course the first way is more faster. because first way we search value in array / set, but second way search value in string.
