Page 1 of 1

Full-text searches in DB

Posted: Thu Jun 23, 2005 9:54 am
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!

Posted: Thu Jun 23, 2005 10:06 am
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.

Posted: Thu Jun 23, 2005 10:16 am
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!