homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

SQL search engine type query.
Adding search capabilities to website

 9:13 pm on Aug 19, 2004 (gmt 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.




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

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)

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)


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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved