PHP & MySQL One item in multiple categories

Question

I try to select all the items from stores table that can belong to multiple categories

This is my table structure:

stores table

id
name

categories table

id
name

store_category table

store_id
category_id

I've tried the folowing query but it doesn't work as i wanted:

SELECT * FROM (stores JOIN store_category ON stores.id = store_category.store_id) JOIN categories ON store_category.category_id = categories.id

What i want to do is like:

Select all stores that have category_id = php var $category_id set in store_category

*added as pseudocode

How can i do this?

PS: example of populated tables

`stores`

id  |  name
1   |  amazon
1   |  aliexpress


`categories`

id  | name
1   | smartphones
2   | fashion

`store_category`

store_id  |  category_id
1         |  1
1         |  2
2         |  1
2         |  2

From this table design i want to express that both stores are included in the two categories

Do you have any kind of php script at all? E.g. setting up a database connection and issuing some kind of sql query. Or do you want a complete script? — maxhb
– maxhb, Commented Nov 10, 2015 at 20:10
I have thr script & database connection. I just can't select the query based on that condition. — m3tsys
– m3tsys, Commented Nov 10, 2015 at 20:16

Kostas Mitsarakis · Accepted Answer · 2015-11-10 20:08:13Z

1

If you want to select all the stores that belong to the category with name 'some_category' then you can try this:

SELECT aa.id, aa.name
FROM stores AS aa
INNER JOIN store_category AS bb
ON aa.id = bb.store_id
INNER JOIN category AS cc
ON bb.category_id = cc.id
WHERE cc.name = 'some_category';

edited Nov 10, 2015 at 20:08

answered Nov 10, 2015 at 20:02

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

m3tsys Over a year ago

Thank you. This is what i was looking for.

Bernd Buffen · Accepted Answer · 2015-11-10 21:10:09Z

1

In this Sample i get first all row with the category name and then i join the stores. So you not must query all stores with there categories

SELECT s.name
FROM store_category AS sc
LEFT JOIN stores AS s ON sc.store_id = c.id
WHERE sc.category_id = 2;

It not testet. please let me now if it works for you.

edited Nov 10, 2015 at 21:10

answered Nov 10, 2015 at 20:16

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

3 Comments

m3tsys Over a year ago

it doesn't work. Let me explain again: I want to select id & name from stores where category_id from store_category = $category_id php variable also asigned to store_id

m3tsys Over a year ago

please see my updated question with example of table design

Bernd Buffen Over a year ago

i have edit my anwser. so it easy to get all stores where have a category. in my sample i use cat_id 2

Vineet1982 · Accepted Answer · 2015-11-10 20:11:01Z

0

You have to simply do this:

SELECT table1.*, table2.*, table3.* from table1, table2, table3
   where table1.id=table2.id and table2.id=table3.id and 
   table3.id=table1.id;

answered Nov 10, 2015 at 20:11

Vineet1982

7,9285 gold badges34 silver badges70 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL One item in multiple categories

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related