SQL search engine type query.

Adding search capabilities to website

[humpo]

Have a database of websites, each website record has several keywords - written in PHP and mysql.

Would like to have some basic search features such as returning best matches first :)
So if someone searched for “widget maker city” it would return all those whose keywords match all 3 terms first, then return those that match two terms and so on.

This seems a bit advanced for my basic understanding of SQL

Any code or recourses(adding search to sites etc) anyone can point me to would be appreciated.

Cheers

[Philosopher]

I found a script I found a while back that has worked well for me. Feel free to sticky me for the URL to the site since I can't post the info.

It's not free, but it's cheap ($5 or $10) if memory serves me.

[Lord Majestic]

This is poor way to search for a number of good reasons, but anyway here is code for MySQL v4.0.20:

1) assume keywords kept in the following table:

create table docs
(
url varchar(255),
keywords varchar(255)
)

2)

-- create temp table
create temporary table tmp (url varchar(255));

-- you will need to create dynamic SQL that will have these queries for all keywords as follows:

insert into tmp
select url from docs where keywords like '%blue%'
union all
select url from docs where keywords like '%widgets%';

-- like %keywords% is bad because it will cause table scan

-- here we select from tmp table and group by number of matches:

select url,count(*) as matches from tmp group by url order by matches DESC;

-- clean up
drop table tmp;

Note: this is poor code especially for any significant number of keywords, better one is more complicated so for simplicity's sake I did not provide it. You might be better off using MySQL's full text indexing (available from version 4 I think).