CIS-1152 Lab #10: FoodTracker

In this lab you will write a PHP application that makes use of the FoodTracker database on Lemuria.

Part 1: Data Entry

Everything below refers to the pccFoodTracker database on Lemuria. Do the following steps:

Add yourself to the data_user table as a food_user. Here is the schema of the data_user table:
```
    CREATE TABLE data_user(
	    id            INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	    user_name     CHAR(16),
	    password_hash VARCHAR(256),
	    user_role     CHAR(18) CHECK(user_role IN ('food_administrator', 'food_user', 'food_researcher')),
	    full_name     VARCHAR(64),
	    email         VARCHAR(64)
    );
  
```
To do this, create a page containing a form that allows you to enter the necessary user information, including a password. Use your username on Lemuria for the user_name field. When the form is submitted, process the form data using PHP that first connects to the database as user jjones (password frenchfry), and then executes the query "SET ROLE food_admin". This will put you into a role that allows you to make arbitrary changes to the tables.

Next, issue an INSERT query to add the information in the form to the data_user table.

Note: If you see an error about insufficient permission related to a "sequence object" let me know right away. I may have to make an adjustment to food_admin's permission settings.
Create a page that allows a user to log into the FoodTracker system and then presents a form that allows the user to create a diary entry. In particular, that form should allow the user to enter the name of the food, the number of servings, and the date when the food was eaten (any reasonable date format will work in the INSERT command). Here is the schema for the diary table:
```
    CREATE TABLE diary(
      id                 INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      user_id            INTEGER NOT NULL REFERENCES data_user(id),
      food_id            INTEGER REFERENCES food(id),
      recipe_id          INTEGER REFERENCES recipe(id),
      number_of_servings NUMERIC(9, 3),
      eaten_on           DATE NOT NULL
      CONSTRAINT food_or_recipe_null CHECK    (food_id IS NULL OR  recipe_id IS NULL)
      CONSTRAINT not_both_null       CHECK(NOT(food_id IS NULL AND recipe_id IS NULL))
    );
    
```
For this assignment ignore recipes (store NULL into the recipe_id field). You will need to look up the food_id for the food named by the user in the food table. That table is fairly wide, but you can ignore nutritional information for now. You only need to locate the food id based on the food's name. Here is the schema for the food table:
```
    CREATE TABLE food(
      id           INTEGER PRIMARY KEY,
      food_name    VARCHAR(256)     NOT NULL,
      serving_size FLOAT            NOT NULL,
      serving_size_unit VARCHAR(64) NOT NULL,

      kcals        FLOAT CHECK(kcals >= 0),     -- (kcals)
      sugar        FLOAT CHECK(sugar >= 0),     -- (g)
      fat          FLOAT CHECK(fat   >= 0),     -- (g)
      carbs        FLOAT CHECK(carbs >= 0),     -- (g)
      protein      FLOAT CHECK(protein >= 0),   -- (g)
      Na           FLOAT CHECK(Na   >= 0),      -- (mg)
      Ca           FLOAT CHECK(Ca   >= 0),      -- (mg)
      Fe           FLOAT CHECK(Fe   >= 0),      -- (mg)
      vitamin_A    FLOAT CHECK(vitamin_A >= 0), -- (IU)
      vitamin_C    FLOAT CHECK(vitamin_C >= 0), -- (mg)
      vitamin_D    FLOAT CHECK(vitamin_D >= 0)  -- (IU)
    );
    
```
In the food table, the serving_size and serving_size_unit are only to provide context for the nutritional data. The number of servings entered by the user is in terms of the service size in the food table, but that is not relevant for this assignment.

Use PHP sessions to communicate the user ID of the logged-in user from the login page to the page where you present the form for the diary entry. Although it is not required to display the diary for this assignment, you can check that your data was entered properly using the following SQL query:
```
      SELECT *
      FROM   diary
      WHERE  user_id = current_user_id;
    
```
Here "current_user_id" is the ID of the currently logged-in user. Hard-code that value for this test.

NOTE! The data in the food table does not have unique names. For purposes of testing, use the food name '4% COTTAGE CHEESE' (use capital letters), and use the lowest ID value returned. For example:
```
      SELECT *
      FROM   food
      WHERE  food_name = '4% COTTAGE CHEESE'
      ORDER BY id;
    
```
... except use the value from the form instead of hard coding the food name. Choose the first ID returned and ignore the others.

Submit the HTML and PHP documents you created for this lab to Canvas. This lab is worth 20 points.