How to find the most suitable answer in the MySQL/PHP on the key words of the question?

0 like 0 dislike
There is a MySQL database which has columns: keywords and answer.
In the keywords column contains arrays of keywords in JSON format. It can be both words and phrases, for example:
["test", "test", "test phrase"]
In the answer column provides answers to these key words.
There is a message: "test phrase", as to find her a more appropriate answer, given that we have a question with the key word "phrase" and key phrase "test phrase"?

The first thought is to split the sentence according to the word and look for every answer for "LIKE %test% AND LIKE %phrase%", but in this case, if not in entry "%test%", the base did not return, although I would have to find a match for at least "phrase".
by | 4 views

2 Answers

0 like 0 dislike
With version 5.7, mySql supported JSON
how to find her a more suitable answer

for the test phrase will test bol any phrase, not a phrase
So I would first look for an exact match, and if it is not present and the phrase - the phrase, not the word, then it would have broken at spaces.
0 like 0 dislike
Perhaps in this case it is necessary to implement full-text search. And not necessarily MySQL tools , there is a more powerful solution, like Sphinx. Such engines "know how" and search for word forms, search for parts of words, misspelled words, etc. ( for example, it will be searched for and "test phrase" , 'tekstovye phrase'). Own implementation of such functionality on their own the task is not so hot )))

Related questions

0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
asked Mar 22, 2019 by pdx
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
110,608 questions
257,186 answers
28,760 users