3

I have a little shop and I want to insert my order into the database. For Example:

Order Number: 1

Products:

  • Apple, Price: 2$, Amount: 2, Total Price: 4$
  • Banane, Price: 3$, Amount: 1, Total Price: 3$
  • Orange: Price: 2$, Amount: 2, Total Price: 2$

Total Price: 9$

So I made now in my database a table with 3 rows (order number, products, total price)

But how can I list all my products in my row "products" in my database. Is there something like a subtable in mysql?

Andrey Vorobyev
  • 896
  • 1
  • 10
  • 37
AbsoluteBeginner
  • 53
  • 1
  • 1
  • 4
  • Order number, products, total price are (should be) columns, not rows. The rest of the questions sounds like you should do some research first. – GolezTrol Mar 17 '15 at 18:24

1 Answers1

0

If you want store products and orders in database you must create three tables.

products
  id
  name
  price

orders
  id
  date


orders_products
  id_order
  id_product
  amount

It's very simple schema, but it's simple solution to start understand this

Example data

products
  |id | name   | price|
  |---|--------|------|
  | 1 | Apple  | 1000 |
  | 2 | Orange | 2000 |

orders
  | id | date                |
  |----|---------------------|
  | 1  | 2015-18-03 00:21:00 |

orders_products
  | id_order | id_product | amount|
  |----------|------------|-------|
  |    1     |     1      |   1   |
  |    1     |     2      |   2   |

Show all products

Select * from products

Show order #1

Select * from orders where id = 1

Show all products from order 1

Select * from orders_products op 
join products p on p.id = op.id_product 
where op.id_order = 1

How you see, third table store relation, and with join you may retrieve information about products in order

DANE O
  • 3
  • 2
Andrey Vorobyev
  • 896
  • 1
  • 10
  • 37