Page 1 of 1

Performing a search

Posted: Thu Dec 22, 2005 5:53 pm
by evilmonkey
Hello,

I'm interested in performing a search on a MySQL database. I'm writing something similar to a search that is used on this forum. Something like this:

+-----------------------------------+
| id | message |
+------------------------------------
| 1 | How about those
| | Toronto Maple Leafs?
+---------------------------------------

I want the query to match "Leafs" or "Toronto". I thik you understand what I'm saying. I'm running MySQL 4.0.25. I looked in the MySQL documentation for full-text searches, but that's for MySQL 5, plus I don't get what they're trying to explain. :(

Thanks for the help. :)

Posted: Thu Dec 22, 2005 5:58 pm
by John Cartwright
http://dev.mysql.com/doc/maxdb/en/9e/21 ... ontent.htm

In short its the use of the LIKE clause.. the % delimiters mean wildcard.. so using your example

Code: Select all

SELECT * FROM `table` WHERE `message` LIKE '%Leafs%'
Go Leafs Go

Posted: Thu Dec 22, 2005 6:05 pm
by evilmonkey
Hey that's cool! Thanks.

Posted: Thu Dec 22, 2005 6:20 pm
by evilmonkey
Actually, one more question. What's the best way to give relevant search results? For instance, if a user is searching for more than one keyword, how can I order the matches in order of relevancy?

Posted: Thu Dec 22, 2005 6:45 pm
by seodevhead
Well when you use LIKE there is no relevancy since it is either a match or no match. There is no ranking among the results. What you can do is use a FULLTEXT search (with a FULLTEXT index on the columns you want to search) and you can use ORDER BY relevance DESC;

SELECT MATCH('col') AGAINST ('keyword') AS relevance FROM table WHERE MATCH('col') AGAINST ('keyword') ORDER BY relevance DESC;

Posted: Thu Dec 22, 2005 6:56 pm
by evilmonkey
Okay, can you elaborate on that? What if there's more than one keyword? Are there wildcards? Will this work with 4.0.25?

Posted: Fri Dec 23, 2005 8:36 am
by seodevhead
No there are not wildcards in FULLTEXT searching because they are not needed. FULLTEXT is a "google-type" search. Yes, it will work on MySQL 4.0.25, but just a note, you should upgrade to 4.1 so you can use a clause called "WITH QUERY EXPANSION" which does a good job of adding more results to a search... which is quite helpful when you have a new database with limited records.

As posted before... this is the syntax you want to use:
SELECT MATCH('col') AGAINST ('keyword') AS relevance FROM table WHERE MATCH('col') AGAINST ('keyword') ORDER BY relevance DESC;

Normally what you would do is put a variable in the 'keyword' part of the query.. which is either the $_GET or $_POST variable of the user's search form. Like so...

SELECT MATCH('col') AGAINST ('$search_words') AS relevance FROM table WHERE MATCH('col') AGAINST ('$search_words') ORDER BY relevance DESC;

You may want to refer to a book on the in's and out's of FULLTEXT searching... it is very easy. The only thing you must do is make sure that whatever column/columns you call in the query for the FULLTEXT search are all included (no more, no less) in a FULLTEXT index on the DB table you are calling.

Posted: Fri Dec 23, 2005 9:43 am
by neophyte
I did not know the query syntax for that.. I'll have to file that one away in the deep dark reaches of my noodle for later usage....

Tanks!

Posted: Sun Dec 25, 2005 12:23 pm
by evilmonkey
seodevhead wrote:The only thing you must do is make sure that whatever column/columns you call in the query for the FULLTEXT search are all included (no more, no less) in a FULLTEXT index on the DB table you are calling.
And how do I go about creating such an index? Can I do it through phpmyadmin? Also, what is 'col'? Does that have to be replaced by the column I'm searching for?

One more thing: for keywords, can I do this: AGAINST ('word1 word2 word3'), or would they somehow need to be seperated?

Thanks!

Posted: Sun Dec 25, 2005 4:25 pm
by Chris Corbyn
evilmonkey wrote:
seodevhead wrote:The only thing you must do is make sure that whatever column/columns you call in the query for the FULLTEXT search are all included (no more, no less) in a FULLTEXT index on the DB table you are calling.
And how do I go about creating such an index? Can I do it through phpmyadmin? Also, what is 'col'? Does that have to be replaced by the column I'm searching for?

One more thing: for keywords, can I do this: AGAINST ('word1 word2 word3'), or would they somehow need to be seperated?

Thanks!
You can run any query you like through phpMyAdmin.... you can also use the actual mysql client tool on command line.

The MySQL manual has all the info you need on creating fulltext indexes.

In fact, using the keywords "mysql fulltext index" on google you find this is the top result ;)

http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Posted: Mon Dec 26, 2005 12:04 pm
by evilmonkey
Okay, I more or less have it figured out. Thanks guys!