Search Tips

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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Search Tips

Post 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.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

I reckon using MATCH() is better... Well they say it's better anyway :o
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Thanks. Got any info for that? Couldn't find it in the mysql manual.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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:
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Thanks I've got a downloaded version which doesn't seem to have that.
Post Reply