Host Database PHP

Code

php
├── api
│   └── v1
│       └── index.php
├── database
│   ├── config.php
│   ├── database.php
│   └── schema.sql
├── .env
├── docker-compose.yml
├── Dockerfile
├── install
│   └── index.php
└── model
    └── host.php

docker-compose.yml:

version: "3"

services:
  web:
    container_name: web
    image: ifpb/php:7.3-apache-pdo
    build: .
    networks:
      - app-network
    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
    restart: unless-stopped
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-network

networks:
  app-network:
    driver: bridge

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

.env:

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

Database

Schema

Host Simple Model

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');

Install

http://localhost:8080/install/ (install/index.php):

<?php

require_once(__DIR__ . '/../database/config.php');

try {
  $schema = file_get_contents('../database/schema.sql');
  $connection = new PDO(DB . ":host=" . DBHOST, DBUSER, DBPWD);
  $connection->exec($schema);
  // echo "Database installed!";
  header('Location: /api/v1/');;
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}

database/config.php:

<?php

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

Model

database/database.php:

<?php
namespace Database;

use \PDO;

require_once('config.php');

class Database {

  protected $connection;

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

}

model/host.php:

<?php
namespace Model;

use Database\Database;
use \PDO;

require_once __DIR__."/../database/database.php";

class Host extends Database {

  public function create($name, $address) {
    $sql = "INSERT INTO host (name, address) VALUES ('${name}', '${address}');";
    try {
      $this->connection->exec($sql);
      return $this->connection->lastInsertId();
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
      return null;
    } 
  }

  public function read($id) {
    $sql = "SELECT * FROM host WHERE id = ${id}";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetch(PDO::FETCH_OBJ) : null;
  }

  public function readByNameAddress($name, $address) {
    $sql = "SELECT * FROM host WHERE name='${name}' AND address='${address}'";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetch(PDO::FETCH_OBJ) : null;
  }

  public function readAll() {
    $sql = "SELECT * FROM host";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetchAll(PDO::FETCH_OBJ) : null;
  }

  public function readOrCreate($name, $address) {
    $result = $this->readByNameAddress($name, $address);

    if ($result) {
      return $result;
    } else {
      return $this->create($name, $address);
    }
  }
  
  public function update($id, $name, $address) {
    $sql = "UPDATE host
            SET name='${name}', address='${address}'
            WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }
  
  public function remove($id) {
    $sql = "DELETE FROM host WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

API

api/v1/index.php:

<?php
use Model\Host;

require(__DIR__.'/../../model/host.php');

$action = $_GET['a'] ?? 'readAll';
$hostDB = new Host();
$json = '';

if ($action === 'create') {
  $name = $_GET['name'] ?? null;
  $address = $_GET['address'] ?? null;
  $result = $hostDB->create($name, $address);
  $json = ['status' => ($result ? 'host created.' : 'host not created.')];
} else if ($action === 'read') {
  $id = $_GET['id'] ?? null;
  $json = $hostDB->read($id);
} else if ($action === 'readAll') {
  $json = $hostDB->readAll();
} else if ($action === 'update') {
  $id = $_GET['id'] ?? null;
  $name = $_GET['name'] ?? null;
  $address = $_GET['address'] ?? null;
  $result = $hostDB->update($id, $name, $address);
  $json = ['status' => ($result ? 'host updated.' : 'host not updated.')];
} else if ($action === 'remove') {
  $id = $_GET['id'] ?? null;
  $result = $hostDB->remove($id);
  $json = ['status' => ($result ? 'host removed.' : 'host not removed.')];
}

header('Content-type: application/json; charset=UTF-8');
header("Access-Control-Allow-Origin: *");
echo json_encode($json);
?>