product search by Category

Question

I want to do search where c_id 3 then i will get product2. Again If i search c_id 1 then i will get product1, product3

Here is my table structure

p_id    p_name     c_id
------------------------
      |          | 
1     | Product1 |   1
      |          |
2     | Product2 |   2,3,4
      |          |
2     | Product3 |   1,2

Fix your data model so you have a proper junction/association table. Do not store numeric ids as strings. — Gordon Linoff, Jul 30 '19 at 14:23
@A.Developer you should have a relational database, should make things better. You can have a table that links `p_id` with `c_id`. Also if this is your "product" category, I'm assuming, you should have a unique ID for each record. — M. Suleiman, Jul 30 '19 at 14:30
Possible duplicate of [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) — Sayed Mohd Ali, Jul 30 '19 at 14:44
Hell, I would like to say that it is the wrong way of storing data. you are not following normalization and you need to learn normalization rules. you may be able to solve the problem but in future when your project reaches at higher level you will face many issues. — Sayed Mohd Ali, Jul 30 '19 at 14:44

score 2 · Accepted Answer · edited Jul 30 '19 at 14:38

2

You can use find_in_set(),

select * from products where find_in_set(1,c_id);

OR

select * from products where find_in_set(3,c_id);

SQL DEMO

edited Jul 30 '19 at 14:38

Juan Carlos Oropeza

47,252
12
78
118

answered Jul 30 '19 at 14:35

Rahul

18,271
7
41
60

1

lol few month ago, I solved similar question and it was marked as duplicate and said one of the moderator told me instead of giving fish to the OP teach him how to catch a fish... lol I raised a question on stack exchange and everyone said it was right ... and now I am looking for the similar catch and no issue no duplicate nothing... – Sayed Mohd Ali Jul 30 '19 at 14:47
Full of as such baffles aye! A fortunate question is sometimes :D. Go with the flow mate ;). – Rahul Jul 30 '19 at 14:52
1

yes, I raised an issue on meta exchange why it is marked as a duplicate of theory-based question normalization... even on meta I got negative votes :P. and both the meta question and the one on stackoverflow got deleted. I wished i took a screenshot then I able to show it to you :P... i know there is nothing wrong with this question and your answer but I want to blacklist those fools :D – Sayed Mohd Ali Jul 30 '19 at 14:54

Juan Carlos Oropeza · Answer 2 · 2019-07-30T14:35:37.933

0

SQL DEMO

  SELECT *
  FROM YourTable
  WHERE CONCAT(',' , c_id, ',') like CONCAT('%,', @search_id, ',%')

edited Jul 30 '19 at 14:35

answered Jul 30 '19 at 14:25

Juan Carlos Oropeza

47,252
12
78
118

But as Gordon said this can only work with one c_id. is better fix your model for the long run. – Juan Carlos Oropeza Jul 30 '19 at 14:26
not working I got a message unknown column `,%` in where clause – A.Developer Jul 30 '19 at 14:41
Yes I update the answer. I use back ticks instead of single quotes for the second % – Juan Carlos Oropeza Jul 30 '19 at 14:44

score 0 · Answer 3 · answered Jul 30 '19 at 14:36

0

Using FIND_IN_SET could be an option in the meantime.

SELECT *
FROM Table_Name
WHERE FIND_IN_SET('specific c_id you are searching for', c_id);

answered Jul 30 '19 at 14:36

Radagast

5,102
3
12
27

product search by Category

3 Answers3