PHP Search Title and Tags

Question

Greetings, I'm thinking about the best way to implement a search on my website. I know about Sphinx and MySQL Full-text searches, however I'm not just searching a single field.

I have two things that I want to search: the title of an article and the tags associated with that article.

What I was thinking of doing is defining another column in my article table, which would contain the keywords from the title, and each of the tags appended to the end of this string.

For example, if the article title was 'how to build a home cinema', and had the tags 'DIY', 'technology' this field would be 'build home cinema DIY technology'. I could then perform a search on this field.

The only downside I see to this approach is that if the tags were updated, I would have to update this field as well which could lead to this field being inaccurate or out of date.

Is there a better way to do this?

Answer 1

Sphinx can do search based on multiple columns. It even allows to define "weight" for each column. I think you should stick with it.

Answer 2

You can do Full text searches on multiple columns of a table in MySQL in MyISAM.

See:

$search = 'how to do short circuit evaluation?';
$q = 'SELECT * FROM `table` WHERE MATCH(tags, title, description) AGAINST ('.mysql_real_escape_string($search).' IN BOOLEAN MODE)';
$r = mysql_query($q);
//.. so on

doing another table requires constant synchronization and it's quite tedious for your system.

Since your tags are in a many to many relationship, you can get them and combine into the query

$q = 'SELECT * FROM `table` WHERE'.
  ' MATCH(tags, title, '.
  '(SELECT  GROUP_CONCAT(`tag`) FROM `tags`'.
  'WHERE `ArticleID`='.(int)$ArticleID.')'.
  ') AGAINST ('.mysql_real_escape_string($search).' IN BOOLEAN MODE)';

Answer 3

Use the multi-valued attributes in Sphinx for your tags:

MVAs, or multi-valued attributes, are an important special type of per-document attributes in Sphinx. MVAs make it possible to attach lists of values to every document. They are useful for article tags, product categories, etc. Filtering and group-by (but not sorting) on MVA attributes is supported.

http://www.sphinxsearch.com/docs/current.html#mva

Answer 4

Sphinx, Lucene, and pretty much all full-text search are not limited to single fields. In fact, the default is to search in all fields and then filter for specified ones. I think the internal MySQL search isn't great, Sphinx and Lucene plug-ins are much better.