Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how to structure one to many in mysql with php

Posted on 2011-02-27
5
403 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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