1

I have a column that holds the genre of a movie (up to three specifiers separated by slashes) such as "comedy/romance/adventure." Is there a function or something similar in mysql or php that would allow me to take the genre of a movie and compare it with other genres of other rows and arrange them by likeness? For example, having a movie with "comedy/romance/adventure" would return movies with all three first, and then movies with 2 of those genres, and finally movies with maybe 1 of those genres.

Thomas
  • 2,231
  • 1
  • 19
  • 27
oipsl
  • 337
  • 4
  • 19

2 Answers2

3

If you enable full-text indexing on the genre column, you can do it. I would recommend using an external full-text search engine such as sphinx to handle this, though, as MySQL's built-in full-text indexing really ain't that great.

You'd start by setting a full-text index on the genre field

ALTER TABLE movies ADD FULLTEXT INDEX (genre);

Then you'd be able to select from this like so:

SELECT *, MATCH(genre) AGAINST ('comedy romance adventure') AS relevancy FROM movies ORDER BY relevancy DESC;
Michael McTiernan
  • 5,153
  • 2
  • 25
  • 16
0

I'd also suggest a full-text index like Sphinx or Apache Solr for relevance search.

There are a number of related posts on the subject.

Community
  • 1
  • 1
Thomas
  • 2,231
  • 1
  • 19
  • 27