【6日でできるPHP入門】アプリを作成する

CREATE DATABASE IF NOT EXISTS company;
USE company;

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  department VARCHAR(64),
  position VARCHAR(64)
);

-- 初期データ追加例
INSERT INTO employee (id, name, department, position) VALUES
  (1001, '佐藤健太', '営業', '主任'),
  (1002, '山田花子', '開発', 'エンジニア'),
  (1003, '鈴木一郎', '総務', 'マネージャー');

SELECT * FROM company.employee;

company_app/
├── common/
│   ├── data_check.php
│   ├── dbmanager.php
│   └── html_functions.php
├── common.php
├── index.php
├── post_data.php
├── employee_delete.php
├── employee_edit.php
├── employee_input.php
└── employee_update.php

index.php
  ├─> [新規登録] ─> employee_input.php ─> post_data.php ─┬─> index.php
  └─> [氏名リンク] ─> employee_edit.php
         ├─> [編集] ─> employee_update.php ─> post_data.php ─> index.php
         └─> [削除] ─> employee_delete.php ─> post_data.php ─> index.php

<?php
require_once("common.php");
show_top("社員一覧");

// 全社員の情報を取得
$members = $dbm->get_all_employees();
if ($members != null) {
    show_employee_list($members);
}
echo '<a href="employee_input.php">新しい社員の登録</a>';

show_bottom();
?>

<?php
require_once("common.php");

$id = $_GET["id"] ?? "";
$name = $_GET["name"] ?? "";
$department = $_GET["department"] ?? "";
$position = $_GET["position"] ?? "";

show_top("社員情報の追加");
// 入力フォームの表示
show_employee_input($id, $name, $department, $position);
show_bottom(true);
?>

<?php
require_once("common.php");
$id = $_GET["id"];
$employee = $dbm->get_employee($id);

show_top("社員情報の選択");

show_employee($employee);
show_employee_operations($id);

show_bottom(true);
?>

<?php
require_once("common/html_functions.php");
require_once("common/dbmanager.php");
require_once("common/data_check.php");

// エラー取得関数
function get_error() {
    $error = "";
    if (isset($_GET["error"])) {
        $error = $_GET["error"];
    }
    return $error;
}

// DBManagerのインスタンスを生成
$dbm = new DBManager();
?>

<?php
function check_employee_input($id, $name, $department, $position, &$error) {
    $error = "";
    if ($id === "" || $name === "" || $department === "" || $position === "") {
        $error = "未入力の項目があります。";
        return false;
    }
    // 社員番号は4桁の数字であることをチェック
    if (!preg_match("/^[1-9][0-9]{3}$/", $id)) {
        $error = "社員番号は1～9で始まる4桁の数字で入力してください。";
        return false;
    }
    return true;
}
?>

<?php
require_once("common.php");
$old_id = $_GET["id"];
$employee = $dbm->get_employee($old_id);

$id = $employee["id"];
$name = $employee["name"];
$department = $employee["department"];
$position = $employee["position"];

show_top("社員情報の編集");
show_employee_update($id, $name, $department, $position, $old_id);
show_bottom(true);
?>

<?php
require_once("common.php");
$id = $_GET["id"];
$employee = $dbm->get_employee($id);

show_top("社員情報の削除");
show_employee_delete($employee);
show_bottom(true);
?>

<?php
require_once("common.php");

