2

I need to write a query for searching a string (keyword) in a database.

When a user enters some text like 'Clothes' then the query should search for the 'Clothes' keyword in the whole database ProductName, ProductCode, SubCategoryName, etc and also for near/similar text. So it should display all 'Clothes' related products, similar to Google search engine.

Also include spelling mistakes such as 'Clotts', ' Clooths' or 'Clothos' then it should display 'Clothes' related items.

If some clarification is needed, please leave a comment.

I'm using VS2010 C#, MySql 5.5 Database

Dave L.
  • 9,595
  • 7
  • 43
  • 69
Karthik K
  • 21
  • 2
  • 4
    I suggest you first start with simple exact matching, and then build on. We won't write all that code for you. It's a lot of code. – Sergio Tulentsev Dec 28 '11 at 20:07
  • suggest any keyword please. Eg- "Like" matches pattern. but i need similar/near text also..? – Karthik K Dec 28 '11 at 20:10
  • Full-text searching could be what you want, which is available on MyIsam tables. Otherwise you even may want to use a specialized search tools such as solr or lucene. – Pelshoff Dec 28 '11 at 20:12
  • 1
    Google has spent last decade and hundreds of engineers to get where they are now. You're not getting it from a Stack Overflow answer. – Amadan Dec 28 '11 at 20:12
  • @ Amadan : sorry for Misunderstand.. :) basically most of all website use similar method to search a product. so that's where my point stands for. – Karthik K Dec 28 '11 at 20:15
  • You might find the answers to [this question about Google "did you mean" searches](http://stackoverflow.com/questions/307291/how-does-the-google-did-you-mean-algorithm-work) useful. – Matthew Strawbridge Dec 28 '11 at 20:54

1 Answers1

3

When a user enters some text like 'Clothes' then the query should search for the 'Clothes' keyword in the whole database 

SELECT f1,f2,f3 FROM table1 
WHERE match (f1,f2,f3) AGAINST ('$search_term' IN NATURAL LANGUAGE MODE)

Note that in MySQL full-text search requires MyISAM tables.
It also does not fix spelling errors.

Also include spelling mistakes such as 'Clotts', ' Clooths' or 'Clothos' then it should display 'Clothes' related items.

You are looking for a algorithm that includes Levenshtein distance

Some people have written UDF that you can install with MySQL (as a dll on Windows) that gives you this functionality: See: http://samjlevy.com/2011/03/mysql-levenshtein-and-damerau-levenshtein-udfs/
The link even includes a howto on installation of the UDF's.

Johan
  • 74,508
  • 24
  • 191
  • 319