Match nearest string to a particular string

Question

What search algorithm should I use to give results when a user searches for a title of a book?

If there exists a book that title should be returned otherwise the book titles that are close to it have to be displayed.

The first case is easier just searching a string. I need how to solve the second case which most of the websites use.

The books table contains fields category, class and title. I have tried the below code and I am getting results when I give exact title I want the system to give titles that are closely related.

$title=isset($_GET['title'])?$_GET['title']:NULL;
      //$code=isset($_GET['code'])?$_GET['code']:NULL;
      $class=isset($_GET['class'])?$_GET['class']:NULL;
      $cat=isset($_GET['category'])?$_GET['category']:NULL;
      if(isset($title))
      $qry="select * from books where quantity>0 and title='$title' ";
      else{
          $qry="select * from books where quantity>0 ";
          if(isset($class)) $qry.=" and class='$class' ";
          if(isset($cat))   $qry.=" and category='$cat' ";
      }

How are book titles stored? Where are they stored? What have you tried already? We aren't here to spoonfeed. — Daniel, Apr 14 '16 at 18:37

score 1 · Accepted Answer · answered Apr 14 '16 at 19:07

1

Well, first i'd recommend using prepared statements to avoid SQL injection via your current code, but using the LIKE operator in SQL should work in the second case.

Change

`select * from books where quantity > 0 and title = '$title'`

to

`select * from books where quantity > 0 and title LIKE '%$title%'`

What this does it checks for missing letters around the word also. So if title was equal to "rabbit" then both "White rabbit", "The Rabbit" and "Rabbit" would be results.

p.s. whitespace costs nothing

answered Apr 14 '16 at 19:07

Daniel

1,229
14
24

Okay. What about if user types "rabit" (typo) and then how to display all the related books? – user6181297 Apr 14 '16 at 19:19
There's no built in way to do this in SQL. Check out http://stackoverflow.com/questions/487003/how-to-detect-a-typo-in-a-product-search-and-suggest-possible-corrections – Daniel Apr 14 '16 at 19:22
How to divide the words in title based on white spaces and search each word? – user6181297 Apr 14 '16 at 19:35
`$words = explode(" ", $title);` and search each word, or join them together into one statement and make use of sql contains(). – Daniel Apr 14 '16 at 19:40
How to sort them according to best one(based on which matches exactly almost) first – user6181297 Apr 14 '16 at 20:09
Sorry for the late reply, but I did a quick search and found this: http://stackoverflow.com/questions/11144394/order-sql-by-strongest-like – Daniel Apr 15 '16 at 12:14
What is the best way among all the above to show related results taking into account that user should be able to see only limited and relevant books? – user6181297 Apr 17 '16 at 09:09
I am using explode and getting same value twice and even more times because each word matches and giving n results of same kind. What should I do? – user6181297 Apr 17 '16 at 11:31
I'm not sure on which is the best, but i'd try out a few to see which yields the best results. Also, you could just merge the duplicates using array_unique() – Daniel Apr 17 '16 at 23:00

Match nearest string to a particular string

1 Answers1