CODEWITHSUNDEEP
phpmysqli
Eoin H
Eoin H

Reputation: 311

Selecting 2 or more rows using an array (PHP, MYSQLi)

Ok, I wasnt sure how to title this.

I have 2 tables, one with products and one with merchants.

Some products can be sold by 2 or more merchants.

The products table has a column called 'product_merchant_id' with 1 or more references in it, like so.. '1237, 38272, 3738'

The id's are related to whats in the 'merchant_id' column in the 'merchants' table.

Merchant Table (merchants)

mer_id | merchant_id | merchant_name
-------------------------------------
1    |    1237     | Merchant One 
2    |    38272    | Merchant Two
3    |    3738     | Merchant Three

Product Table (products)

product_id   |   product_merchant_id   |  product_name
------------------------------------------------------------
    1        |   1237, 38272           |  Sample Product One
    2        |   1237, 3738, 38272     |  Sample Product Two
    3        |   3728                  |  Sample Product Three

So, basically, if I was querying product_id 2, I want to be able to pull 2 rows with the merchant_id's of 1237 & 38272 from the merchant table and loop them in my template, something like...

<div>
    <p>Merchant Name: Merchant One</p>
    <p>Merchant ID: 1237</p>
</div>
<div>
    <p>Merchant Name: Merchant Two</p>
    <p>Merchant ID: 38272</p>
</div>

Upvotes: 2

Views: 52

Answers (3)

Mark Miller
Mark Miller

Reputation: 7447

The solution is to change your table structure. Remove the product_merchant_id column in the Product table, then create a new table, called product_merchants, with two columns: product_id and merchant_id. It will look something like:

product_id | merchant_id
--------------------------
1          |    1237 
1          |    38272
2          |    1237
2          |    2728   
2          |    38272   
3          |    3738

Now, you can use a join to get all the information you need. Something like this should work:

SELECT m.merchant_name, m.merchant_id
FROM merchants m
JOIN product_merchants mp ON m.merchant_id = mp.merchant_id
    AND mp.product_id = 2

See demo

Upvotes: 3

kefy
kefy

Reputation: 535

My advice is you can add one table that like stock table that will be mapped product_id and merchant_id and you will get stock_id it will make easier for development

Upvotes: 2

Coder anonymous
Coder anonymous

Reputation: 927

Something like this can help:

Select * from merchants where merchant_id in ((select product_merchant_id from products where product_id=2))

I believe this should work.

Upvotes: 2

Related Questions