Section 17: PHP & SQL Connection

Returning to Mowgli's Café

Overview

In Section 12, you implemented AJAX to fetch from the mowgliscafe.php web service we to get a menu represented as JSON, and then used localstorage to store menu data on an online cafe menu.

Today, we'll implement our own PHP web service (menu.php) using SQL and PHP's handy PDO class to store menu data (the same data from Thursday's section!). This web service will behave similarly to mowgliscafe.php but with some fresh updates!

Part 1: From JSON to SQL

The web service you used in Section 12 returned JSON for menu data. Let's use this to work backwards, building the table(s) in SQL that might help us organize a cafe menu with different categories, subcategories, etc. 

{
  "Drinks" : [{
     "subcategory" : "Coffee",
     "items" : [{ "name" : "Americano", "price" : "3.75", "cost" : "1.10" },
                { ... }, ... ]
  ... }],
  "Bakery" : [{ ... }, { ... }, ... ]
}

JSON (schema)

Eventually, we'd like to output JSON from our SQL table that looks like this menu.json.

Part 1: Steps to Creating a DB/Table

  1. Create a database called cafedb in phpMyAdmin.
  2. Start a SQL file called setup-cafe.sql
  3. Design a SQL table for the menu data. Recall that a SQL table is defined with column names and datatypes (e.g. VARCHAR or INT). Take a few minutes to discuss the following:
    • What columns should this table have, noting we eventually want to output the JSON you saw?
    • What datatypes should each column have?
    • What should our PRIMARY KEY be? Would it be useful to AUTO_INCREMENT?
    • Should any values be NOT NULL by default?

Example menu Table

Here's an example table for the menu (in setup-cafe.sql). Were your columns and datatypes close?

CREATE TABLE menu(
  id INT AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  category VARCHAR(50) NOT NULL,
  subcategory VARCHAR(50),
  price DECIMAL(4, 2), -- we can set precision to 2 decimals with DECIMAL
  cost DECIMAL(4, 2),
  -- an "id" column is often used for a PRIMARY KEY, but name
  -- could also be a PRIMARY KEY if guaranteed unique
  PRIMARY KEY(id)
);

SQL

Remember that there are often multiple ways to design a SQL table, with various trade-offs. You may find yourself changing your tables (or adding new ones) as your projects get more complex, but this one will help us implement our menu.

Populating the menu table with setup-cafe.sql

With the cafedb database selected in phpMyAdmin, import setup-cafe.sql.

Select the menu table that appears (initally empty).

Import menu-data.csv" to populate the menu (you should see 59 rows added)

Try adding a few INSERT statements in the MySQL console to add a few of our section's own items!)

Part 2: Starting our PHP Web Service

With our SQL table populated, we'll use our new SQL database with PHP's PDO class to start implementing our own version of the menu.php web service!

As discussed in lecture on Friday, it's good practice to have a common PHP file to factor out shared settings for your database connection from PHP.

We have provided a menu.phps and common.phps (save as .php files) to get started. This common.php is very similar to the one from lecture, but factors out the PDO construction with a get_PDO() function you can call to get a connected PDO object (but we need to fill out the db connection settings). common.php also includes a few helper functions for handling 503 and 400 errors, which you will need to change for Part 4.

API Overview

Our menu.php web serivce will support two GET requests:

  1. menu.php?list=<items|categories> will return a flat JSON array of all items or all categories in the menu, depending on the value passed.
  2. menu.php (no GET parameters) will return a more structured JSON object of items on the menu (similar to the response of the mowgliscafe.php web service you used in Section 12)

Designing our Queries

First, we'll design the queries we need for each request, and test them in the SQL console in phpMyAdmin. These are the queries we will then use with the PDO object in PHP!

Part 2 Exercises

menu.php?list=items: What is the SQL query to get all name values in the menu table, ordered alphabetically?

SELECT name FROM menu ORDER BY name;

SQL

menu.php?list=categories: What is the SQL query to get all category values in the menu table, ordered alphabetically?

SELECT DISTINCT category FROM menu ORDER BY category;

SQL

menu.php: What is the SQL query to get the name, category, cost, and price of all items in the menu table, ordered by category and breaking ties by subcategory alphabetically?

SELECT name, category, cost, price FROM menu ORDER BY category, subcategory;

SQL

With these queries in hand, we'll use PHP's PDO object to connect our PHP with SQL!

From SQL to PHP: The PDO Connection

Setting Up the PDO Connection


# Variables for connections to the database.
$host = 'localhost'; # fill in with server name
$port = '8889'       # fill in with a port if necessary (will be different mac/pc)
$user = 'root';      # fill in with user name
$password = 'root';  # fill in with password (will be different mac/pc)
$dbname = 'cafedb';  # fill in with db name

# Make a data source string that will be used in creating the PDO object
$ds = "mysql:host={$host}:{$port};dbname={$dbname};charset=utf8";

# connect to the cafedb database and set some attributes
$db = new PDO($ds, $user, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

PHP (example)

You'll need to fill in the variables for your MAMP server, but we provide the rest of the get_PDO() function in common.phps

To find find out what to use with phpMyAdmin, check out the MAMP WebStart page (screenshot here). For other systems you have to search on the web.

Using PDO in PHP to query the SQL database

Once you have the PDO object, you can use it to query the database. PDO object's query function returns a PDOStatement object that contains the rows that match a query, which we'll want to loop through with the fetch function to get the values we're going to use in our PHP code. 


$rows = $db->query("SQL query string"); # fill in the query string
while ($row = $rows->fetch()) {
  # We can now access the column values for each row!
}

PHP (template)


$rows = $db->query("SELECT name FROM menu ORDER BY name");
while ($row = $rows->fetch()) {
  echo $row["name"] . "\n"; # "Americano"
}

PHP (example)

More about using fetch

The query returns a PDOStatement

To get the data out of the rows you either use a foreach loop OR you can fetchAll to get all information from the statement (fetch will only get one row, whatever is "next" in the PDO object)

PDO::FETCH_BOTH is the default fetch style, but there are others 

$rows = $db->query("SELECT * FROM menu ORDER BY name LIMIT 1;");
$row = $rows->fetch(PDO::FETCH_ASSOC);
print_r($row);       # Just get (all columns) for the first row

PHP

Array(
    [id] => 34
    [name] => Americano
    [category] => Drinks
    [subcategory] => Coffee
    [price] => 3.75
    [cost] => 1.10
)

printed output

Other fetch examples

Each assume $rows contains the results of a query


 while ($row = $rows->fetch()) {
  print_r($row);
  print("\n");
 }

 # PDO::FETCH_ASSOC only fetches the associative array
 while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
  print_r($row);
  print("\n");
 }

 # PDO::FETCH_NUM only fetches by position
 while ($row = $rows->fetch(PDO::FETCH_NUM)) {
   print_r($row);
   print("\n");
 }

PHP

Part 3: Implementing menu.php?list=categories|items

First, we'll tackle the GET request for listing categories or the names of menu items as flat arrays of strings.

menu.php?list=items

Using the PDO, retrieve all items from the menu and return an array result as JSON, sorting the items by name alphabetically. An example response (abbreviated) is shown below:

["Americano", "Apple Cider", "Avocado Toast", ..., "White Chocolate Mocha"]

JSON

menu.php?list=categories

Next, we'll update menu.php to support listing category names instead of item names. How can you factor this out with the existing solution to reduce redundancy?

An example response is shown below:

["Bakery", "Drinks", "Entrees", "Salad"]

JSON

Part 4: Error-handling with JSON

We've output our error messages in plain text in most of the web services so far. But you can also output them in JSON! We have provided two helper functions for error-handling in common.php. Change them as necessary to output JSON for the different errors.

Add a case in menu.php such that if list is passed with a value other than items or categories, a 400 error is output with the JSON format: 

{ "error" : "Please pass a list of 'items' or 'categories'" }

JSON

While we're at it, we'd like our error responses to be consistent. We'll update the 503 errors to output the following JSON whenever a PDO connection fails (using try/catch for any code that uses the PDO object):

{ "error" : "HTTP/1.1 503 Service Unavailable" }

JSON

Part 5: menu.php

Next, we'll build the more useful JSON that we saw as clients in Section 12. If no list parameter is passed (e.g. a client requests menu.php, retrieve all the name, category, price, and cost for all rows from the menu table and respond with JSON in the following format:

{
  "<categoryname>" : [
    { "subcategory" : "<subcategoryname>",
      "items" : [{ "name" : "<name>", "price" : <price>, "cost" : <cost> },
                   "name" : "<name>", "price" : <price>, "cost" : <cost> }, ... ]},
    { "subcategory" : "<subcategoryname>", ... }, ... ],
  "<categoryname>" : [
    { "subcategory" : "<subcategoryname>",
      "items" : [ ... ] }]
}

JSON (Schema)

You can find the expected output JSON for the data in menu-data.csv here.

This one's a bit trickier with nested arrays, but remember to test your PHP code incrementally!

Solution

One example solution is here: menu.phps