From a nonstandard string, sometimes I get the department / category equivalent, sometimes equivalent to category / subcategory, sometimes equivalent to department / subcategory, sometimes department / category / subcategory ... etc. .
I need to create a SQL query for a table standardized with department, category and subcategory to return the possible categories that most match the received string.
I'm breaking my head but I have not found an efficient way to do this kind of query yet.
Would anyone help me think of an efficient algorithm that can solve this kind of problem?
Example
string recebida = Camisetas/Masculino
Example of a MySQL table row:
--------------------------------------------------------
| Departamento | Categoria | Subcategoria |
--------------------------------------------------------
| Moda e Acessórios | Roupas Masculinas | Camisetas |
--------------------------------------------------------
I started the PHP script with an explode in the received string to get the first item from the array as a department, the second as a category and the third (when it exists) as a subcategory, but it does not seem like a good way.
Then it inflects the terms.
So finally I make a comparison using the native PHP function similar_text ()
But I'm not having very satisfactory results.
Is there a pattern for these cases? Any ideas will be very welcome.