@ PHP

OOP:CRUD

OOP(Object Oriented PHP)で CRUD を作成してみます。

ディレクトリ構成は次のようにします。

/shop
├── classes
│   ├── DbConnect.php
│   ├── Crud.php
│   └── Validation.php
├── add.php
├── delete.php
├── edit.php
├── index.php
└── style.css

データベースの作成

phpMyAdmin などを利用して、以下のデータベースを作成します。

  • データベース名:shop
  • 照合順序:utf8_general_ci

データベースに新規テーブルを作成する

データベース shop 配下に新規に products テーブルを作成します。

products テーブルのレイアウトはつぎのようにします。

フィールド名 データ型 概要
id INT(11) ID AUTO_INCREMENT (PK)
name VARCHAR(100) 商品名 NOT NULL
price INT(11) 価格 NOT NULL
description VARCHAR(100) 商品説明 NOT NULL

createtable.php

<?php

try {
    $dbh = new PDO(
        'mysql:host=localhost; dbname=shop; charset=utf8',
        'user',
        'password',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );

    $sql = <<<SQL
    CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INT(11) NOT NULL,
    description VARCHAR(100) NOT NULL
    );
SQL;

    $sth = $dbh->prepare($sql);
    $sth->execute();

    if ($sth == true) {
        printf("Created table products.\n");
    } else {
        printf("Cannot created table products.\n");
    }
} catch (PDOException $e) {
print "ERR! : {$e->getMessage()}";
} finally {
$dbh = null;
}

データベース接続クラス

classes/DbConnect.php

<?php

class DbConnect
{
    private $_user = "user";
    private $_password = "password";
    private $_dns = "mysql:dbname=shop;host=localhost;charset=utf8";
    private $_options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];

    protected $dbh;

    public function __construct()
    {
        try {
            $this->dbh = new PDO($this->_dns, $this->_user, $this->_password, $this->_options);
            return $this->dbh;
        } catch (PDOException $e) {
            echo 'ERR! : ' . $e->getMessage();
            return false;
        } finally {
            return true;
        }
    }
}

CRUDクラス

classes/Crud.php

<?php

require_once('DbConnect.php');

class Crud extends DbConnect
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getData($sql)
    {
        $sth = $this->executeSQL($sql, null);
        foreach ($rows = $sth->fetchAll() as $row) {
            return $rows;
        }
    }

    public function delete($id, $table)
    {
        $sql = "DELETE FROM $table WHERE id=?";
        $array = array($id);
        $this->executeSQL($sql, $array);
        return true;
    }

    public function executeSQL($sql, $array)
    {
        try {
            $sth = $this->dbh->prepare($sql);
            $sth->execute($array);
            return $sth;
        } catch (PDOException $e) {
            echo 'ERR! : ' . $e->getMessage();
            return false;
        } finally {
            $this->dbh = null;
        }
    }

    public function h($str, string $charset = 'UTF-8'): string
    {
        return htmlspecialchars($str, ENT_QUOTES | ENT_HTML5, $charset);
    }
}

入力検証クラス

classes/Validation.php

<?php

class Validation
{
    public function __construct()
    {
    }

    /**
     * チェックの実施
     */
    public function check($parameters, $conditions)
    {
        $errors = array();

        foreach ($conditions as $key => $condition) {
            // 評価する値の抽出
            $value = "";
            if (isset($parameters[$key])) {
                $value = $parameters[$key];
            }

            // チェック条件の初期化
            $max = (isset($condition["max"]) ? $condition["max"] : null);
            $min = (isset($condition["min"]) ? $condition["min"] : null);
            $type = (isset($condition["type"]) ? $condition["type"] : null);
            $disallowWhitespace = (isset($condition["disallowWhitespace"]) ? $condition["disallowWhitespace"] : null);
            $disallowZenkaku = (isset($condition["disallowZenkaku"]) ? $condition["disallowZenkaku"] : null);
            $required = (isset($condition["required"]) ? $condition["required"] : null);

            // 必須チェック
            if (!$this->checkRequired($value, $required)) {
                $errors[$key]["required"] = "必ず入力してください";
            }

            // max文字数チェック
            if (!$this->checkMax($value, $max)) {
                $errors[$key]["max"] = "{$max}文字以下で入力してください";
            }

            // min文字数チェック
            if (!$this->checkMin($value, $min)) {
                $errors[$key]["min"] = "{$min}文字以上で入力してください";
            }

            // 全角文字チェック
            if (!$this->checkZenkaku($value, $disallowZenkaku)) {
                $errors[$key]["disallowZenkaku"] = "全角文字を含めず入力してください";
            }

            // 空白文字チェック
            if (!$this->checkWhitespace($value, $disallowWhitespace)) {
                $errors[$key]["disallowWhitespace"] = "スペースを含めず入力してください";
            }

            // 形式チェック
            if (!$this->checkType($value, $type)) {
                $errors[$key]["type"] = "正しい形式で入力してください";
            }
        }

        if (count($errors) > 0) {
            return $errors;
        } else {
            return false;
        }
    }

