Search Tips
Posted: Fri Feb 14, 2003 11:43 pm
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
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.
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
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.