How to link products with merchants without hundreds of queries to sql

quecoder

Hello !
I have a big list of merchants ( 1300+ ) , each one has , website & address ...etc and his combination of products he is selling .. I got a unique list of products ( 3000+) that all merchants share ..
some share ( provide ) 3 products , and up to 100..
I have the list of merchants stored properly in a MySQL database , but the problem is , the list of products is just a list of products ordered alphabetically ..

**I want my visitors , besides browsing my list of merchants and read their data and products , to search , as well , for merchants by browsing these products keywords and when someone clicks on one , it shows him a list of merchants who provide this product ...
Thanks alot 🙂

blackhorse

3 tables

products

productsID, ...

merchants

merchantsID

products-merchants

productsID, merchantsId, ...

quecoder

blackhorse wrote:
3 tables

products

productsID, ...

merchants

merchantsID

products-merchants

productsID, merchantsId, ...

Yes , I have two tables now , Merchants & Products ... now , how to make the third table providing that my first table :
*1- Merchants table : [MechantId , Address , Website , eMail, products]
products field : has the products separated by commas

*2- Products table :[productID, ProductName]
ProductName has one product per record

*3: the third table you are telling me to make, needs to connect products with possible merchants that sell it ... how to search for them for each product and then inserting MerchantIDs in a new field in the 2nd table ??

And that was my first question

Thanks for your help anyway

quecoder

ah you mean a third table that links merchant ID with product ID.. but still , how to make it

ucffool

You would simply have a table with:
pkey - merchantid - productid

Then, you would have an entry in the database for every product they carry:

1 - 10 - 21342
2 - 10 - 42423
3 - 10 - 42513
and so on...

Then query the database for all entries from a particular merchant id (you can also do a JOIN of merchants.id = products_merchants.merchantid) and display all their products.

blackhorse

yeah, for example when you manage the merchant with merchantID 10,

Pick up merchant (id 10) first. And go to a page, list the products, each item with a checkbox. if this merchant has this product, the checkbox is checked. You can turn the checkbox on and off and save your changes.

bogu

quecoder wrote:
*3: the third table you are telling me to make, needs to connect products with possible merchants that sell it ...

If your DB tables have a n:m relationship then you need a third table to link those 2 tables, if your relationship is 1:n then you don't need the third table ...