homepage Welcome to WebmasterWorld Guest from 54.234.0.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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
humpo

10+ Year Member



 
Msg#: 4804 posted 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.

Cheers

 

Philosopher

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4804 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4804 posted 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)
)

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

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