Solved

how to structure one to many in mysql with php

Posted on 2011-02-27
5
400 Views
Last Modified: 2012-05-11
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
Comment
Question by:ikon32
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 34994149
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
 

Author Comment

by:ikon32
ID: 34994184
could you pls explain what  *PK and fk, stands for
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 34994210
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
 
LVL 9

Expert Comment

by:rfportilla
ID: 34994218
*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
 

Author Comment

by:ikon32
ID: 34994232
thanks  rfportilla, will do
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Constructing a Query Using Escaped Variables 3 43
MySQL not outputting all Emojis in text field when using UTF8MB4 5 42
Clean text to insert in database 9 52
join tables 4 51
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now