Full-Text Searching

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
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Full-Text Searching

Post by rubberjohn »

Hi,

I just need something clearing up regarding implementing a full-text search.

Suppose you have three tables with the following fields:

USERS
user_id
user_name

KEYWORD_USER
user_id
keyword_id

KEYWORD
keyword_id
keyword

So a (searching) user will enter a number of keywords, the results are then ordered by the user in the db with the most matching keywords.

I havent used full-text before so i just want to check that the score feature wouldn't work in this situation - or would it?

I was thinking something like this would be more effective

Code: Select all

SELECT * from KEYWORD
WHERE keyword = search_array??

SELECT * from KEYWORD_USER
WHERE keyword_id = KEYWORD.keyword_id
Then place the returned user_id as a key into an associative array with their corresponding keyword_id as the values.

A Join would be used instead of a nested SELECT.

Would that be better or is there another way to do this?

Thanks

rj
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Moved to Databases
Post Reply