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
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.
For me, the best way is , add all match possibility keywords (shoes, footwear, men dress and etc.) to all products (to keywords column) when you add to base , which user will be search. And search products from this column(or table).