4

I have a table with names of movies, and I want to be able to search for a movie in that table. But I want to be able to search for part of the title, and still return a result. For example, if there is a record with the name "The quantum of solace", then I want to be able to do a search for "quantum solace", or even "007: quantum solace" and I want to find that record. Is there a way to do this?

EDIT And how do I sort according to the matches? That is, the row that matches the most, should be returned first.

Marius
  • 57,995
  • 32
  • 132
  • 151

2 Answers2

5

Use a MySQL Full Text Search in boolean mode. If you do this when you search for '007: quantum solace' as it contains at least one matching result in the column it will be displayed, you can then order by relevancy.

SELECT *, MATCH(title) AGAINST ('quantum solace' IN BOOLEAN MODE) AS rank 
FROM films
WHERE MATCH(title) AGAINST ('quantum solace' IN BOOLEAN MODE) ORDER BY rank DESC
Mark Davidson
  • 5,503
  • 5
  • 35
  • 54
1

Have a look at the full text search capabilities of MySQL. Once you set up a full text index, you can do queries like this one:

SELECT * 
FROM movies
WHERE MATCH(title) AGAINST ('quantum solace' IN BOOLEAN MODE)
Tomalak
  • 332,285
  • 67
  • 532
  • 628