if (isset($_POST["action"])) {
    // 入力値の取得
    $id = $_POST["id"] ?? "";
    $name = $_POST["name"] ?? "";
    $department = $_POST["department"] ?? "";
    $position = $_POST["position"] ?? "";
    $old_id = $_POST["old_id"] ?? "";

    // 登録処理
    if ($_POST["action"] == "create") {
        if (!check_employee_input($id, $name, $department, $position, $error)) {
            header("Location: employee_input.php?error={$error}");
            exit();
        }
        if ($dbm->if_id_exists($id)) {
            $error = "すでに登録されている社員番号です。";
            header("Location: employee_input.php?error={$error}");
            exit();
        }
        if (!$dbm->insert_employee($id, $name, $department, $position)) {
            $error = "データベースエラーが発生しました。";
            header("Location: employee_input.php?error={$error}");
            exit();
        }
        header("Location: index.php");
        exit();

    // 更新処理
    } else if ($_POST["action"] == "update") {
        if (!check_employee_input($id, $name, $department, $position, $error)) {
            header("Location: employee_update.php?error={$error}&id={$old_id}");
            exit();
        }
        if ($dbm->if_id_exists($id) && $id != $old_id) {
            $error = "すでに登録されている社員番号です。";
            header("Location: employee_update.php?error={$error}&id={$old_id}");
            exit();
        }
        $dbm->update_employee($id, $name, $department, $position, $old_id);
        header("Location: index.php");
        exit();

    // 削除処理
    } else if ($_POST["action"] == "delete") {
        if (!$dbm->delete_employee($id)) {
            $error = "データベースエラーが発生しました。";
            header("Location: employee_delete.php?error={$error}&id={$id}");
            exit();
        }
        header("Location: index.php");
        exit();
    }
}
?>

<?php
class DBManager {
    private $access_info;
    private $user;
    private $password;
    private $db = null;

    function __construct() {
        $this->access_info = "mysql:host=localhost;dbname=company";
        $this->user = "root";
        $this->password = "root";
    }

    // DB接続
    private function connect() {
        $this->db = new PDO($this->access_info, $this->user, $this->password);
    }
    private function disconnect() {
        $this->db = null;
    }

    // 全社員取得
    function get_all_employees() {
        try {
            $this->connect();
            $stmt = $this->db->prepare("SELECT * FROM employee ORDER BY id;");
            $stmt->execute();
            $members = $stmt->fetchAll();
            $this->disconnect();
            return $members;
        } catch(PDOException $e) {
            $this->disconnect();
            return null;
        }
    }

    // 指定社員取得
    function get_employee($id) {
        try {
            $this->connect();
            $stmt = $this->db->prepare("SELECT * FROM employee WHERE id = ?;");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            $members = $stmt->fetchAll();
            $this->disconnect();
            if (count($members) === 0) return null;
            return $members[0];
        } catch(PDOException $e) {
            $this->disconnect();
            return null;
        }
    }

    // 社員番号の存在確認
    function if_id_exists($id) {
        return $this->get_employee($id) !== null;
    }

    // 社員登録
    function insert_employee($id, $name, $department, $position) {
        try {
            $this->connect();
            $stmt = $this->db->prepare("INSERT INTO employee VALUES (?, ?, ?, ?);");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->bindParam(2, $name, PDO::PARAM_STR);
            $stmt->bindParam(3, $department, PDO::PARAM_STR);
            $stmt->bindParam(4, $position, PDO::PARAM_STR);
            $stmt->execute();
            $this->disconnect();
            return true;
        } catch(PDOException $e) {
            $this->disconnect();
            return false;
        }
    }

    // 社員情報の更新
    function update_employee($id, $name, $department, $position, $old_id) {
        try {
            $this->connect();
            $stmt = $this->db->prepare("UPDATE employee SET id = ?, name = ?, department = ?, position = ? WHERE id = ?;");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->bindParam(2, $name, PDO::PARAM_STR);
            $stmt->bindParam(3, $department, PDO::PARAM_STR);
            $stmt->bindParam(4, $position, PDO::PARAM_STR);
            $stmt->bindParam(5, $old_id, PDO::PARAM_INT);
            $stmt->execute();
            $this->disconnect();
            return true;
        } catch(PDOException $e) {
            $this->disconnect();
            return false;
        }
    }

    // 社員情報の削除
    function delete_employee($id) {
        try {
            $this->connect();
            $stmt = $this->db->prepare("DELETE FROM employee WHERE id = ?;");
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            $this->disconnect();
            return true;
        } catch(PDOException $e) {
            $this->disconnect();
            return false;
        }
    }
}
?>