    /**
     * 必須チェック
     */
    protected function checkRequired($value, $required)
    {
        if ($value == "") {
            if ($required) {
                return false;
            }
        }
        return true;
    }

    /**
     * max文字数チェック
     */
    protected function checkMax($value, $max)
    {
        if ($value == "") {
            return true;
        }
        if ($max) {
            $mb_length = mb_strlen($value, "UTF-8");
            if ($max < $mb_length) {
                return false;
            }
        }
        return true;
    }

    /**
     * min文字数チェック
     */
    protected function checkMin($value, $min)
    {
        if ($value == "") {
            return true;
        }
        if ($min) {
            $mb_length = mb_strlen($value, "UTF-8");
            if ($min > $mb_length) {
                return false;
            }
        }
        return true;
    }

    /**
     * 全角文字チェック
     */
    protected function checkZenkaku($value, $disallowZenkaku)
    {
        if ($value == "") {
            return true;
        }
        if ($disallowZenkaku) {
            $mb_length = mb_strlen($value, "UTF-8");
            if ($mb_length != strlen($value)) {
                return false;
            }
        }
        return true;
    }

    /**
     * 空白文字チェック
     */
    protected function checkWhitespace($value, $disallowWhitespace)
    {
        if ($value == "") {
            return true;
        }
        if ($disallowWhitespace) {
            if (strpos($value, " ") !== false || strpos($value, " ") !== false) {
                return false;
            }
        }
        return true;
    }

    /**
     * 形式チェック
     */
    protected function checkType($value, $type)
    {
        $error = false;
        switch ($type) {
            // アルファベット
            case "alphabet":
            case "a":
                if (!preg_match('/^[a-z]*$/i', $value)) {
                    $error = true;
                }
                break;
            // 数字
            case "numeric":
            case "n":
                if (!preg_match('/^[0-9]*$/i', $value)) {
                    $error = true;
                }
                break;
            // アルファベットと数字
            case "alphabet&numeric":
            case "an":
                if (!preg_match('/^[a-z0-9]*$/i', $value)) {
                    $error = true;
                }
                break;
            // アルファベットと数字と記号
            case "alphabet&numeric&symbols":
            case "ans":
                if (!preg_match('/^[a-z0-9\!\"\#\$\%\&\'\(\)\=\-\^\~\`\@\[\{\+\;\*\:\]\}\,\<\.\>\/\?\_]*$/i', $value)) {
                    $error = true;
                }
                break;
            // Eメール
            case "email":
                if (!preg_match('/^([a-z0-9\+_\-\.\(\)\^\=\*\&\%\#\!]+)*@([a-z0-9\-]+\.)+[a-z]{2,6}$/i', $value)) {
                    $error = true;
                }
                break;
            // URL
            case "url":
                if (!preg_match('/^https?\:\/\/[a-z0-9\/\:\%\#\$&\?\(\)~\.\=\+\-]+$/i', $value)) {
                    $error = true;
                }
                break;
            // 電話番号
            case "tel":
                if (!preg_match('/^[0-9\-\+]*$/i', $value)) {
                    $error = true;
                }
                break;
            // その他
            default:
                break;
        }
        return !$error;
    }
}

登録

add.php

<?php
require_once('classes/Crud.php');
require_once('classes/Validation.php');

$crud = new Crud();

