homepage Welcome to WebmasterWorld Guest from 54.237.99.131
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

    
Code for a basic search engine
accurate site search
whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4556910 posted 7:44 pm on Mar 20, 2013 (gmt 0)

So I want to be able to search through my database on my site.

Essentially I want it to work like this, someone makes a search query, then I want to only show results that contain each keyword, at least once within the title or description fields.

So I assume first I break the search query into an array, then each element of the array is checked to see if it is contained in the title or description of each row.

Then a check is done to make sure that all elements of the array have occurred in the title or description. Then these are the results I want to display.

Is that clear enough?

 

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4556910 posted 7:51 pm on Mar 20, 2013 (gmt 0)

Create a full text index on the two db columns (you can create an index across multiple columns)

then query the database using MATCH

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4556910 posted 7:20 am on Mar 21, 2013 (gmt 0)

cool, how do I do that?

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4556910 posted 9:18 am on Mar 21, 2013 (gmt 0)

create the full text index using phpMyAdmin or however you usually admin your database ... or run a query like:
ALTER TABLE myTable ADD FULLTEXT(title, description); (you would need sufficient privileges to do this)

and then your query would be something like this:

SELECT * FROM myTable WHERE MATCH(title,description) AGAINST('+keyword1 +keyword2' IN BOOLEAN MODE)

that's how i do it anyway.

you need to write some kind of function in order to add the '+' sign before each word.

note by default i think the index only indexes words of 3 letters or more, you can change this in the MySQL configuration file if you want to.

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4556910 posted 8:44 pm on Mar 21, 2013 (gmt 0)

Oh wow, that is so much easier than I had envisioned. Thanks so much.
So I would create a variable for the search keywords, then replace ' ' with +, and then add it in there. I don't even need to create an array.

What about ranking, how can I sort rows where the keyword is in the title above keyword in the description?

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4556910 posted 10:04 am on Mar 22, 2013 (gmt 0)

... dble post

[edited by: topr8 at 10:08 am (utc) on Mar 22, 2013]

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4556910 posted 10:07 am on Mar 22, 2013 (gmt 0)

look into MATCH there are a whole bunch of things you can do with it ...

https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

i'm not sure if you can weight one column above another, there is a RELEVANCE function but i'm not sure how it works i haven't used it.

... remember to add a '+' at the beginning of the string as well!

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