Lecture 23 - PHP with SQL & Modifying Databases

Administrivia

Consortium for the Advancement of Undergraduate STEM Education (CAUSE)

• Consent video watched together
• Opt-out forms at the CSE front desk(s)

CP4 Showcase

Check Piazza for a message about status of HW3 grading

Creative Project 5 will be out later today (a Promise? :))

• Due Thursday May 30, 11:00pm
• Gitgrade lock Friday May 31, 11:00pm

Homework 5 will be released early next week

• Due Wednesday June 5, 11:00pm
• Gitgrade lock Saturday June 8, 11:00pm

Today's Agenda

More SQL commands and database manipulation, including:

• CREATE TABLE
• INSERT into a table

The PHP/SQL Connection with PDO

More SQL commands

Comments in SQL

Comments in SQL are traditionally done with two dashes --, but you can also use Java/JavaScript types comments /* */

-- This is a comment

/*
   This is also a comment
 */

SQL (example)

In this class, you must use -- comments for any work you turn-in (this is more conventional, but it is also a requirement for GitGrade submissions)

Database commands

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)

Data Types

A full list of data types are here.

Other handy statements

• PRIMARY KEY (keyname): Used to specify a column or group of columns uniquely idenfies a row in a table.
• AUTO_INCREMENT: Used with a primary key field to automatically generate the "next" value in a particular column when a new row is added.
• NOT NULL: ensures a column has a value, i.e. there must be a value for this column or you can't insert this row into the database.

WPL Queue

What types of data will we need to store in the database for the WPL Queue?

Text based

• The name of the person for the queue
• The email address of the person
• The details about the question

But what about...

• The student number
• Whether it is a 2 or 10 minute question
• A unique identifier for each question

CREATE TABLE example

What would the CREATE TABLE SQL command look like to create a table to hold the queue for the WPL example?

CREATE TABLE queue(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(255),
   email VARCHAR(255),
   student_id INT,
   time TINYINT,
   question VARCHAR(255),
   PRIMARY KEY(id)
);

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 queue(id, name, email, student_id, time, question)
VALUES (1, "Gilly I. Fuchs", "gif@uw.edu",
        1234567, 2, "MAMP MySQL won't start");

-- Example omitting the id value, which was defined as an AUTO_INCREMENT value
-- in the table's CREATE statement
INSERT INTO queue(name, email, student_id, time, question)
VALUES ("Jeff I. Frea", "jif@uw.edu",
        9876543, 10, "I don't understand fat arrow functions");

SQL (example)

INSERTing many rows

You can insert many rows at once by doing this:

INSERT INTO queue(id, name, email, student_id, time, question)
VALUES
  (3, "Jacki", "jb@uw.edu", 1111111, 10, "My code doesn't work"),
  (4, "Cassian P.", "cp@uw.edu", 2222222, 2, "Why is my header incorrect?"),
  (5, "R. Ducky", "quack@uw.edu", 33333333, 10, "Can I help?"),
  (6, "Matthew", "mbm@uw.edu", 44444444, 10, "How can I insert into a DB?");

SQL (example)

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.

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

But first: PHP Objects

You can define objects in PHP with the class keyword.

class Car {
    public $color = "white";
    public $has_sun_roof = false;
    public $is_dirty = true;

    public function __construct($col, $sun, $dirt) {
      $color = $col;
      $has_sun_roof = $sun;
      $is_dirty = $dirt;
    }

    public function wash_car() {
      $is_dirty = false;
    }
  }
  

PHP

We will be using PHP PDO objects to connect PHP

Point of clarification -> vs =>

The double arrow operator (=>) in PHP is used to set associations in arrays.

$pet = $array("Lauren" => "Jack", "Melissa" => "Mowgli");

PHP

The object operator (->) in PHP is used to access methods and properties in an object. It is the equivalent of the . in Java or Python.

class Car {
  public $color = "white";
  public $has_sun_roof = false;
  ...
}
$camry = new Car();
$camry->color = "brown";
$camry->has_sun_roof = true;
$camry->wash_car();

PHP

We will use the object operator on our PDO objects that are used to keep track of the connection to the database.

PHP include statement

"Evaluates" the contents of the specified file, i.e. reads it in and interprets is at the point where the include statement is located.

When a file is included into another file, it inherits all of the variable scoping as of the line where the include occurs. All functions and classes from the included file have global scope.

include "filename.php";

PHP (template)

include "wpl-commmon.php";

PHP (example)

Connecting to a database

To connect to a database you need 4-5 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 = 'wpldb';    #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 search 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 $pdoex) {
  # 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 $pdoex) {
   header("HTTP/1.1 503 Service Unavailable");
   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)

The PDO query function returns a PDOStatement object.

A PDOStatement is a representation of the rows that match the query and it contains more information that just an array of arrays of row information (although only the query string prints if requested).

$rows = $db->query("SELECT * FROM wpldb;");
print_r($rows);

       

PHP (example)

PDOStatement Object
 (
     [queryString] => SELECT * FROM queue;
 )
         

output

Extracting the data from the PDOStatement

There are a few ways to get the data out of the the PDOStatement.

