CODEWITHSUNDEEP
phpmysql
Mostafa Elkady
Mostafa Elkady

Reputation: 5801

How to match key words in a sentence against pre-existing sentences in a database table?

I want insert "This is a apple, it is a fruit" to query the database, and only match the words 'apple' and 'fruit' not 'fruits' from the database and return '1 fruit apple red 10'.

How to modify the query code?

Database name 'food' fields as below:

id name1 name2 name3 name4 type
1 fruit apple red 10 article
2 fruit banana yellow 12 article
3 drink beer yellow 6 article
4 fruits apple yellow 16 books

Code:

mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("article") or die(mysql_error());
... ...
echo $match . "r"; // $match= This is a apple, it is a fruit
$query = mysql_query("SELECT * FROM food WHERE name1, name2 like '%$match%' ");
$row=mysql_fetch_array($query);
echo $row['id ']. "r" .$row['name1']. "r".$row['name2']. "r".$row['name3']. "r".$row['name4']. "n";

Upvotes: 0

Views: 115

Answers (1)

Thomas
Thomas

Reputation: 53

It's hard to tell, but I think if you are trying to avoid returning anything that does not equal the exact word (apples != apple). You will have to break your $match string into smaller parts and check for them individually. maybe use explode() to break the string apart using white spaces and then do exact matches on the words that apply.

Currently, You are checking for any data in the database that could resemble the string in anyway whatsoever. So it could return "pineapple is a fruit" just because it has the words "is", "a" and "fruit".

It might also be worth it to rename your database columns. Nobody whats you look a code with name1,name2,name3.... how about type,name,color,amount?

$match = trim($match);
$match = strtolower($match);
$newmatch = explode(" ",$newmatch);

So if $match was "This is an apple" $newmatch = array("this","is","an","apple")

Of course, this wont get rid of any punctuation. But you can do before hand if you like with trim and preg_replace.

Help: trim() , strtolower() , explode()

Upvotes: 3

Related Questions