MySQL Task Information

Do not reproduct this page, Your page to reproduce can be found here


You are given three tables of information: companies,products, inventory
See the table at right for details on the table structure
The company table contains information about all the companies
The products table contains information about all the products
The inventory table connects the two. Each row contains both the company ID and product ID it is linked to.

That is to say that "Bread" (prod_id = 4) can be found in many rows for different companies
And that "Massa Ltd" (comp_id = 11) can be found in many rows for different products
Each row contains both the product ID and the company ID, as well as lists the price and quantity for this product at this company.
For a more detailed example:

the first row of inventory has the following details:
inv_comp_id = 12 (meaning it belongs to company ID 12 --> Carp Farmer's Market)
inv_prod_id = 1 (meaning is is product ID 1 --> Mackerel)
inv_pricePerUnit = 1.04
inv_quantity = 22

Which is to say that the Carp Farmer's Market is selling Mackarel for $1.04 per unit, and they have 22 of them

All of your work will be stored in the folder /var/www/html/students/studentFolder/XXXXXX/sqlTestPractice/
An appropriate database.php will already be found there
You each have your own database, nothing you do will impact anyone else's work

Take a good look at the proceeding page to make sure you are reproducing its functionality correctly.

Specifications broken down by level:

As always, the raw level of your mark is determined via functionality as defined below.
Code commenting, formatting, variable/function naming and efficiency determines +/-

Level 1

  • Read from the 'companies' table to display a list of companies, sorted and displayed the same way as shown.
  • Have each company's name be a link to view their inventory (a link to 'getInventory.php')
  • This new page should at least list all the matching entries for that company in the 'inventory' table in some way
Level 2
  • All requirements from Level 1
  • The 'getInventory.php' page should accurately display all the product names for that company's inventory
    • NOTE: Do not worry about sorting this list
  • Each inventory item there should have an 'EDIT' link, which is a link to 'editInventory.php'
Level 3
  • All requirements from Level 2
  • Provide a working 'EDIT' link that brings the user to a populated form (as shown in the example)
    • NOTE: Only quantity and price need be listed in the form.
    • Unless you are implementing the level 4 requirements, do not make the dropdown box for Companies at all.
  • Buttons must work as shown on 'getInventory.php' and 'editInventory.php'
  • When the user clicks 'Save Changes'
    • Have the information be updated in the database
    • The user should be redirected to the index page
Level 4
  • All requirements from Level 3
  • On the 'editInventory.php' page, the user should be given the chance to also edit which company this inventory belongs to.

Database Information

| Tables_in_ajaxtest |
| companies          |
| inventory          |
| products           |

companies table
| Field       | Type         |
| comp_id     | int(11)      |
| comp_name   | varchar(255) |
| comp_street | varchar(255) |
| comp_city   | varchar(255) |
| comp_prov   | varchar(32)  |
| comp_postal | varchar(16)  |
| comp_phone  | varchar(16)  |

products table
| Field     | Type         |
| prod_id   | int(11)      |
| prod_name | varchar(255) |

inventory table
| Field            | Type    |
| inv_id           | int(11) |
| inv_comp_id      | int(11) |
| inv_prod_id      | int(11) |
| inv_pricePerUnit | double  |
| inv_quantity     | int(11) |