Lecture 22 - More SQL (Modifying Databases)

Today's Agenda

Database manipulation

• CREATE TABLE
• INSERT into a table
• UPDATE data in a table
• DELETE data from a table

Introduction to the PHP/SQL Connection

Full-stack website organization

A full stack website consists of three layers.

• Web Browser (client): HTML, CSS, JS
• Web Server: PHP
• Database Server: SQL

• The web server makes requests of the database server much like our javascript used AJAX
• Unlike our asynchonous AJAX calls, our PHP code will only be accessing our databases in a synchronous manner.

Running phpMyAdmin with MAMP

Instructions are on the resources page

1. Start MAMP, click on the "Open WebStart Page"
2. Click on the phpMyAdmin link

Note for those using Ubuntu 18.04 and MAMP: Here is the fix if you see an error that says
count(): Parameter must be an array or an object that implements Countable

You can also use MySQL from the command line, if you're interested please come see us during office hours.

Demo of loading games database with phpMyAdmin

Databases

Each MySQL server can have multiple databases.

CREATE DATABASE database_name; creates a new database

SHOW DATABASES; displays a list of the databases

USE database_name; selects a database for use

CREATE TABLE

CREATE TABLE is used to create a new table.

Syntax:

CREATE TABLE table_name(
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype,
  PRIMARY KEY(one or more columns)
);

SQL (template)

Let's plan a potluck

What kinds of information would you need if you were going to plan a potluck meal?

This might include

• The name of the person or people bringing a dish
• The the dish they're bringing
• How many people it serves
• Whether the dish needs to be kept warm, cold, or room temperature

CREATE TABLE example

CREATE TABLE Potluck(
   id INT,
   name VARCHAR(255),
   dish VARCHAR(255),
   serves INT,
   temperature VARCHAR(255),
   PRIMARY KEY(id)
);

SQL (example)

Data Types

A full list of data types are here.

Other table commands

SHOW TABLES; displays a list of the tables

DESCRIBE table_name; display the columns of the table

DROP TABLE table_name; deletes a table

DROP TABLE IF EXISTS table_name; deletes a table only if it exists, which prevents an error if it doesn't

INSERT

INSERT is used to insert a new record into an existing table.

Syntax:

INSERT INTO Potluck (id, name, dish, serves, temperature )
  VALUES (1, "Lauren", "Paleo Apple Tart", 8, "room");

INSERT INTO Potluck (id, name, dish, serves, temperature )
  VALUES (2, "Stephen", "Balsamic Brussels Sprouts", 8, "hot");

INSERT INTO Potluck (id, name, dish, serves, temperature )
  VALUES (3, "Elias", "Apple Pie", 8, "room");

SQL (example)

INSERTing many rows

You can insert many rows at once by doing this:

INSERT INTO Potluck (id, name, dish, serves, temperature )
VALUES
  (4, "Kristina", "Broccoli Salad", 16, "cold"),
  (5, "Ron and Jacki", "Turkey", 20, "hot"),
  (6, "Ron and Jacki", "Stuffing", 16, "hot"),
  (7, "Matthew", "Drinks", 16, "cold"),
  (8, "Ron", "Mashed Potatoes", 12, "hot"),
  (9, "Ron", "Green Bean Casserole", 16, "hot"),
  (10, "Audrey", "Baked Brie", 10, "hot"),
  (11, "Sadie", "Dog food", 1, "room");

SQL (example)

UPDATE

UPDATE table
  SET col1 = val1, col2 = val2, ...
  WHERE condition;
        

SQL (template)

UPDATE Potluck
  SET dish = "Mashed Potato Casserole", name = "Stephen"
  WHERE dish = "Mashed Potatoes";
        

SQL (example)

UPDATE changes all rows where the condition is true.

DELETE

DELETE FROM table
  WHERE condition;
        

SQL (template)

DELETE FROM Potluck
  WHERE name = "Sadie";
        

SQL (example)

Deletes the specified records based on the results of the WHERE clause

CAUTION: if you omit the WHERE clause all records will be deleted!!!

Intro to the PHP SQL Connection

PDO class

There are two ways to connect to a database through PHP

1. Using MySQLi - a procedural object-oriented connection scheme that only works on MySQL databases.
2. Using PHP Data Objects (PDO) - an object-oriented representation of the connection between PHP and the server that works on 12 different database system.

We are using PDO in this class

Connecting to a database

To connect to a database you need 4 pieces of information

• host
• port (maybe)
• database name
• the user name for the database
• the database password

Then create a PDO object to represent the database connection

PDO Connection

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

# 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 Potluck database and set some attributes
$db = new PDO($ds, $user, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

PHP (example)

To find find out what to use with phpMyAdmin, check out the MAMP WebStart page. For other systems you have to do searching on the web.

try/catch

Database connections can have different problems: the database server could be down, the database could be missing or corrupted

try/catch helps us catch and identify when errors occur so we can handle the error correctly

try {
   # things to try that could throw an error
}
catch (PDOException $ex) {
  # code for handling the error here.
}
 

PHP (template)

try/catch example

try {
   $db = new PDO($ds, $user, $password);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch (PDOException $ex) {
   header("HTTP/1.1 400 Invalid Request");
   header("Content-Type: text/plain");
   die("Can not connect to the database. Please try again later.");
 }
         

PHP (example)

PDO query

Once you have the PDO object, you can use it to query the database.

$rows = $db->query("SQL query string"); # fill in the query string

PHP (template)

$rows = $db->query("SELECT * FROM Potluck;");
foreach($rows as $row){
print_r($row) . "\n";
}
       

PHP (example)

PDO object's query function returns a PDOStatement object that contains the rows that match a query

Array ([id] => 1 [0] => 1 ...)
Array ([id] => 2 [0] => 2 ...)
         

output

PDO query real output

Each row from the output is an associative array both with the column names AND positional indices as the default.

Array ( [id] => 1 [0] => 1
        [name] => Lauren [1] => Lauren
        [dish] => Paleo Apple Tart [2] => Paleo Apple Tart
        [serves] => 8 [3] => 8
        [temperature] => cold [4] => cold)
Array ( [id] => 2 [0] => 2
        [name] => Stephen [1] => Stephen
        [dish] => Balsamic Brussels Sprouts
        [2] => Balsamic Brussels Sprouts
        [serves] => 8 [3] => 8
        [temperature] => hot [4] => hot )
          

simulated printed output

Example: in your PHP code $row["dish"] or $row[2] gives the contents of the dish column.

Exceptions for PDO errors

          $db = new PDO($ds, $user, $password);
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SELECT * FROM Potluck;");
          # kaboom!
          

PHP

Using setAttribute, you can tell PDO to throw (generate) a PDOException when an error occurs.

The exceptions will appear as error messages on the page output.

You can catch the exception to gracefully handle the error.

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

          $db = new PDO($ds, $user, $password);
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SELECT * FROM Potluck LIMIT 1;");
          $row = $rows->fetch(PDO::FETCH_ASSOC);
          print_r($row);       # Just get the first row
          

PHP

Array ( [id] => 1
[name] => Lauren
[dish] => Paleo Apple Tart
[serves] => 8
[temperature] => cold)
          

simulated printed output