Free Android CakePHP GMaps Articles by Bali Web Design

July 9, 2008

how to search value inside a set or array in mysql

Filed under: mysql,query tips — Tags: , — admin @ 10:01 am

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.