Section 18: More PHP & SQL Connection

Exploration Session This Week

Computer Networks

Today, 4:30pm, MGH389

  • One HW Extra Credit Point Per Session Attended!
  • If you have a legitmate conflict, fill out the form on the exploration sessions page of the course website to watch the Panapto and take a short quiz for credit.

An introduction to 'An introduction to Networks.' We'll be exploring the layers of a computer network from the physical wire to application protocols as well as network security / special applications like TOR.

Section Overview

In this section, you have 2 options to practice PHP and SQL:

  1. Worktime for CP5
  2. Build off of Tuesday's menu.php web service with practice for secure POST requests using PDO with INSERT/DELETE/UPDATE.

For Option 2, you can find a walkthrough in the following slides!

Mowgli's Cafe Part II: Adding to the DB with PDO

In this extension of Mowgli's cafe, we will build off of Tuesday's menu.php web service with the cafedb database and get practice with handling POST requests in PHP to modify your MySQL database, which often work a little differently than simple SELECT queries.

Outline

We will build off of what you did in section this week to add POST requests to modify the cafedb database. You are also encouraged to review Tuesday's exercises if you didn't finish them in section.

  • (Review) Setup: Make sure you have a cafedb database and menu table from Tuesday's setup instructions.
  • Exercise 1: INSERT new menu items with add-item.php
  • Exercise 2: DELETE menu items with delete-item.php
  • Exercise 3: UPDATE the menu with update-menu.php

Setting Things Up: Creating/Importing Data

On Tuesday, we wrote a setup-menu.sql file to setup our menu table in the cafedb. This table stored information for item ids, names, categories, subcategories, price, and cost. If you didn't follow on your own computer, make sure to follow the instructions on this page.

Confirm that the menu table was successfully populated in your cafedb database using a SELECT query in the bottom SQL console of phpMyAdmin (you should see 59 rows in the result table upon success).

Setting Things Up: Current Web Service

We strongly recommend working through Tuesday's section slides if you didn't finish them on Tuesday. There are various possible solutions, and when first using SQL with PHP, it's important that you get experience building a web service supporting different requests on your own and asking about how to break down different requests and behavior. This is a great chance to ask TA's for tips and tricks, and to review any questions you have about PHP, SQL, and PDO.

However, you can also find the solution code in section17-menu-solution.zip, which you are free to build off in this section (make sure you understand it, and remember that it's only one possible solution).

Exercises 1-3: Using PDO to Modify the Cafe DB

The following exercises will use PDO to modify (INSERT, DELETE, and UPDATE) the cafedb. Make sure to refer to yesterday's lecture slides and the previously-assigned PDO reading if needed!

As you work through the exercises, think of ways you can factor out shared code in common.php - you may want to add more functions to the version from Tuesday!

Exercise 1: Adding to the Menu with add-item.php

Write a new PHP file add-item.php that can be used to add items to the menu table. When a POST request is made with the following parameters:

  • Required: name - name of item
  • Required: category - category for menu item
  • Optional: subcategory - subcategory for item (default as the category name)
  • Optional: price - price of item (default of 0.00)
  • Optional: cost - cost of item (default of 0.00)

A new menu item should be added with these values for the respective columns (remember you don't need to add a value for the id column if it has the AUTO_INCREMENT attribute). Use PHP's ucwords function to insert any string values (name, category, subcategory) in Title Case format (e.g. "brewed coffee" should be inserted as "Brewed Coffee").

See the slides below for more instructions.

Exercise 1: Response Messages

Upon success, output the following JSON message:

{ "success" : "<name> added to the menu!" }

Otherwise, handle the possible invalid requests to add-item.php error messages as JSON format (in order of precedence):

  • If missing the required name and category POST parameters: 
    • { "error" : "Missing required name and/or category parameters" }
  • If a price or cost is passed as a negative value:
    • { "error" : "Price and cost should be non-negative." }
  • If an item already exists in the table with the given name: 
    • { "error" : "We already have <name> on the menu!" }

Where used, <name> should be replaced with the name in the same format passed by the client.

Tip: Test your solution out with POSTMan! This will be a very useful testing tool for HW5 since POST requests are a little more tedious to test without writing a front-end.

Exercise 2: Deleting an item from the menu

Write a new PHP file delete-item.php that can be used to remove items from the menu table. When a POST request is made with the required name parameter, any item with that name should be removed from the table. If an item was succesfully removed, respond with the JSON format: 

{ "success" : "<name>" removed from the menu!" }

Otherwise, handle the possible invalid requests to delete-item.php error messages as JSON format (in order of precedence):

  • If missing the required name parameter:
    • { "error" : "Missing required name parameter." }
  • If no item exists in the table with the given name:
    • { "error" : "No item on the menu found with name: <name>."}

When used in a response message, <name> should be replaced with the name in the same format passed by the client.

Exercise 3: Updating the menu with update-menu.php

Write a new PHP file update-menu.php that can be used to update the price and/or cost of items from the menu table. When a POST request is made with the required name parameter and at least one of price or cost, the item in the menu table having the passed name should be updated with the new price and/or cost.

If an item was succesfully updated, respond with the JSON format: 

{ "success" : "<name> now has a cost of $<cost> and price of $<price>!" }

Note that both cost and price values for the item in the menu table are output, even if only one was updated. To format a number as a string with 2 decimal places, remember you can use the number_format function (e.g. number_format(154, 2) to get "154.00")

There are a few possible invalid requests we'll want to check before updating an item though, specified on the slide below.

Exercise 3: Error-handling

Handle the possible invalid requests to update-menu.php with 400 error messages as JSON format (in order of precedence):

  • Missing the required name POST parameter should behave the same as in delete-item.php.
  • If neither price or cost are passed: 
    • { "error" : "Missing price or cost to update the menu item." }
  • If price or cost is passed as a negative value: 
    • { "error" : "Price and cost should be non-negative." }

Exercise 1-3 Solutions

Possible solutions are provided below:

Extra Features to Try on Your Own

  • Add a second table to the cafedb to manage an online "cart" for a user who is building a cafe order to submit. What kind of columns would that cart table need? Consider writing PHP services to support retrieving and updating the cart, and having a column for the DATETIME an item was added.
  • Write a front-end (HTML/CSS/JS) that calls your web services to populate a menu page for a manager of the cafe restaurant to update the menu. Consider using a bulleted list or a table for displaying a menu list.
  • Add filtering UI elements to let a user filter products by the categories they want to see and use your category parameter to return filtered results when a call is made to menu.php.
  • Add a view to the front-end to show a list of all of the products in a customer's cart. Consider using a bulleted list or a table for displaying a shopping cart.