<?php
// HTMLヘッダー
function show_top($title = "社員管理") {
    echo <<<HEADER
<html>
<head><title>{$title}</title></head>
<body>
<h1>{$title}</h1>
HEADER;
}

// HTMLフッター
function show_bottom($return_top = false) {
    if ($return_top) {
        echo '<a href="index.php">社員一覧に戻る</a><br>';
    }
    echo <<<FOOTER
</body>
</html>
FOOTER;
}

// 社員一覧テーブル表示
function show_employee_list($members) {
    echo '<table border="1" style="border-collapse:collapse">';
    echo '<tr><th>社員番号</th><th>名前</th><th>所属</th><th>役職</th></tr>';
    foreach ($members as $row) {
        echo "<tr align='center'>";
        echo "<td>{$row['id']}</td>";
        echo "<td><a href='employee_edit.php?id={$row['id']}'>{$row['name']}</a></td>";
        echo "<td>{$row['department']}</td>";
        echo "<td>{$row['position']}</td>";
        echo "</tr>";
    }
    echo '</table><br>';
}

// 新規登録フォーム
function show_employee_input() {
    $error = get_error();
    echo <<<FORM
<form action="post_data.php" method="post">
<p>社員番号<br><input type="text" name="id" placeholder="例：1001"></p>
<p>名前<br><input type="text" name="name" placeholder="例：佐藤一郎"></p>
<p>所属<br><input type="text" name="department" placeholder="例：営業部"></p>
<p>役職<br><input type="text" name="position" placeholder="例：課長"></p>
<p style="color:red;">{$error}</p>
<input type="hidden" name="action" value="create">
<input type="submit" value="登録">
</form>
FORM;
}

// 編集フォーム
function show_employee_update($id, $name, $department, $position, $old_id) {
    $error = get_error();
    echo <<<FORM
<form action="post_data.php" method="post">
<p>社員番号<br><input type="text" name="id" value="{$id}"></p>
<p>名前<br><input type="text" name="name" value="{$name}"></p>
<p>所属<br><input type="text" name="department" value="{$department}"></p>
<p>役職<br><input type="text" name="position" value="{$position}"></p>
<p style="color:red;">{$error}</p>
<input type="hidden" name="old_id" value="{$old_id}">
<input type="hidden" name="action" value="update">
<input type="submit" value="更新">
</form>
FORM;
}

// 削除フォーム
function show_employee_delete($employee) {
    $error = get_error();
    echo '<table border="1" style="border-collapse:collapse">';
    echo '<tr><th>社員番号</th><th>名前</th><th>所属</th><th>役職</th></tr>';
    echo "<tr align='center'>";
    echo "<td>{$employee['id']}</td>";
    echo "<td>{$employee['name']}</td>";
    echo "<td>{$employee['department']}</td>";
    echo "<td>{$employee['position']}</td>";
    echo "</tr></table><br>";
    echo <<<FORM
<form action="post_data.php" method="post">
<p style="color:red;">{$error}</p>
<input type="hidden" name="id" value="{$employee['id']}">
<input type="hidden" name="action" value="delete">
<input type="submit" value="削除">
</form>
FORM;
}

// 社員詳細
function show_employee($member) {
    if ($member === null) {
        echo "<p>該当する社員データがありません。</p>";
        return;
    }
    // ここから従来のテーブル出力
    echo "<table border='1'>";
    echo "<tr><th>社員番号</th><th>名前</th><th>所属</th><th>役職</th></tr>";
    echo "<tr align='center'>";
    echo "<td>{$member['id']}</td>";
    echo "<td>{$member['name']}</td>";
    echo "<td>{$member['department']}</td>";
    echo "<td>{$member['position']}</td>";
    echo "</tr>";
    echo "</table><br>";
}

// 編集・削除操作
function show_employee_operations($id) {
    echo "<a href='employee_update.php?id={$id}'>情報の編集</a><br>";
    echo "<a href='employee_delete.php?id={$id}'>情報の削除</a><br><br>";
}
?>

http://localhost/company_app/index.php