Search for synonyms in php sql database

Question

I am searching for string in mysql database in php,which i m doing with like keyword.The problem is when i am having synonyms entered from user.Like in the database i m having all the products with the name shoes,and user enters footwear or some mismatched words.how to search for these different conditions.. Please guide on this.

Currently i m using following select query

Select * from table where name like '%user entered string%'

Please guide on how to tackle these conditions

Answer 1

Let's sum it up.

Let's assume that you have a table products which, I highly assume and hope, consists of products with unique ids.

products

id     product
1      Shoes
2      Trousers

And so on. If you were to add an another column, let's say, synonyms, it would look something like this:

products

id     product    synonyms
1      Shoes      Footwear, stuff on feet, 
2      Trousers   

We don't want this. You'd have to select the synonyms column, parse the string and make sure you don't ruin the column when you somehow wish to add new synonyms to each product.

It makes much more sense to have an atomic database where you'd have a table of synonyms where each synonym references a unique id in your products table, this way it's easy to delete old synonyms and add new ones.

products

id     product
1      Shoes
2      Trousers

synonyms
id     product_id     synonym
1      1              Footwear
2      1              stuff on feet

You can then look up in this table if the original select like statement fails.

Using an external data-source is also a possibility but this is probably the most suitable way to go if you want to control the flow and avoid external sources.