如果一列有多个关键字，如何从数据库中获取结果？

Question

Well, I've a table called keywords and there are 2 columns.

1) kid
2) keywords (keyword value)

and I've another table called contact_details where there a column called keyword. In this keyword column I'm inserting many keyword from keywordstable. So 2 tables is look like this...

Keywords table:

kid    keyword
1      php     
1      mysql     
1      html     
1      css     
1      css3     
1      wp     
1      photoshop     
1      3d

contact_details table:

cid    name    phone   keyword
1      alex    123     php, mysql, hmtl
2      alex1   124     php, html, css3
3      alex2   125     wp, html, css
4      alex3   126     photoshop, 3d
5      alex4   127     html, 3d, php
6      alex5   128     mysql, wp, html

Now I've a search box which is searching people (name From contact_details table) by using keyword value. In search box, Search value could be few keywords. I mean it's could be php, mysql, html or could be php, 3d, photoshop.

So My question is : how can I write a Sql query to get the result ? I want to get all name which match the search keyword/s from contact_details table ?

Is there any field need to add in keywords table ? Can not get an IDEA :(

Answer 1

Select name from contact_details where keyword like '%<search keywords>%'

Like search keyword key php then you need pass php in query and will get list of all name which having keywords as php'

Select name from contact_details where keyword like '%php%'

hope this will resolve your issue.

Proper approach, Make kid as primary key in Keyword table

Keywords table:
kid    keyword
1      php     
2      mysql     
3      html 

Remove keywords column from contact_details table.

contact_details table:
cid    name    phone   
1      alex    123     
2      alex1   124     
3      alex2   125 

Make one more table which having many to many relationship and you need insert the relationship here so that no need touch again keywords and contact_details table again.

keyword_contact_mapping
kcid   kid_fk   cid_fk
1      1        1     
2      1        2
3      1        3
4      2        1    
5      2        1 
6      2        2
7      2        3

Sql query (Not tested you can also use alias)

select name from contact_details join keyword_contact_mapping on kid_fk =(select kid from Keywords where keyword='php')