| 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
| 7:20 am on Mar 21, 2013 (gmt 0)|
cool, how do I do that?
| 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.
| 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?
| 10:04 am on Mar 22, 2013 (gmt 0)|
... dble post
[edited by: topr8 at 10:08 am (utc) on Mar 22, 2013]
| 10:07 am on Mar 22, 2013 (gmt 0)|
look into MATCH there are a whole bunch of things you can do with it ...
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!