mysql query question

Question

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? Thanks.

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

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";

Answer 1

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()