示例服装数据库架构。 （我究竟做错了什么？）

Question

So here are my tables for a sample clothing database:

Table Product:

ID | Title | Description
--------------------------------
1  | Shirt | Man's Shirt
2  | Dress | The one that I like

Table Product_Info:

PI_ID | PI_Color | PI_Size | PI_Quantity | PI_Price | PI_RetailPrice
--------------------------------------------------------------------
1     | Blue     | Small   | 3           | 65.00    | 75.00
1     | Yellow   | Medium  | 1           | 55.00    | 60.00
1     | Yellow   | Large   | 2           | 55.00    | 60.00
2     | White    | Large   | 5           | 125.00   | 150.00

So now this is all good but when it comes to displaying it I am having trouble. I want to display all data in a table for administrators to quickly search/edit/delete or add products. How can I display this so that all product details are within 1 row, so goal display should be something like:

Title | Description | Color | Size  | Price
--------------------------------------------------------
Shirt | Man's Shirt | Blue  | Small | 65.00
                      Yellow| Medium| 55.00
                              Large | 55.00

How can I display this with PHP/HTML so that its readable and easy to understand. Do I need to change the way I structured tables? I am not asking for a code I just need an idea on how to display this in an easy way.

Answer 1

If you want to have an easy and nice way to edit/read the table, you have to make some HTML/PHP.

You have to write in your back end some PHP to get the data from the DB. You need a JOIN request:

SELECT * FROM Product p JOIN Product_Info pi ON p.ID=pi.PI_ID

This will get you something like this (with all the columns of your two tables, that I haven't represented).

 ID | Title | Description | PI_ID | Color | Size  | Price
 --------------------------------------------------------
 1  | Shirt | Man's Shirt |  1    | Blue  | Small | 65.00
 1  | Shirt | Man's Shirt |  1    | Yellow| Medium| 55.00
 1  | Shirt | Man's Shirt |  1    | Yellow| Large | 55.00
 2  | Dress | The one ... |  2    | White | Large | 125.00

Then in your HTML front end you need to make a loop to display this information as you want, add and edit button where you want to edit

Then your front end will send the edited data to your back end which will make the UPDATErequest accordingly.