Exercise 1: Creating/Importing Data

We have provided a starting .sql file of products for our fictional e-commerce store.

1. Create a new database called bmstore in phpMyAdmin.
2. Download setup-inventory.sql and import it in this new database. You should see a number of "Query OK" messages appear.

Using phpMyAdmin, what are two ways in we can check whether the Inventory table was successfully populated in our bmstore database? Hint: You should see 119 rows in the result table upon success.

Exercise 1: Finishing common.php

As discussed in lecture, it's good practice to have a PHP file to factor out shared settings for your database connection from PHP (and any other shared code across multiple PHP files). We have provided a common.phps (save as a .php file, not .phps) to get started.

This one is very similar to the one from lecture, but factors out the PDO construction with a function you can call to get a connected PDO object, and also includes a helper function for handling errors. Recall that in order to work properly, we need the port number to match that of our local server (you can see the port number for MAMP on the home page when you start a MAMP server session).

You can link to a common.php file using include("common.php") in each PHP file you write (in the same directory) that uses it.

Exercise 2: Writing SQL SELECT Queries in PHP with PDO

In this task, we will start our PHP web service to use our SQL data for the Bricker and Morter store and display data about the items in our Inventory table.

• Write a new PHP file called bmstore.php. At the top of the file, include your common.php and create a new PDO object with the provided get_PDO() function. Note: Why don't we need to set error_handling again in this PHP file?

Exercise 2: SQL Query for Item Data

Once you have a PHP file started with your common.php included, it's a good time to use our PDO with a SQL query (and make sure we have successfully connected with the PDO object settings).

First, write the SQL query to find the name and price of all items in the Inventory table. Test your query in the phpMyAdmin SQL console (you should get 119 rows in the response).

Exercise 2: SQL Query for Item Data (Continued)

Once we have our SQL query working in the console, we'll use our PDO object in PHP to handle a GET request for price data.

• Whenever a GET parameter called mode is passed with the value prices, a function called get_item_prices should be called to accept a PDO object as a parameter and use our SQL query to print out a plain text response of all of the items and their prices in the Inventory table.

More details about this function are in the slide below.

Exercise 2: SQL Query for Item Data (continued)

• If the query is successful, your function should print data (as plain text) for each row on a new line in the format "ITEM_NAME ($ITEM_PRICE)" where ITEM_PRICE is the $US decimal format for the price of the item. You can use number_format($str, 2) to print a PHP variable with a precision of 2 decimals.
• Once you have the response printed out correctly when visiting bmstore.php?mode=prices, use the provided handle_error function available from common.php to catch any PDO exceptions with an error message "Cannot query from the database." Note that you can pass in a second optional parameter to this function to pass in the $ex variable in the "catch" statement, which is helpful to print more details about the PDOException.

Exercise 2: SQL Query for Item Data (Solution)

<?php
  include ("common.php");
  $db = get_PDO();
  if (isset($_GET["mode"]) && $_GET["mode"] === "prices") {
    get_price_data($db);
  } else {
    handle_error("Missing required mode=prices GET parameter.");
  }

  function get_item_prices($db) {
    try {
      $rows = $db->query("SELECT name, price FROM Inventory");
      header("Content-type: text/plain");
      foreach ($rows as $row) {
        $price = number_format($row['price'], 2);
        echo "{$row['name']} ({$price})\n";
      }
    } catch (PDOException $ex) {
      handle_error("Cannot query from database", $ex);
    }
  }
?>

PHP

Exercise 2: Improving Response Format

Once we've successfully printed each item and its price on a new line as plain text, let's change the function to print a JSON-encoded version of the data:

[
  { 
    name : ITEM_NAME, 
    price : ITEM_PRICE 
  },
  { 
    name : ITEM_NAME, 
    price : ITEM_PRICE 
  },
  ...
]

JSON (schema)

You can convert a string to a decimal using doubleval in PHP, which you may find helpful since the query results will return string values for each row.

Exercise 2: JSON Solution for get_item_prices

function get_item_prices($db) {
  try {
    $rows = $db->query("SELECT name, price FROM Inventory;");
    
    $output = array();
    foreach($rows as $row) {
      $item_data = array();
      $item_data["name"] = $row["name"];
      $item_data["price"] = doubleval($row["price"]);
      array_push($output, $item_data);
    }
    header("Content-Type: application/json");
    print(json_encode($output));
  } catch (PDOException $ex) {
    handle_error("Cannot query the database", $ex);
  }
}

PHP

Exercise 2: Adding a Second (Filtered) SQL Query

