topr8
msg:4556915 | 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
msg:4557011 | 7:20 am on Mar 21, 2013 (gmt 0) |
cool, how do I do that?
|
topr8
msg:4557039 | 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
msg:4557217 | 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
msg:4557355 | 10:04 am on Mar 22, 2013 (gmt 0) |
... dble post [edited by: topr8 at 10:08 am (utc) on Mar 22, 2013]
|
topr8
msg:4557356 | 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!
|
|
