[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

how to structure one to many in mysql with php

Hi.
Need to get some advice on the best way to store/structure (one to many) in mysql using php.
I have a products table  like this:
p_id  p_price  p_name  p_supplier  

I created a seperate table to store category names:
id_auto_increment    category_name  

my problem is with the category,  a product can be assign in to many categories so what is the best way and how to store the products in the categories?
0
ikon32
Asked:
ikon32
  • 2
  • 2
1 Solution
 
ThomasianCommented:
That is actually a many to many relationship since each product can be in many categories and each category can have many products.

Products
----------------------------------------
p_id *PK
p_price
p_name
p_supplier  


Categories
----------------------------------------
c_id *PK
category_name


ProductCategories
----------------------------------------
p_id *PK (fk to Products)
c_id *PK (fk to Categories)
0
 
ikon32Author Commented:
could you pls explain what  *PK and fk, stands for
0
 
ThomasianCommented:
PK stands for primary key. It's the unique id of a record in a table.
http://www.1keydata.com/sql/sql-primary-key.html

FK stands for foreign key. It's the field used to relate to another table.
http://www.1keydata.com/sql/sql-foreign-key.html

e.g.
Products
----------------------------------------
p_id     p_name
1        Product1   
2        Product2


Categories
----------------------------------------
c_id     category_name
1        Category1
2        Category2


ProductCategories
----------------------------------------
p_id     c_id
1        1     ->Product1 is assigned to Category 1
1        2     ->Product1 is assigned to Category 2
2        2     ->Product2 is assigned to Category 2

Open in new window

0
 
rfportillaCommented:
*PK: primary key

When you setup the primary key in the ProductCategories table, you will set up two fields as the primary key.  This guarantees that the combination of the two fields will be unique.  

You will have a one-to-many relationship between the Products table and hte ProductCategories table, respectively.  You will also have a one-to-many relationship between the Categories table and hte ProductCategories table.  This will give you the many-to-many relationship that you are looking for.  

If you need more info, google junction tables or mapping tables.

Hope this helps.
0
 
ikon32Author Commented:
thanks  rfportilla, will do
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now