Trigger database search result with multiple keywords

Question

I have a PHP search script that searches a MySQL database to answer questions. Currently my database is set out like this...

id
question = what is the time
answer = The time is 12:00

This data is then sent to my PHP code:

<?php

mysql_connect("localhost","username","password");
mysql_select_db("database");

if(!empty($_GET['q'])){
$query=mysql_real_escape_string(trim($_GET['q']));
$searchSQL="SELECT * FROM questions WHERE `question` LIKE '%{$query}%'  LIMIT 1";
$searchResult=mysql_query($searchSQL);

while($row=mysql_fetch_assoc($searchResult)){
$results="{$row['answer']}";
}

echo $results;
}

?>

Currently, the users query must contain exactly the same text as the question field. My question is; how can I make this work so it triggers a certain for several keywords?

I hope you can understand my question

Could you give an example of the sort of keywords you'd like to match a particular question with? — Clive
– Clive, Commented Oct 11, 2011 at 19:06
I think that kind of natural language processing is going to be beyond the scope of a SO Q&A. — Joe Stefanelli
– Joe Stefanelli, Commented Oct 11, 2011 at 19:07

Juozas · Accepted Answer · 2011-10-11 20:09:03Z

1

I think full test search index can help http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html SOUNDEX function can be useful in this context too. You can split query to keywords and generate dynamic SQL but it is inefficient (performance issues SQL injection attacks ) see http://php.net/manual/en/function.split.php

Alternative is to add table QUESTION_KEYWORD(QUESTION_ID,KEYWORD), split question and search this table to find best QUESTION_ID, but full text search index is more efficient. In fact full text search index uses similar data structure to optimize test search.

edited Oct 11, 2011 at 20:09

answered Oct 11, 2011 at 19:12

Juozas

362 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clive · Accepted Answer · 2011-10-11 19:15:16Z

0

For an easy quick-fix based on your comment you could do something as simple as this:

// Assuming query string is ?q=make,sponge,cake
$searchSQL="SELECT * FROM questions WHERE ";
$count = 0;
foreach (explode(',', $_GET['q']) as $query) {
  $searchSQL .= "`question` LIKE '%" .mysql_real_escape_string(trim($query)) . "%' ";
  if ($count++ > 0) {
    $searchSQL .= ' OR ';
  }
}

$searchSQL .= ' LIMIT 1';
$searchResult = mysql_query($searchSQL);

answered Oct 11, 2011 at 19:15

Clive

36.9k8 gold badges89 silver badges113 bronze badges

1 Comment

Clive Over a year ago

You could just reverse the process, ie q=how,to,make,a,sponge,cake, if it's ok to be loose with your matching (using OR) then that would work. If you want a selective AND then I'm afraid it's more like natural language processing as stated above

Collectives™ on Stack Overflow

Trigger database search result with multiple keywords

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related