Free Android CakePHP GMaps Articles by Bali Web Design

July 29, 2009

Find a data in mysql Array field

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

I want to show you a situation that probably happen to you when build a mysql query. The situation can be vary but basically same condition. For example we have places table to store all beautiful places around the world. And a categories table to classify our places data. The rule is a place can be put on one or some categories. To achieve this function we just add a field into places table that save category id list.

So places table will have id, name, categories fields. and categories table have id, name. We want to save category list into categories field on places table. The data will look like this ‘2,3,4,5,6,7,8’. A question will come to your mind about how to grab or find a matching id inside this categories, for example we want to show all place with category id 5.

We cannot use LIKE in this example because it cannot match perfectly on many digit number. So i found a solution that easy to do. The query to find all data inside mysql Array field is

SELECT * FROM places WHERE FIND_IN_SET(5, categories)

Another solution is by adding new table to save category list for place. But my solution look nice, right?

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment