Welcome to WebmasterWorld Guest from 220.127.116.11
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.
1) assume keywords kept in the following table:
create table docs
-- 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%'
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).