PDO — PHP Data Objects

Code

host-crud
├── .env
├── Dockerfile
├── config.php
├── database
│   ├── schema.mwb
│   └── schema.sql
├── database.php
├── docker-compose.yml
├── host.php
├── test-create.php
├── test-delete.php
├── test-read.php
└── test-update.php

codes/host-crud/docker-compose.yml:

version: "3"

services:
  web:
    container_name: web
    image: ifpb/php:7.3-apache-pdo
    build: .
    networks:
      - app-tier
    ports:
      - 8080:80
    volumes:
      - ./:/var/www/html/
    depends_on:
      - mysql

  mysql:
    container_name: mysql
    image: mysql:8.0
    command:
      - --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306

  adminer:
    container_name: adminer
    image: adminer:4.7.5
    restart: always
    networks:
      - app-tier
    ports:
      - 8081:8080
    depends_on:
      - mysql

networks:
  app-tier:
    driver: bridge

codes/host-crud/Dockerfile:

FROM php:7.3-apache

RUN apt -y update \
  && apt install -y \
  mariadb-client

# pdo_mysql package
RUN docker-php-ext-install pdo_mysql

codes/host-crud/.env:

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=example
MYSQL_USER=devuser
MYSQL_PASSWORD=devpass

Database

Schema

codes/host-crud/database/schema.sql:

DROP DATABASE IF EXISTS computer;
CREATE DATABASE computer;
USE computer;

DROP TABLE IF EXISTS `host`;
CREATE TABLE `host` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `host`
  (`name`, `address`)
VALUES
  ('www.google.com', '216.58.222.100');

Import

$ docker-compose exec web bash
# mysql -h mysql -u root -p < pdo/codes/host-crud/database/schema.sql

PDO CRUD

Check PDO

$ docker-compose exec web bash
# php -m | grep pdo
pdo_mysql
pdo_sqlite

Connection

codes/host-crud/config.php:

<?php

const DB = 'mysql';
const DBHOST = 'mysql';
const DBNAME = 'computer';
const DBUSER = 'root';
const DBPWD = 'secret';

codes/host-crud/database.php:

<?php
require_once('config.php');

function connect(){
  $dsn = DB.":dbname=".DBNAME.";host=".DBHOST;
  try {
    return new PDO($dsn, DBUSER, DBPWD);
  } catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
  }
}

Host Model

codes/host-crud/host.php:

<?php

require_once('database.php');

/**
 * Create
 */
function create($name, $address)
{
  $connection = connect();

  $sql = "INSERT INTO host (name, address) VALUES ('${name}', '${address}');";

  try {
    $connection->exec($sql);
    return $connection->lastInsertId();
  } catch (PDOExecption $e) {
    $connection->rollback();
    print "Error!: " . $e->getMessage();
    return null;
  }
}

/**
 *  Read by ID
 */
function read($id)
{
  $connection = connect();
  $sql = "SELECT * FROM host WHERE id = ${id}";
  $pdoStm = $connection->query($sql);
  return $pdoStm ? $pdoStm->fetch(PDO::FETCH_ASSOC) : null;
}

/**
 * Read by Name and Address
 */
function readByNameAddress($name, $address)
{
  $connection = connect();
  $sql = "SELECT * FROM host WHERE name='${name}' AND address='${address}'";
  $pdoStm = $connection->query($sql);
  return $pdoStm ? $pdoStm->fetch(PDO::FETCH_ASSOC) : null;
}

/**
 * Read All
 */
function readAll()
{
  $connection = connect();
  $sql = "SELECT * FROM host";
  $pdoStm = $connection->query($sql);
  return $pdoStm ? $pdoStm->fetchAll(PDO::FETCH_ASSOC) : null;
}

/**
 * Read or Create
 */
function readOrCreate($name, $address)
{
  $connection = connect();
  $result = readByNameAddress($name, $address);

  if ($result) {
    return $result;
  } else {
    return create($name, $address);
  }
}

/**
 * Update
 */
function update($name, $address, $id)
{
  $connection = connect();

  $sql = "UPDATE host
          SET name='${name}', address='${address}'
          WHERE id=${id}";

  try {
    return $connection->exec($sql);
  } catch (PDOExecption $e) {
    $connection->rollback();
    print "Error!: " . $e->getMessage();
  }
}

/**
 * Delete
 */
function delete($id)
{
  $connection = connect();

  $sql = "DELETE FROM host WHERE id=${id}";

  try {
    return $connection->exec($sql);
  } catch (PDOExecption $e) {
    $connection->rollback();
    print "Error!: " . $e->getMessage();
  }
}

Create Data

codes/host-crud/test-create.php:

<pre>
<?php
require_once('host.php');

var_dump(create('dns google', '8.8.8.8')); // string(1) "2"
?>
</pre>

http://localhost:8080/test-create.php

Read Data

codes/host-crud/test-read.php:

<pre>
<?php
require_once('host.php');

var_dump(read(2));
//=>
// array(3) {
//   ["id"]=>  string(1) "2"
//   ["name"]=>  string(10) "dns google"
//   ["address"]=>  string(7) "8.8.8.8"
// }
var_dump(readByNameAddress("dns google", "8.8.8.8"));
//=>
// array(3) {
//   ["id"]=>  string(1) "2"
//   ["name"]=>  string(10) "dns google"
//   ["address"]=>  string(7) "8.8.8.8"
// }
var_dump(readAll());
//=>
// array(2) {
//   [0]=>array(3) {
//     ["id"]=>string(1) "1"
//     ["name"]=>string(14) "www.google.com"
//     ["address"]=>string(14) "216.58.222.100"
//   }
//   [1]=>array(3) {
//     ["id"]=>string(1) "2"
//     ["name"]=>string(10) "dns google"
//     ["address"]=>string(7) "8.8.8.8"
//   }
// }
var_dump(readOrCreate("dns google", "8.8.8.8"));
//=>
// array(3) {
//   ["id"]=>  string(1) "2"
//   ["name"]=>  string(10) "dns google"
//   ["address"]=>  string(7) "8.8.8.8"
// }
var_dump(readOrCreate("dns google", "8.8.4.4")); //=> string(1) "3"
?>
</pre>

http://localhost:8080/test-read.php

Update Data

codes/host-crud/test-update.php:

<pre>
<?php
require_once('host.php');

var_dump(update('Google DNS', '8.8.8.8', 2)); //=> int(1)
var_dump(update('Google DNS', '8.8.8.8', 2)); //=> int(0)
?>
</pre>

http://localhost:8080/test-update.php

Delete Data

codes/host-crud/test-delete.php:

<pre>
<?php
require_once('host.php');

var_dump(delete(2)); //=> int(1)
var_dump(delete(2)); //=> int(0)
?>
</pre>

http://localhost:8080/test-delete.php

PDO Model