0

I've got a table product and the user needs to be able to search through products using an autocomplete function on a text field. But I want also when people make little mistakes in the name, it gets found aswell.

What is needed

All my titles are formated like this: [brand] [productnamename] [versionname] So for example, I've got a product 'foo evo HD3455' i need to find it when i type 'foo' or 'evo' or 'HD2455' or parts of it, but also when they type 'froo eno HD3455' for example they need to find it aswell

What i have

I have this SQL query:

SELECT * FROM `product` WHERE SOUNDEX(title) LIKE CONCAT('%',SUBSTRING(SOUNDEX('here the search text'),2),'%')

Whats not working

It works for the brands, even if you make a little mistake in the name, but when I search for the [productname] or [versionname], then it does't work. When i type the [brand] first and then the [versionname] the results with the [versionname] in it don't show up.

Where i need it for

I'm going to use it for an autocomplete field, so that users can easily find products, even if they make a mistake in their search text.

I can't use fulltext search because i use innoDB

deacs
  • 4,259
  • 2
  • 27
  • 37
Rickert
  • 1,677
  • 1
  • 16
  • 23
  • `soundex()` isn't particularly useful. What you probably want is Levenshtein distance, a very clever method (described at http://en.wikipedia.org/wiki/Levenshtein_distance). If you Google for Levenshtein and MySQL, I'm sure you'll stumble upon an implementation in the datbase. – Gordon Linoff Mar 17 '14 at 13:42
  • "I can't use fulltext search because i use innoDB" I don't understand this statement. – Strawberry Mar 17 '14 at 13:46
  • @Strawberry innoDB doen't support fulltext search (read it here: http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb) – Rickert Mar 17 '14 at 13:56
  • InnoDB supports FULLTEXT at MySQL 5.6 and above - check the manual at https://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html – D Mac Mar 17 '14 at 14:00
  • @DMac i have 5.5.13 so its not suported – Rickert Mar 17 '14 at 14:14
  • 1
    5.6 has been around since 2011. Maybe it's time to upgrade! – Strawberry Mar 17 '14 at 14:19

0 Answers0