Welcome to WebmasterWorld Guest from 54.196.125.165

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

SQL search engine type query.

Adding search capabilities to website

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

Junior Member

10+ Year Member

joined:Dec 18, 2003
posts:58
votes: 0


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

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

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 28, 2002
posts:993
votes: 2


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.

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 8, 2004
posts:1679
votes: 0


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

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members