0

What is the best way to build a MySQL query so as to:

  1. Display only 100% match if it exists (one row will be returned) or, if there isn't a 100% match,
  2. Display other entries with different accuracies (many rows returned, but not entries with 0% accuracy).

The results should resemble something like that:

SELECT Green Mile FROM books

Results:
Green Mile | 100%

SELECT Green Road FROM books

Results:
Green Mile | 50%
Winding Road | 50%
Greet Me | 20%

I am not providing any database schema as I will be grateful to receive general advice about how I could achieve similar results quering a database. I should add that queries and output are constructed in PHP.

Thank you in advance!

luqo33
  • 8,001
  • 16
  • 54
  • 107
  • How do you define "100%" match? How do you define partial matches? – Gordon Linoff Sep 03 '14 at 13:59
  • You can't (easily) do that sort of thing in MySQL. I guess you're wanting some sort of "weighting" - look at Levenstein Functions - try this http://stackoverflow.com/questions/4671378/levenshtein-mysql-php – splig Sep 03 '14 at 14:00
  • @Gordon Linoff I define 100% match when the term searched for exists in a database in its entirety - exactly word for word. Partial match shoudl be calculated based on the number of matching characters in the searched term v. database entries. @splig I was hoping for a simpler solution. Mayby SQL `MATCH`..`AGAINST`? – luqo33 Sep 03 '14 at 14:05
  • fulltext searches can give you a relevance score, but the number you get back is somewhat arbitrary. if you want percentages, you'll have to do the work yourself. – Marc B Sep 03 '14 at 14:13

0 Answers0