if (isset($_POST['add'])) {
    // 連想配列で値を渡す
    $name = $_POST;

    // 評価内容を定義
    $conditions = [
        "name" => [
            "required" => true, // true->必須
            "max" => 20, // 最大文字数
            "min" => null, // 最小文字数
            "type" => null, // 許容形式(null / alphabet / numeric / alphabet&numeric / alphabet&numeric&symbols / email / url / tel)
            "disallowWhitespace" => true, // 空白文字を禁止するか
            "disallowZenkaku" => null, // 全角文字を禁止するか
        ],
        "price" => [
            "required" => true,
            "max" => 5,
            "min" => 2,
            "type" => "numeric",
            "disallowWhitespace" => null,
            "disallowZenkaku" => null,
        ],
        "description" => [
          "required" => true,
          "max" => 50,
          "min" => null,
          "type" => null,
          "disallowWhitespace" => null,
          "disallowZenkaku" => null,
        ],
    ];

    $validation = new Validation();

    $errors = $validation->check($name, $conditions);

    $name_error = $errors['name'];
    $price_error = $errors['price'];
    $description_error = $errors['description'];

    if (! $errors) {
        $sql = 'INSERT INTO products VALUES(?,?,?,?)';
        $array = array($_POST['id'],$_POST['name'],$_POST['price'],$_POST['description']);
        $crud->executeSQL($sql, $array);
        header('location: index.php');
        exit();
    }
}
?>
<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="utf-8">
  <title>PHP OOP CRUD</title>
  <link rel="stylesheet" href="styles.css">
</head>

<body>
  <main class="container">
    <h1>Add product</h1>
    <div><a href="index.php">Products list</a></div>
    <div style="color:red"><?= $status ?></div>
    <form action="add.php" method="post" name="form1">
      <fieldset>
        <div>
          <label for="name">Product name: </label><br>
          <input type="text" id="name" name="name" value="<?= $crud->h($_POST['name']) ?>"
              placeholder="Enter name">
        </div>
