PHP-MySQL 3 tables select, update, delete and insert linked with Foreign Key

Question

I have a problem with 3 InnoDB tables in MySQL linked with a foreign key, My tables are:

Table1 name - products

PID(PK, bigint, auto inc), 
CATALOG_NO, 
PRODUCT_NAME, 
COMPOSITION, 
SIZE, 
PRICE, 
SUBCAT_ID(foreign Key, index, bigint, keyname-FK_products_1)

Table2 name - subcategory

SUBCAT_ID(PKey, bigint, auto inc),
SUBCATEGORY_NAME, CAT_ID(fkey, bigint)


Table3 name - category

CAT_ID(Pkey, bigint, auot inc),
CAT_NAME

1.What is the right query to SELECT and list the data by joining 3 tables which should display result as:

ProductsPID, CATALOG_NO, PRODUCT_NAME, COMPO, SIZE, PRICE, SUBCATEGORY_NAME, CAT_NAME

2.What is the correct way to UPDATE and DELETE the above joined records by using one single query through one single form?

3.Is it possible to insert the records also through single query by using single html form containing fields such as product name (input type=text), catalog no (input type=text), composition (input type=text), size (input type=text), price (input type=text), Choose subcategory (select type field with options), category (select type field with options) if yes then how?

PLEASE HELP, ITS URGENT.

bw_üezi · Accepted Answer · 2011-03-28 22:26:34Z

0

SELECT
  p.PID AS ProductsPID,
  p.CATALOG_NO, 
  p.PRODUCT_NAME, 
  p.COMPOSITION AS COMPO, 
  p.SIZE, 
  p.PRICE, 
  s.SUBCATEGORY_NAME,
  c.CAT_NAME
FROM products p
  INNER JOIN subcategory s
    ON p.SUBCAT_ID = s.SUBCAT_ID
  INNER JOIN category c
    ON s.CAT_ID = c.CAT_ID

update and delete can also use join in some cases (not on foreignkeys) but you probably only need to change the product table. insert has to be on the individual tables.

edited Mar 28, 2011 at 22:26

answered Mar 28, 2011 at 21:50

bw_üezi

4,6044 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Henry Over a year ago

Hi Thanks for your precious help, am able to display the results properly but my main problem is of update and delete, by using a form, how can i update products table which will also change the subcategory and category of a particular product as i want to give user an option to select cat n subcat from a drop down list? Same how can I add a product in the product table by using a form, form which will have user input fields- product name, catalog no, compo, size, price, subcategory(pre-populated drop down list to select an option) and category(pre-populated drop down list to select an option)

bw_üezi Over a year ago

With your current tables the category is implicit given by the selected subcategory. But as it looks this design is not what you want. You'll probably have to move the CAT_ID column from table subcategory to table products.

Henry Over a year ago

can i do this through php or do i have to do this manually by using phpmyadmin panel, how is it possible to move that column without loosing or disturbing the data.

bw_üezi Over a year ago

It's just an idea to change the table structure... You should consider pros and cons before doing it. moving column can be done by 1. create new column, 2. copy data using e.g. insert statement with subquery, 3. after verification remove old column.

Collectives™ on Stack Overflow

PHP-MySQL 3 tables select, update, delete and insert linked with Foreign Key

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related