Page 1 of 1

Search Tips

Posted: Fri Feb 14, 2003 11:43 pm
by McGruff
The mysql manual isn't much help for a complete explanation of RLINK expressions, and I'm sure many suffered like I have when creating their first search script.

Here's what I've managed to find out so far, for future reference:

LIKE, %LIKE, and LIKE% only seem to look from the start and/or end of a field - OK for short fields with a single word, but not much use if you want to find a string in a large text field.

(Incidentally, I wonder if padded column types would upset LIKE?)

%LIKE% has it's uses in big text fields, but doesn't let you do the kind of wildcard searches most people are used to.

This is how I search large text fields:

Loose match (eg buff as well as buffy and rebuffed):

"... LIKE '%" . $term . "%'"; // most people know that one

Exact Match: (eg buff not buffy or rebuffed)

"... RLIKE '[[:<:]]" . $term . "[[:>:]]'"; // not in the manual :roll:

Match Wildcard (buff* or *buff):

" ... RLIKE '[[:<:]]" . $term . ".*'"; // following wildcard
" ... RLIKE '.*" . $term . "[[:>:]]'"; // preceding wildcard

Wildcards: you'd need to strip out any * wildcard characters from POST'd search terms, and IF / ELSE to pick the correct search expression.

Posted: Sat Feb 15, 2003 11:24 am
by Takuma
I reckon using MATCH() is better... Well they say it's better anyway :o

Posted: Sat Feb 15, 2003 11:36 am
by McGruff
Thanks. Got any info for that? Couldn't find it in the mysql manual.

Posted: Sat Feb 15, 2003 1:39 pm
by Takuma
It is on the MySQL manual, the URL is
http://www.mysql.com/doc/en/Fulltext_Search.html
I just remebered the main function is FULLTEXT()... :oops:

Posted: Sat Feb 15, 2003 2:17 pm
by McGruff
Thanks I've got a downloaded version which doesn't seem to have that.