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:
What is the room price ?
room and price.Can I pay with credit card ?
credit card.What is the room size ?
room and size.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.
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.
My approach would be to use the concept of STOP WORDS remove all STOP WORDS from the user query.
Then only to search for ALL the KEY WORDS in the user query.
DATA entry needs to remove most of the users data to be robust. What if they intend to break your system by inserting CODE.
STOP WORDS include 'the' 'a' 'of' The idea is to remove as much rubbish as you can and then to be very picky about other words.
Log the query data for inspection in case of failure. log the ACCESS data that you think you are processing and then set a timeout on the response time. eg. if you know that the query should only ever take X ms. Then anything that takes longer than that is suspect. It could have gotten past your protective layer. DO make sure you log the IP address and timestamp in the log files - preferably right at the start of the log entry.
Then write scripts for handling a SLICE. A SLICE is a nice way to help the system administrators who may have to send you a slice of the log files. The slice can be complicated - from DAY (YYYYMMDDmm.s) to another DAY and they may have had an overnight compression system running - so your script needs to access normal log files and compressed log files. Sometimes the files are split up by system failures - ie. the system died for some reason.
Your SLICE info can be packaged up into an email etc. and sent to you for analysis.
Good luck.