Performing a search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Performing a search

Post 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. :)
Last edited by evilmonkey on Thu Dec 22, 2005 6:05 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Hey that's cool! Thanks.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post 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?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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;
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post 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?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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!
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post 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!
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Okay, I more or less have it figured out. Thanks guys!
Post Reply