CODEWITHSUNDEEP
phpmysql
hayllone
hayllone

Reputation: 141

Mysql query, to select a sentence that match to a list of keywords

i've searched for similar question that is answered and helps me but i cannot find. So, my question is, how to write the mysql query to pull the sentence from DB, which contains a keywords generated from another sentence, like this: 

$tyrsena = "php statement for clause";  
$ques = "SELECT * FROM question WHERE title IN '$tyrsena'";  
$re = mysql_query($ques)or die(mysql_error());  
while ($da = mysql_fetch_assoc($re)){    
        $sugg = $da['title'];  
        echo $sugg;  
}

So $tyrsena is the sentence from which I need to generate the keywords, and i want to match the titles from question table which match to those keywords.
The code that i'm presenting is not working, its matching the sentece only if its 100% same, i'm giving it just to imagine what i want to achieve. Thank you guys :)

Upvotes: 2

Views: 1671

Answers (2)

Jim Dennis
Jim Dennis

Reputation: 17510

First it sounds like you need to create a "FULLTEXT" index on your table and use the MATCH() function in your query.

Do StackOverflow searches on the following two tags and peruse the questions and answers that relate to this:

  • mysql
  • full-text-search

Also you'd have to tokenize the source sentence into a list of words such that it would be suitable for interpolation into your query. The naive strategy would be to split on spaces and, for each resulting term, strip off any trailing punctuation and convert it all to lower case. (This preserves "o'clock" for example). You might also strip of any instances of "'s" (apostrophe "s") and "n't" and similarly common contractions. (A less naive approach would do proper stemming.

Normally you'd also filter out any "stop words" (those which are far too common in English sentences to be of any value in keyword searching: of, a, an, the, by, for, in, it, ... etc. (You should be able to find a list of the two or three hundred recommend English stop words pretty easily with simple web searches). (Some are listed the bottom of that Wikipedia article.

Upvotes: 6

hsz
hsz

Reputation: 152266

Try with:

$tyrsena = "php statement for clause";
$tyrsena = explode(' ', $tyrsena);
$tyrsena = implode("','", $tyrsena);
$ques = "SELECT * FROM question WHERE title IN '$tyrsena'";

or quicker:

$tyrsena = "php statement for clause";
$tyrsena = str_replace(" ", "','", $tyrsena);

Upvotes: 1

Related Questions