Homework 5
Due: July 4,
Use the following commands in mysql to set up your database:
use [accountname];
source ~avitabij/mysql/hw5script;
The script is:
DROP TABLE PRODUCTS;
DROP TABLE ORDERS;
DROP TABLE
ORDERITEMS;
CREATE TABLE ORDERS
(ORDERID INTEGER NOT NULL,
CUSTOMER VARCHAR(20)
NOT NULL,
ADDRESS VARCHAR(20)
NOT NULL,
CITY VARCHAR(20)
NOT NULL,
TIMESTAMP VARCHAR(30),
CONSTRAINT pk
PRIMARY KEY (ORDERID));
CREATE TABLE
PRODUCTS
(PRODUCTID INTEGER NOT NULL,
DESCRIPTION VARCHAR(40)
NOT NULL,
PRICE
FLOAT NOT NULL,
QUANTITY INTEGER NOT NULL,
CONSTRAINT pk
PRIMARY KEY (PRODUCTID));
CREATE TABLE
ORDERITEMS
(ORDERID INTEGER NOT NULL,
PRODUCTID INTEGER NOT NULL,
CONSTRAINT pk
PRIMARY KEY (ORDERID, PRODUCTID),
CONSTRAINT fk1 FOREIGN KEY (ORDERID)
REFERENCES ORDERS(ORDERID),
CONSTRAINT fk2 FOREIGN KEY (ORDERID)
REFERENCES PRODUCTS(PRODUCTID)
);
INSERT INTO ORDERS
VALUES
(1, 'jack', '10
green street', '
INSERT INTO ORDERS
VALUES
(2, 'jill', '8 brown street', 'troy', '6/22/2006');
INSERT INTO PRODUCTS
VALUES
(1, 'shirt', 9.95,
3);
INSERT INTO PRODUCTS
VALUES
(2, 'hat', 19.95,
0);
INSERT INTO PRODUCTS
VALUES
(3, 'sweatshirt',
29.95, 5);
INSERT INTO PRODUCTS
VALUES
(4, 'book', 14.95,
7);
INSERT INTO
ORDERITEMS VALUES
(1, 2);
INSERT INTO
ORDERITEMS VALUES
(1, 3);
INSERT INTO
ORDERITEMS VALUES
(2, 3);
You can write your own script or modify this one if you like, but you do need the three tables.
Have a php file named hw5a.php that will display the contents of all three tables.
Have a php file named hw5b.php for an order form that will display in a table the following columns: Product ID, Description, Price, and Number Ordered (using a select). Only display product names for products in stock, and have the select with values from 0 to the number in stock. Also have a way for the user to enter their name, address, and city. Have a submit button to go to hw5c.php.
hw5c.php will update database tables (insert the appropriate records into ORDERS and ORDERITEMS, and update the PRODUCTS table) and display an invoice that could be printed (price, total, etc.).
And also have a file hw5d.htm that lets the user enter information for a new product in a form, and sends the data to a file hw5d.php. This php file adds a new product if that product doesn’t already exist. The appropriate response is generated.