0

I want to perform a site search. The thing is that I have 3 columns I want to search, and a row will be displayed in the results if the search query is any of these fields. By which I mean that if someone searches "keyword1 keyword2 keyword3", the columns we search are page name, title and description, so if keyword1 was only in the title, keyword2 was only in the name, and keyword3 was only in the description, that would be a valid result.

I just can't figure out the logic behind this. I tried using regex in the sql with the OR operator, but this still required all the keywords in either the title, name or description. Any idea how I accomplish this?

MJ Khan
  • 1,696
  • 3
  • 21
  • 36
Source
  • 1,026
  • 4
  • 11
  • 23

1 Answers1

2

Please look at MySQL LIKE IN()?

You can replace the spaces with | using str_replace and build a query like:

SELECT * from mytable where pagename REGEXP 'keyword1|keyword2|keyword3' and title REGEXP 'keyword1|keyword2|keyword3' and description REGEXP 'keyword1|keyword2|keyword3';
Community
  • 1
  • 1
MJ Khan
  • 1,696
  • 3
  • 21
  • 36
  • wow! that easy? Is there any easy way to sort the results based on where the keyword was found. e.g. if its in the title it would rank higher than if in the description. – Source Jun 10 '13 at 06:00
  • 1
    depending on how much weightage you want to give.. say I give 5 to title and 3 to description: here is the modified query: SELECT *, 5*(title REGEXP 'keyword1|keyword2|keyword3')+3*(description REGEXP 'keyword1|keyword2|keyword3') as rank from mytable where pagename REGEXP 'keyword1|keyword2|keyword3' and title REGEXP 'keyword1|keyword2|keyword3' and description REGEXP 'keyword1|keyword2|keyword3'; – MJ Khan Jun 10 '13 at 06:25
  • 1
    also you must consider using [mysql fulltext search](http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) if your database engine supports it. That is very fast and provide better search results. – MJ Khan Jun 10 '13 at 06:27