• Get each individual row one after the other using the PDOStatement fetch method.
• Get all of the rows in an array of arrays using the PDOStatement fetchAll method.
• Use a foreach loop to extract all row sequentially from the PDOStatement one by one. This method uses an iterator and the fetch method behind the scenes.

Using fetch

       $rows = $db->query("SELECT * FROM queue LIMIT 1;");
       # Just get the first row using one fetch() call
       # PDO::FETCH_BOTH is the default for fetch
       # results are associative and numerical
       $row = $rows->fetch();
       print_r($row);
       

PHP

Array (
  [id] => 1 [0] => 1
  [name] => Gilly I. Fuchs [1] => Gilly I. Fuchs
  [email] => gif@uw.edu [2] => gif@uw.edu
  [student_id] => 1234567 [3] => 1234567
  [time] => 2 [4] => 2
  [question] => MAMP MySQL won't start
  [5] => MAMP MySQL won't start
)
         

simulated printed output

Associative and positional indices

fetch by default fetches both an association between the column name and the value, as well as a column position and the value. This is considered PDO::FETCH_BOTH.

Array ([id] => 1 [0] => 1
    [name] => Gilly I. Fuchs [1] => Gilly I. Fuchs
    [email] => gif@uw.edu [2] => gif@uw.edu
    [student_id] => 1234567 [3] => 1234567
    [time] => 2 [4] => 2
    [question] => MAMP MySQL won't start
    [5] => MAMP MySQL won't start )
       

simulated printed output

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

Other fetch examples

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

# PDO::FETCH_ASSOC only fetches the associative array
$rows = $db->query("SELECT * FROM wpldb LIMIT 1;");
$row = $rows->fetch(PDO::FETCH_ASSOC)
print_r($row);

# PDO::FETCH_NUM only fetches by position
$rows = $db->query("SELECT * FROM wpldb LIMIT 1;");
$row = $rows->fetch(PDO::FETCH_NUM) {
print_r($row);
        

 Array (
     [id] => 1 [name] => Gilly I. Fuchs
     [email] => gif@uw.edu [student_id] => 1234567
     [time] => 2 [question] => MAMP MySQL won't start
 )

 Array (
     [0] => 1 [1] => Gilly I. Fuchs
     [2] => gif@uw.edu [3] => 1234567
     [4] => 2 [5] => MAMP MySQL won't start
 )
          

Using fetchAll

fetchAll can be used to get all information from the statement

        $rows = $db->query("SELECT * FROM wpldb;");
        $all_rows = $rows->fetchAll(PDO::FETCH_ASSOC);
        print_r($all_rows);
          

Array
(
  [0] => Array
    (
      [id] => 1 [name] => Gilly I. Fuchs
      [email] => gif@uw.edu [student_id] => 1234567
      [time] => 2 [question] => MAMP MySQL won't start
    )
  [1] => Array
    (
      [id] => 2 [name] => Jeff I. Frea
      [email] => jif@uw.edu [student_id] => 9876543
      [time] => 10 [question] => I don't understand fat arrow functions
    )
  ...
)
          

Extracting rows with a foreach loops

It's helpful to use setFetchMode to set the fetch mode prior to using a foreach loop otherwise it does the default PDO::FETCH_BOTH.

$rows = $db->query("SELECT * FROM wpldb;");
$rows->setFetchMode(PDO::FETCH_ASSOC);
foreach($rows as $row){
  print_r($row);
  print("\n");
}
         

 Array
 (
   [id] => 1 [name] => Gilly I. Fuchs
   [email] => gif@uw.edu [student_id] => 1234567
   [time] => 2 [question] => MAMP MySQL won't start
 )
 Array
 (
   [id] => 2 [name] => Jeff I. Frea
   [email] => jif@uw.edu [student_id] => 9876543
   [time] => 10 [question] => I don't understand fat arrow functions
 )
           

Exceptions for PDO errors

          $db = new PDO($ds, $user, $password);
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SELECT * FROM wpldb;");
          # 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.

PDO and modifying DBs

Remember... With Great Power Comes Great Responsibility

Inserting through PHP

 $str = "INSERT INTO wpldb (name, email, student_id, time, question) " .
        "VALUES ('$name', '$email', '$sid', '$minutes', '$question');";
 $rows = $db->exec($str);
          

PHP (example)

exec runs the given SQL code and returns the number of rows affected.

NOTE: THIS CODE IS NOT SECURE!

It's vulnerable to SQL injection (demo)

Little Bobby Tables

XKCD 327

Another example of SQL injection

Hackaday

Using PDO prepare for security

 $sql = "INSERT INTO queue(name, email, student_id, time, question) "
        . "VALUES (:name, :email, :sid, :minutes, :question);";
 $stmt = $db->prepare($sql);
 $params = array("name" => $_POST["name"],
                 "email" => $_POST["email"],
                 "sid" => $_POST["sid"],
                 "minutes" => $_POST["minutes"],
                 "question" => $_POST["question"]);
 $stmt->execute($params);
          

PHP (example)

The "variables" with colons in the SQL statement are replaced with values from the associative array that is being passed in on execute