Lesson: PHP - PDO

PHP_PDO.md

PHP - PDO

Learning Objectives

• Explain what PDO is and it's role in accessing data
• Explain how to set up environment variables using PHP dotenv
• Explain how to establish and destroy database connections
• Describe what prepared statements are
• Explain how to bind parameters to safely form SQL queries
• Explain how to get the new ID generated from an insert statement
• Read data from an existing database in our appplication
• Access JSON data from a remote API

PDO

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases like MySQL in PHP.

Safely store credentials with PHP dotenv

PHP dotenv Installation

Students might have to install Composer

Optional: Create the .env.example file

DATABASE_HOST="localhost"
DATABASE_NAME="example_database"
DATABASE_USER="my_user_name"
DATABASE_PASSWORD="my_password"

1. Create the .env file with the actual credentials.

2. Use .gitignore to exclude your .env file from being added to Git.

3. Load your environment values in your scripts.

$dotenv = new Dotenv\Dotenv(__DIR__);
$dotenv->load();

Establishing a connection to the database

// The format required by PDO
$dbh = new PDO('mysql:host=' . $_ENV['DATABASE_HOST'] . ';dbname=' . $_ENV['DATABASE_NAME'], $_ENV['DATABASE_USER'], $_ENV['DATABASE_PASSWORD']);

// Using PHP dotenv
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

Handling connection errors

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
} catch (PDOException $e) {
    die('Error: ' . $e->getMessage() . '<br/>');
}

Closing a connection

$dbh = null;

CRUD

Read

$sql = "
    SELECT name, colour 
    FROM fruit
";
$stmt = $dbh->prepare($sql);
$stmt->execute();

// Fetch as associative array
echo "Return next row as an array indexed by column name\n";
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

// Fetch data as object
echo "Return next row as an anonymous object with column names as properties\n";
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
print_r($result->name);

Binding parameters

$sql = "
    SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour
";
$stmt = $dbh->prepare($sql);

$stmt->bindParam(':calories', 150);
$stmt->bindParam(':colour', 'red');
$stmt->execute();
$red = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stmt->bindParam(':calories', 175);
$stmt->bindParam(':colour', 'yellow');
$stmt->execute();
$yellow = $stmt->fetchAll(PDO::FETCH_ASSOC);

Create

$name = 'Apple';
$calories = 150;
$colour = 'red';

$sql = "
    INSERT INTO fruit (name, colour, calories) VALUES 
    (:name, :colour, :calories)
";
$stmt = $dbh->prepare();
$stmt->bindParam(':name', $name);
$stmt->bindParam(':calories', $calories);
$stmt->bindParam(':colour', $colour);
$stmt->execute();

$new_id = $dbh->lastInsertId();

JSON

Access JSON from a remote server

$response = file_get_contents('https://btc-e.com/api/2/btc_usd/ticker');
$data = json_decode($response);