在php中的两个表之间建立连接

Question

i am creating a basic insert product page in ecommerce website(admin panel)

where i have a table category (with male or female) then a man table in which i put product category like grooming kit watches etc same in female table (makeup item and dresses etc)

and third table product where all product where all products would be inserted okay

so now i want to link them in such a way that (suppose a drop down interface)

in category i select male then it take id of male from "category" table suppose it 1 and then from another dropdown of "male" table if i select grooming kit which has id suppose 1 there the product table take these id's from these table automatically... for example i am describing table structure

CATEGORY
CAT_ID   CAT_TITLE      *(cat_id to auto increment so if i add kids = 3 )
1            MALE
2           FEMALE

MALE
M_ID      M_TITLE     *M_ID auto increment
1           GROOMING KIT
2           WATCHES

FEMALE                *f_id auto increment
F_ID      F_TITLE
1          DRESSES
2          SHOES

product p_id(*auto increment) CAT_ID(*must auto matically take id from category) g_id(*must take id from male or female title)

so that i know where to insert product on page

Answer 1

I see a problem with the database structure, even before we get into coding.

Remove the MALE and FEMALE tables and use only the PRODUCT table. Then add a foreign key from PRODUCT to CATEGORY. Like so:

CATEGORY
Cat_id: primary key
Cat_title: male or female

PRODUCT
Product_id: primary key
Product_name
Product_price
... other product fields ...
Product_Cat: foreign key to CATEGORY:Cat_id

This way you will be able to work on all your products from a single table (ex. sort by price, without regards to the Category).

In your queries you will have to join PRODUCT and CATEGORY together to get male or female products only.

Also if you add a new Category, you will not have to create new tables, just add products with the new Cat_id.

The advantages for the future are plentiful!