使用句子中的关键字查找记录

Question

I have some problems in my search function. When some user type the sentences in search field I want to get the result from the keywords inside the sentence which user type before. For example I have database table like this:

ID | Keywords             | Answer
-----------------------------------------------------------------------------
1  | price, room          | The price room is $150 / night
2  | credit card          | Yes, you could pay with credit card
3  | location             | The Hotel location is in the Los Angeles
4  | how to, way to, book | You could pay with credit card or wire transfer
5  | room, size           | The room size is 50sqm

And this is the examples of sentences which user input:

1. What is the room price ?

   • From that sentences the system will find the keywords inside the senteces in that case the keywords is room and price.
   • And from that keywords the systems will show the answer is The price room is $150 / night

2. Can I pay with credit card ?

   • From that sentences the system will find the keywords inside the sentences in that case the keywords is credit card.
   • And from that keywords the systems will show the answer is Yes, you could pay with credit card

3. What is the room size ?

   • From that sentences the system will find the keywords inside the sentences in that case the keywords is room and size.
   • And from that keywords the systems will show the answer is The room size is 50sqm

The example 1 and 3 has room in the sentences. I would also want to know that the keywords is room price and room size.

How could I find the keywords from the sentences which user already input ?

How to I get the answer from database with that keywords ?

From that examples I want to know how could I to do that with PHP and MySql ? Or maybe there is some way to build that ? Please anybody knows to do this could help me. Thanks before.

Answer 1

I would suggest not to store keywords separating with commas in single row, instead insert them in different rows. Because when you will try to search any text which is in keywords it will always check for credit card or price, room. It will not consider price and room as different words instead it will consider this as string.

For your question, try following code :

$que = 'What is the room price';

$keywords = str_replace(" ", ",", $que);

$sql = 'select answer from your_table where keywords IN (' . $keywords . ')';  

OR you can try for FIND_IN_SET() to search comma separated keywords.

It may work.