Add another function named get_category_data similar to get_item_prices but which takes a second parameter $category and returns more information for each product sharing that catogory (case in-sensitive) in the following format:

              [
                { name : ITEM_NAME, category : CATEGORY, subcategory : SUBCATEGORY,
                  price : ITEM_PRICE, cost : ITEM_COST, 
                  description : ITEM_DESCRIPTION},
                { name : ITEM_NAME, category : CATEGORY, subcategory : SUBCATEGORY,
                  price : ITEM_PRICE, cost : ITEM_COST, 
                  description : ITEM_DESCRIPTION},
                   ...
              ]
            

JSON (schema)

Some products do not have descriptions (they are NULL in the SQL table). Omit any description key/values for such items in your result. Call this function when the required mode parameter is passed as alldata and a second category parameter is passed with a category name.

Exercise 3: Creating a New MyCart Table

Next, we will get practice creating our own SQL table to store data bout a user's "shopping cart". In your CP5, you have been asked to create your own table(s), and it's important (and fun!) to think about how to design/structure each in a database.

• What type of columns do you think we might want to define in this table?
• What datatypes do you think each column should have?
• What might the primary key be?

Exercise 3: Creating setup-mycart.sql

There are usually various design decisions you'll make depending on the amount of data you are going to end up storing, relations with other tables, etc. On HW5 we'll be very clear on the table schema you are to implement, and for today, we will also give you a recommended schema to get started.

Write a sql file called setup-mycart.sql that creates a SQL table in the bmstore database called MyCart. This table should have as its PRIMARY KEY an integer attribute called cid (for 'cart item id'), pid (for 'product id'), qty (integer) and lastupdated (DATETIME type) representing the time the item was most recently added or updated.

Exercise 3: Creating a New MyCart Table

You may find it helpful to refer to the provided setup-inventory.sql file for using AUTO INCREMENT and PRIMARY KEY to set these constraints on the cid key for your table. Note that pid will correspond to the id attribute for each item in Inventory.

You can use the FOREIGN KEY constraint to link the pid from Inventory to your MyCart table. This constraint makes it so that only items that exist in the Inventory table can be added to MyCart, which is an appropriate constraint to set for the purposes of an e-commerce website limited to one set of products available for customers to choose from.

Exercise 3: Creating a New MyCart Table

You should not insert any rows in this file, just write the statements to create the table. When you have finished, import setup-mycart.sql from the bmstore database panel in phpMyAdmin.

In the SQL console, test your new table by inserting your first row into the table with a product id and quantity of your choice (use an id from the Inventory table). You can then SELECT from the MyCart table to check that you have exactly one row. If this is successful, delete the row to empty the MyCart table again.

Exercise 4: Adding Items to MyCart Using PHP

Now we'll add a new PHP file to handle inserting items into the MyCart table. Write a new file called update-mycart.php. Remember to include common.php in this file so you have the get_PDO() function available.

In this PHP file, write a function named add_product_to_cart that takes as parameters $pid and $qty and gets the corresponding row from Inventory based on $pid. Remember that the pid corresponds to the unique id in the Inventory table.

If a row is found with the pid, insert the pid and qty into the MyCart table with lastupdated assigned to NOW() - add a new row if there is not yet a product with the pid in the MyCart table, otherwise update the qty value to add the passed $qty to the current qty for that row in the table. You should also update the lastupdated value to be NOW() to reflect the recent quantity change for the product in the cart.

Exercise 4: Inserting Items to MyCart

Upon success, output the following JSON message:

            { "success" : "X of Y added to your shopping cart!" }
            

JSON

replacing "X" with $qty and "Y" with the name of the product added. Note that since you don't have the name as an attribute in MyCart, you'll need to get this information from a query the Inventory table - you will need to have already written a query on Inventory to determine whether the pid was valid, so try to find a way to use information from that query here.

Test your function by calling it with a pid and qty of your choice so that when you open update-mycart.php on your browser (with a local server running), your function is called with those values and you see the JSON result printed. Check the contents of your MyCart table to confirm that it is no longer empty.

Exercise 5: Adding POST parameters to update-mycart.php

Next, modify update-mycart.php to check for three (required) POST parameters: mode, pid, and qty. If these are all passed to your web service and mode is passed as add call the corresponding function you wrote in Exercise 4 with these values.

If MyCart was successfully updated with these values, return a JSON "success" response as specified in Exercise 4. If any of the required POST parameters are missing or an unrecognized mode value is passed, output a 400 error with a user-friendly plain text message.