0

I am new to MySQL and everything related to coding, and I need your help! I have a table that contains the following columns:

table1
~~~~~~
id,
question,  
answer, 
keywords.

the 'keywords' column contains multiple words with a comma in between(ex: word1, word2, word3, etc...) and I have a search bot (written in PHP) whenever the user asks a question (the question should be transformed into an array/keyword) then the query should search for the keywords to find the row that contains the most matching keywords and display the answer.

Is there a correct way of doing this? Thank you in advance!

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Annie Ian
  • 11
  • 1
  • 4
    Yes. See normalization. – Strawberry May 10 '17 at 11:37
  • 2
    The correct way should be to normalize your table.. CSV (comma separated values) are hard to query – Raymond Nijland May 10 '17 at 11:37
  • if you want to exact match keyword then it will not wok, otherwise you can use LIKE in query to search – Ahmed Ginani May 10 '17 at 11:37
  • 2
    Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 – e4c5 May 10 '17 at 11:38
  • 1
    **Stop! in the name of love, before you break our hearts.** https://www.youtube.com/watch?v=NPBkiBbO4_4 Think it over. Do not do this **a,b,c** data formatting unless some fool has left you this data format. Normalize your data. Add a new table for this one-to-many relationship. Do it now. – O. Jones May 10 '17 at 11:51
  • @O.Jones many-to-many relationship! This Song tho :-D :-D – Daniel W. May 10 '17 at 12:03

2 Answers2

1

It is possible in MySQL (see SQL split values to multiple rows) but the best practice and most convenient solution would be normalization, the first form (atomicity) to be exact.

Create a new table, named keywords with keyword_id (indexed) and keyword (unique), add a compound primary key over both fields.

Create another table called question_has_keywords with id from your main table and keyword_id, both Foreign Keys.

This is called a N to M relation table because many questions can have many keywords.

To lookup the data, you would use JOIN.

Funfact - This would be such an easy task using MongoDB Aggregation ($unwind pipeline stage).

Community
  • 1
  • 1
Daniel W.
  • 31,164
  • 13
  • 93
  • 151
0

I would suggest use FIND_IN_SET() function to search keyword in your column separated by comma

Amit Gaud
  • 756
  • 6
  • 15