search keyword in mysql field which has comma separated value

I have 2 tables resumes and category. Resume table stores only th开发者_如何学Ce category id and the category table stores the cat id and name. Resume table category field values has comma separated value for example 1, 4, 6, 9 . Now the problem is I have to search the keyword hotel management which has the category id 6 in category table. What is the query to do this? Any one please help.

  SELECT * FROM resumes t1 LEFT JOIN resumes_category AS t2 ON t2.`id` = t1.`category`
    WHERE (
    t1.name LIKE '%test%'
    OR t1.keywords LIKE '%test%'
    OR t1.description LIKE '%test%'
    )

Along with this query, I have to search the resume category keyword hotel but resume table has category id only.

What you are describing is a Many-To-Many relationship.

In relational databases, you cannot express a many-to-many relationship using only the two tables involved. You must break up the many-to-many into 2 one-to-many relationships using what is called a link table.

Your tables then become the following:

+---------------------+  +---------------------+  +---------------------+
| resumes             |  | categories          |  | resumes_categories  |
+---------------------+  +---------------------+  +---------------------+
| resume_id (PK)      |  | category_id (PK)    |  | resume_id (PK,FK)   |
| other_fields        |  | name                |  | category_id (PK,FK) |
+---------------------+  | keyword             |  +---------------------+
                         | description         |
                         +---------------------+

Using this table layout, your queries then become relatively easy:

SELECT resumes.*, categories.*
  FROM resumes LEFT JOIN (resumes_categories, categories)
               ON (resumes.resume_id = resumes_categories.resume_id AND
                   categories.category_id = resumes_categories.resume_id)
  WHERE categories.name = 'test';

If you are stuck using the database layout you are currently using, MySQL has a function called FIND_IN_SET(). It returns an integer representing the position of the found string, or NULL.

Note that this function is slow and I would recommend changing your database layout.

SELECT *
  FROM resumes, resumes_category
  WHERE FIND_IN_SET(resumes_category.id, resumes.category) IS NOT NULL
   AND FIND_IN_SET(resumes_category.id, resumes.category) > 0
   AND resumes_category.name = 'test';