I have a column in my MySQL table. The column name is "attached_products", now, for example I have some records there and the attached_products have a value "1,16,164,1645", how do I select all records with "16"?
I tried the where clause, it went something like:
select * from websites where attached_products like '%16%'
but it also select the records with 164 and 21645.
Thanks for any help! :)
since you are using a bad design for your database you could use, like you said:
SELECT * FROM `websites` WHERE (`attached_products` LIKE '%,16,%' OR `attached_products` LIKE '16,%' OR `attached_products` LIKE '%,16' OR `attached_products`='16');
but you should consider redesigning your database for easier selects. You can add another table with only those attached products like:
table 1
id | etc ... | etc .... |
attached_products
id | id_product | product
where id_product is from table 1 and product is the product id
You could try select * from websites where attached_products like '%,16,%'
A better solution would be to create a new table to store this relationship (containing the product and attached product IDs)
Try this
WHERE FIND_IN_SET('16', attached_products);
select * from websites where (select substr(attached_products, ',')) = 16. But the good solution is redesigning