I am creating a table named company_Registration which stores
company_id
company_name
product_escription
---using php and mysql---
I want product_description column should take multiple values e.g IC chips, motors
and should be separated by commas ..
I know how to import .csv files to database and is working properly..
But, I want when user search in webpage then product_description column values should be treated as separated by commas how to achieve?
While it is possible to search for a match on a comma separated list (using FIND_IN_SET for example), it is nasty code to read, inefficient, prevents referential integrity and is generally a really bad idea (although not quite as bad as storing a php serialized array in a php field).
At least create an extra table of descriptions, with 1 row for each description for each company.
Possibly better is have a table of possible descriptions, then a link table with many rows per company, one for each description. The link table rows just contain the unique id of the company and the unique id of the description.
For example, simple tables
CompanyTable
CompanyId
CompanyName
ProductTable
ProductId
ProductDescription
CompanyProductLink
CompanyId
ProductId
Then to find all the companies who sell 'IC chips' or 'sound system' :-
SELECT a.CompanyId, a.CompanyName, COUNT(c.ProductId)
FROM Company a
INNER JOIN CompanyProductLink b ON a.CompanyId = b.CompanyId
INNER JOIN ProductTable c ON b.ProductId = c.ProductId AND c.ProductDescription IN ('IC chips','sound system')
GROUP BY a.CompanyId, a.CompanyName
If you wanted companies who sell just 1 of either 'IC chips' or 'sound system' :-
SELECT a.CompanyId, a.CompanyName, COUNT(c.ProductId) AS ProductCount
FROM Company a
LEFT OUTER JOIN CompanyProductLink b ON a.CompanyId = b.CompanyId
LEFT OUTER JOIN ProductTable c ON b.ProductId = c.ProductId AND c.ProductDescription IN ('IC chips','sound system')
GROUP BY a.CompanyId, a.CompanyName
HAVING ProductCount = 1