Code for a basic search engine

accurate site search

[whatson]

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]

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]

cool, how do I do that?

[topr8]

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]

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]

... dble post

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

[topr8]

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!