CODEWITHSUNDEEP
phpmysqlforeign-keys
PHPNewbie
PHPNewbie

Reputation: 35

PHP MySQL add another row function

I am starting to learn PHP;

i have two tables in my database:

  1. the Brands List that contains Brand_ID, Brand_Name, and Brand_Description.
  2. the Product Lines List that contains Line_ID, Line_Name.

i want to create a third table named Offered Products that contains Offer_Id, Brand_ID, Line_Name - how can i manage this using Foreign Keys?

After creating the table, i want to create a php page that will let users to populate the third table by

  1. Selecting a brand from a dropdown list
  2. Selecting a Line Name from a drop down List
  3. Beside it has a button "Add Row" - which when click will display another row below the first one in order to do number 1 and 2 again and so on
  4. A save button is also present which when clicked will finally save the record on my database.

how can i exactlydo this? please help

Upvotes: 1

Views: 380

Answers (1)

diolemo
diolemo

Reputation: 2671

Creating the table.

Your third table would only need to store Line_ID and Brand_ID and (for whatever reason) you also want Offer_ID. 

CREATE TABLE `Offered Products` (
    /* define columns */
    Offer_ID  INT NOT NULL AUTO_INCREMENT,
    Brand_ID  INT NOT NULL,
    Line_ID   INT NOT NULL,

    /* define primary key */
    PRIMARY KEY (Offer_ID)

    /* define foreign keys */
    FOREIGN KEY Brand_ID (Brand_ID)
        REFERENCES `Brands List` (Brand_ID)
    FOREIGN KEY Line_ID (Line_ID)
        REFERENCES `Lines List` (Line_ID)
);

This assumes that the Lines List table (with a space) and Brand List table have Line_ID and Brand_ID respectively defined as primary keys.

Creating the form.

Design the form with HTML and PHP. Have each list populated from the corresponding table. I'm not going to provide code for this; it should be straight forward.

Inserting into the table.

INSERT INTO `Offered Products` (Brand_ID, Line_ID) VALUES (###, ###)

The ### represents the ID numbers from the HTML form.

Joining the tables.

To obtain information from all of the tables you can join as below. 

SELECT * FROM `Offered Products` as op 
    JOIN `Brand List` as bl ON bl.Brand_ID = op.Brand_ID
    JOIN `Line List` as ll on ll.Line_ID = op.Line_ID

Other Notes.

To use foreign keys in MySQL you need to be using the InnoDB engine. Using myisam will not allow foreign keys, but you can still join the tables as demonstrated to achieve a similar result.

Avoid using spaces in your table names if you actually did that.

Upvotes: 1

Related Questions