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).