<?php
if ($name_error) {
    foreach ($name_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div>
          <label for="price">Price: </label><br>
          <input type="text" id="price" name="price" value="<?= $crud->h($_POST['price']) ?>"
              placeholder="Enter price">
        </div>
<?php
if ($price_error) {
    foreach ($price_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div>
          <label for="description">Description: </label><br>
          <textarea name="description" rows="2"
              placeholder="Enter description"><?= $crud->h($_POST['description']) ?></textarea>
        </div>
<?php
if ($description_error) {
    foreach ($description_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div><input type="submit" name="add" value="Add"></div>
      </fieldset>
    </form>
  </main>
</body>

</html>

一覧

index.php

<?php session_start();
require_once('classes/Crud.php');

$crud = new Crud();

if ($_SESSION) {
    $_SESSION = [];
    if (isset($_COOKIE["PHPSESSID"])) {
        setcookie("PHPSESSID", '', time() - 1800, '/');
    }
    session_destroy();
}

$sql = 'SELECT * FROM products ORDER BY id DESC';
$rows = $crud->getData($sql);
?>
<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="utf-8">
  <title>PHP OOP CRUD</title>
  <link rel="stylesheet" href="styles.css">
</head>

<body>
  <main class="container">
    <h1>Product list</h1>
    <div><a href="add.php">Add product</a></div>
    <table>
      <thead>
        <tr>
          <th>Product name</th>
          <th>Price</th>
          <th>Description</th>
          <th>Actions</th>
        </tr>
      </thead>
      <tbody>
<?php
if ($rows) :
    foreach ($rows as $res) :
?>
        <tr>
          <td><?= $crud->h($res['name']) ?></td>
          <td><?= $crud->h($res['price']) ?></td>
          <td><?= $crud->h($res['description']) ?></td>
          <td><a href="edit.php?id=<?= $crud->h($res[id]) ?>">Edit</a> | <a href="delete.php?id=<?= $crud->h($res[id]) ?>"
              onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
        </tr>
<?php
    endforeach;
endif;
?>
      <tbody>
    </table>
  </main>
</body>

</html>

更新

edit.php

<?php session_start();
require_once('classes/Crud.php');
require_once('classes/Validation.php');

$crud = new Crud();

if (!$_SESSION) {
    $id = $_GET['id'];
    $rows = $crud->getData("SELECT * FROM products WHERE id=$id");
    foreach ($rows as $res) {
        $_SESSION['product'] = $res;
    }
}

if (isset($_POST['update'])) {
    $name = $_POST;

    $conditions = [
            "name" => [
                "required" => true,
                "max" => 20,
                "min" => null,
                "type" => null, // (null / alphabet / numeric / alphabet&numeric / alphabet&numeric&symbols / email / url / tel)
                "disallowWhitespace" => true,
                "disallowZenkaku" => null,
            ],
            "price" => [
                "required" => true,
                "max" => 5,
                "min" => 2,
                "type" => "numeric",
                "disallowWhitespace" => null,
                "disallowZenkaku" => null,
            ],
            "description" => [
              "required" => true,
              "max" => 50,
              "min" => null,
              "type" => null,
              "disallowWhitespace" => null,
              "disallowZenkaku" => null,
            ],
        ];

    $validation = new Validation();

    $errors = $validation->check($name, $conditions);

    $name_error = $errors['name'];
    $price_error = $errors['price'];
    $description_error = $errors['description'];


    if (! $errors) {
        $sql = 'UPDATE products SET name=?, price=?, description=? WHERE id=?';
        $array = [
            $_POST['name'],
            $_POST['price'],
            $_POST['description'],
            $_SESSION['product']['id']
        ];
        $crud->executeSQL($sql, $array);

        $_SESSION = [];
        if (isset($_COOKIE["PHPSESSID"])) {
            setcookie("PHPSESSID", '', time() - 1800, '/');
        }
        session_destroy();
        header('Location: index.php');
        exit();
    }
}
?>
<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="utf-8">
  <title>PHP OOP CRUD</title>
  <link rel="stylesheet" href="styles.css">
</head>

<body>
  <main class="container">
    <h1>Edit record</h1>
    <div><a href="index.php">Products list</a></div>
    <div style="color:red"><?= $status ?></div>
    <form action="edit.php" method="post" name="form1">
      <fieldset>
        <div>
          <label for="name">Product name: </label>
          <input type="text" id="name" name="name" value="<?= $crud->h($_SESSION['product']['name']) ?>"
              placeholder="Enter name">
        </div>
<?php
if ($name_error) {
    foreach ($name_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div>
          <label for="price">Price: </label>
          <input type="text" id="price" name="price" value="<?= $crud->h($_SESSION['product']['price']) ?>"
              placeholder="Enter price">
        </div>
<?php
if ($price_error) {
    foreach ($price_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div>
          <label for="description">Description: </label>
          <textarea name="description" rows="2"
              placeholder="Enter description"><?= $crud->h($_SESSION['product']['description']) ?></textarea>
        </div>
<?php
if ($description_error) {
    foreach ($description_error as $msg) {
        echo $msg."<br>";
    }
}
?>
        <div>
          <input type="hidden" name="id" value="<?= $crud->h($_GET['id']) ?>">
          <input type="submit" name="update" value="Update">
        </div>
      </fieldset>
    </form>
  </main>
</body>

</html>

削除

delete.php

<?php
require_once('classes/Crud.php');

$crud = new Crud();

$id = $_GET['id'];

$sth = $crud->delete($_GET['id'], 'products');

if ($sth) {
    header('Location:index.php');
    exit();
}

CSS

styles.css

@charset "utf-8";
* {
  box-sizing: border-box;
}

body,
input,
textarea {
  color: #000;
  font-family: '游ゴシック', YuGothic, 'ヒラギノ角ゴ Pro W3', 'Hiragino Kaku Gothic Pro', 'メイリオ', Meiryo, 'MS Pゴシック', Verdana, sans-serif;
  font-size: 1rem;
  font-weight: normal;
  font-style: normal;
  line-height: 1.8rem;
  word-break: keep-all;
}

table {
  width: 100%;
  margin: 20px 0;
}

table,
tr,
th,
td {
  border: solid 1px #EEE;
  border-collapse: collapse;
  padding: 5px 10px;
}

.container {
  max-width: 960px;
  margin: 0 auto;
  background: #fff;
  padding: 40px;
  box-shadow: 0px 0px 2px rgba(0, 0, 0, 0.4);
  border-radius: 3px;
}


/* form */

fieldset {
  margin: 0;
  padding: 10px 0;
  border: transparent;
}

input[type="text"],
textarea {
  border: 0;
  padding: 5px 15px;
  font-size: 1rem;
  color: #000;
  border: solid 1px #ccc;
  margin: 0 0 10px;
  width: 100%;
  border-radius: 4px;
}

input[type="date"] {
  border: 0;
  padding: 5px 15px;
  font-size: 1rem;
  color: #000;
  border: solid 1px #ccc;
  margin: 0 0 10px;
  border-radius: 4px;
}

input[type="text"]:focus,
input[type="password"]:focus,
textarea:focus {
  box-shadow: inset 0 1px 1px rgba(0, 0, 0, .075), 0 0 5px rgba(102, 175, 233, .6);
}

input[type=submit] {
  padding: 5px 15px;
  margin: 20px 0 0;
  background: #337ab7;
  color: #fff;
  border: 0 none;
  cursor: pointer;
  -webkit-border-radius: 4px;
  border-radius: 4px;
}