Welcome to WebmasterWorld Guest from 54.159.214.250

Forum Moderators: coopster & jatar k

Code for a basic search engine

accurate site search

   
7:44 pm on Mar 20, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
7:51 pm on Mar 20, 2013 (gmt 0)

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



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

then query the database using MATCH
7:20 am on Mar 21, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



cool, how do I do that?
9:18 am on Mar 21, 2013 (gmt 0)

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



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.
8:44 pm on Mar 21, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
10:04 am on Mar 22, 2013 (gmt 0)

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



... dble post

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

10:07 am on Mar 22, 2013 (gmt 0)

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



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

[dev.mysql.com...]

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!
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month