Full-text searches in DB

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
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Full-text searches in DB

Post by dardsemail »

Hi,

Quick question I'm hoping someone can answer...

I'm building my first search engine for a client and I've got the basics figured out, but was wondering whether or not its possible to mix 'regular' searches and full-text searches in the same query?

For example, could you do something like:

Code: Select all

SELECT * FROM table_name WHERE field_name1 = '$string1' OR MATCH (field_name2, field_name3)  AGAINST ('keyword')
I suppose I could just try it to see whether its possible... but I thought I'd ask.

The search is intended to work on multiple fields - some of which include pretty simple 'yes/no' verifications against fields in a database, and some which need to do keyword searches. I'm wondering whether I should include these in one query - or generate multiple queries. It may also be a result of the way in which my db is designed. I may need to optimize the configuration somewhat... hmmmm.... food for thought.

Thanks!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Ya, I think it's possible, but you need to re-arrange your query:

Code: Select all

SELECT
        *,
        MATCH(field_nam2,field_name3) AGAINST('keyword') AS score
FROM
        table_name
WHERE
        MATCH(field_name2,field_name3) AGAINST ('keyword') OR
        field_name1 = '$string1'
ORDER BY
        score
You'll also note I put a MATCH ... AGAINST line in the SELECT clause. Ordering by that result will give you a result set ordered by relevancy. It's a neat trick but one that isn't really intuitive in my opinion.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Post by dardsemail »

Thanks so much. I'll give it a try. I'm not sure I'll need to do it on this project as I realized that I could do things a little more simply by just creating individual fields with yes/no on a couple of items that were to be part of the match. But I may try it anyway, just out of curiosity and to allow end users the ability to search on specific fields as well as keywords.

Thanks again!
Post Reply