Have an account? Sign in
Login  Register  Facebook
mysql query 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?

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%' ");
echo $row['id ']. "\r" .$row['name1']. "\r".$row['name2']. "\r".$row['name3']. "\r".$row['name4']. "\n";
Started: September 21, 2011 Latest Activity: September 21, 2011 mysqlquery
3 Answers
Thanks master, I have read the tutorial of trim() , strtolower() and explode().
explode() is very powerful, and my question is solved.

$newmatch = explode(" ",$newmatch); is the best way.

Posted: youlichika
In: September 21, 2011

Not sure exactly what you're asking but if you're wondering how to match fruit but not fruits (same with apple and apples) you can do this:
 WHERE name1 REGEXP '[[:<:]]fruit[[:>:]]' OR name1 REGEXP '[[:<:]]apple[[:>:]]'


Posted: Go
In: September 21, 2011

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

Posted: MacOS
In: September 21, 2011

How to break $match? I'm not sure how many words is the sentence composed of? This time is 8 words( This is a apple, it is a fruit.) May be next time will be 4 words(pineapple is a fruit.) Or is there anyway to instead of mysql_query?I just want put a sentence into database to match the keyword and echo of the row.
September 21, 2011

You dont need to know the length to break it up. If you want to be really clean and neat about it, I suggest using trim(),strtolower(), then explode(). Explode can be used to output anything separated by a space into an array. You can then cross check individual sections in the array with the database.
September 21, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer