Welcome to WebmasterWorld Guest from 54.196.175.173

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

SQL search engine type query.

Adding search capabilities to website

     

humpo

9:13 pm on Aug 19, 2004 (gmt 0)

10+ Year Member



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

9:32 pm on Aug 19, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

10:20 pm on Aug 19, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month