PHP, MySQLの複数テーブルの検索

Question

PHPで学習塾のホームページを作成しております。

1. ログインIDで生徒か先生か判別した上でそれぞれトップページに導くログイン機能を実装したいと考えています。しかし、MySQLを用いてteacher, studentのいずれのテーブルにも存在するカラムであるID, passwordの一致を調べる関数がうまく動作しません。複数テーブルから検索はどのような記述になるでしょうか。初歩的な質問で申し訳ございません。

function select_teacher_member($dbh, $id, $password) {
    $sql = 'SELECT * FROM teacher WHERE id = :id LIMIT 1';
　　//ここのFROMの中を変更する？
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(':id', $id, PDO::PARAM_STR);
    $stmt->execute();
    if($stmt->rowCount() > 0 ){
      $data = $stmt->fetch(PDO::FETCH_ASSOC);
      if($password == $data['password']){
          return $data;
      }else{
          return FALSE;
      }
    }else{
        return FALSE;
    }
}

1. 生徒のログイン後、先生を絞り込み検索したいと考えております。そもそも、生徒と先生のテーブルを分けた方がいいでしょうか。分けた場合、IDが重複する可能性があります。分けなかった場合、先生と生徒を判別するカラムを追加する必要があります。先生の絞り込み検索やログイン後のトップページを分けることも見越した場合、いずれの方がより簡便にコード設計できるでしょうか。

よろしくお願いいたします。

CREATE TABLE `teacher` (
  `id` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name1` varchar(20) NOT NULL,
  `name2` varchar(20) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `birthday` date NOT NULL,
  `entry` date NOT NULL,
  `subject` int(3) NOT NULL,
  `email` varchar(100) NOT NULL,
  `message` varchar(200) DEFAULT NULL,
  `photo` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` (`id`, `password`, `name1`, `name2`, `gender`, `birthday`, `entry`, `subject`, `email`, `message`, `photo`) VALUES
('honda', 'honda', '本田圭佑', 'ほんだけいすけ', 1, '1986-06-13', '2018-09-01', 120, '[e-mail]', NULL, NULL),
('ichihara', 'ichihara', '市原悦子', 'いちはらえつこ', 2, '1936-01-24', '1960-01-01', 127, '[e-mail]', NULL, NULL);

CREATE TABLE `student` (
  `id` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name1` varchar(20) NOT NULL,
  `name2` varchar(20) NOT NULL,
  `gender` tinyint(4) NOT NULL,
  `birthday` date NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `password`, `name1`, `name2`, `gender`, `birthday`, `email`) VALUES
('ashida', 'ashida', '芦田愛菜', 'あしだまな', 2, '2004-06-23', '[e-mail]'),
('suzuki', 'suzuki', '鈴木福', 'すずきふく', 1, '2004-06-14', '[e-mail]');

Comments

[Orlofsky]

質問にteacher, studentのCREATE TABLEとINSERTを何件か提示してください。MySQL, SQL タグを追加してください。

[DaisukeKusakari]

修正しました。ご確認お願い致します。

Answer 1

以下のように、ユーザー、ロール（役割）テーブルを作成してログイン機能は共通にするのが定石では？

-- User
CREATE TABLE `user` (
  `userId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `roleId` int(11) unsigned NOT NULL COMMENT 'ロールID',
  `displayName` varchar(64) NOT NULL COMMENT '氏名',
  `email` varchar(128) NOT NULL COMMENT 'メールアドレス',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT 'パスワード',
  `loginFailureCount` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'ログイン失敗回数',
  `loginFailureDatetime` datetime DEFAULT NULL COMMENT 'ログイン失敗日時',
  `deleteFlag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '削除フラグ',
  `createDatetime` datetime NOT NULL COMMENT 'データ作成日時',
  `updateDatetime` datetime NOT NULL COMMENT 'データ更新日時',
  `deleteDatetime` datetime DEFAULT NULL COMMENT 'データ削除日時',
  `createUserId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'データ作成ユーザーID',
  `updateUserId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'データ更新ユーザーID',
  `deleteUserId` int(11) unsigned DEFAULT NULL COMMENT 'データ削除ユーザーID',
  PRIMARY KEY (`userId`) COMMENT 'プライマリーキー',
  UNIQUE KEY `email` (`email`),
  KEY `createUserId` (`roleId`),
  KEY `createUserId` (`createUserId`),
  KEY `updateUserId` (`updateUserId`),
  KEY `deleteUserId` (`deleteUserId`),
  KEY `deleteFlag` (`deleteFlag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザー';

CREATE TABLE `Role` (
  `roleId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `roleName` varchar(64) NOT NULL DEFAULT '' COMMENT 'ロール名',
  `deleteFlag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '削除フラグ',
  `createDatetime` datetime NOT NULL COMMENT 'データ作成日時',
  `updateDatetime` datetime NOT NULL COMMENT 'データ更新日時',
  `deleteDatetime` datetime DEFAULT NULL COMMENT 'データ削除日時',
  `createUserId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'データ作成ユーザーID',
  `updateUserId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'データ更新ユーザーID',
  `deleteUserId` int(11) unsigned DEFAULT '0' COMMENT 'データ削除ユーザーID',
  PRIMARY KEY (`roleId`),
  KEY `createUserId` (`createUserId`),
  KEY `updateUserId` (`updateUserId`),
  KEY `deleteUserId` (`deleteUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ロール';

Answer 2

そもそものアプリケーション仕様が確定していないから、変なところで悩むことになります。
アプリケーションの仕様が確定していれば、テーブルを1つにするか分けるかは、自明になるかと。

2つに分けているのであれば、その設計根拠があるはずなので、まずそこを整理することからはじめてみると良いです。

以下妄想
テーブルを分けていることから、先生用のアプリケーションと、生徒用のアプリケーションはほぼ独立したアプリケーションになっているのだと思います。
ので、ややこしいことはしないで、入り口を2つに分けてあげるとシンプルな設計で実現できます。

Comments

[DaisukeKusakari]

ご回答ありがとうございます。質問が不明瞭で、申し訳ございません。

アプリケーションの仕様としては、下記を想定しています。
共通：ログイン機能、スケジュール（カレンダー）管理、チャット機能
先生側：
予定（個別指導の日程）のアップロード
生徒側：
先生の絞り込み検索（年齢、教えられる教科など）-->個別指導予約

ログイン画面に生徒・先生のチェックボックスを作って入口を分ける手もありですが、ユーザーにひと手間を加えたくないなとも思いました。

[退会済みユーザー]

> アプリケーションの仕様としては、下記を想定しています
アプリケーションの仕様と呼べる段階ではないです。
要求機能の段階で思考停止しているので、きちんと要件定義まで落とし込んでください。

例えば、要求機能の一部に
・先生のスケジュール集計を一日一度実施しなければならない
・生徒の名簿入れ替えが、春夏冬休み毎に発生する
等があって、アプリケーションの仕様を
・集計時間帯のログインを停止する
・名簿入れ替え時間帯のログインを停止する
・先生/生徒のそれぞれのメンテナンス時間帯に他方はログイン可能
とすると、ログイン画面が同じより、別であったほうがメンテナンス告知が楽であることに気が付きます。（まぁ、この場合、「ログイン画面を分ける」までがアプリケーションの使用になるので、例としては不適切ですが＾＾；）

また、先生と生徒では名簿の保つ意味も違ってくるはずです。
例えば、名簿から削除される場合
・先生→退職（復職を考慮せず。物理削除）
・生徒→受講終了（再登録の可能性あり。論理削除）
とかの処理になるのであれば、テーブルは分けておいたほうがメンテナンスしやすいです。

ちゃんと設計しましょう。

[DaisukeKusakari]

当方、まったくの初心者ですので非常に具体的に例示していただけると参考になります。ありがとうございます。

・先生のスケジュール集計を一日一度実施しなければならない
特に集計する予定はありませんが、スケジュール入力依頼を2週間に1度、定期的に実施しなければなりません。それに対して、先生が適宜自らの予定を更新していきます。

・生徒の名簿入れ替えが、春夏冬休み毎に発生する
名簿は随時変更されていきます。

・先生→退職（復職を考慮せず。物理削除）
これは、非常に重要な機能だと感じました。先生にアクティブ・非アクティブ（求職中か否か）の論理設計を加えようと思います。

Answer 3

テーブルはひとつでも良いと思いますが
権限の違いを生徒と先生で作ることが出来れば大丈夫だと思います。
intで1~100の間なら生徒、それ以上であれば先生のような分岐でやりたいことができるかと

Comments

[退会済みユーザー]

プレースホルダをシングルクオートで囲んじゃ、ダメ。

[ousetsuaki]

失礼しました！

[DaisukeKusakari]

ousetsuakiさんご回答ありがとうございます。

luckerさんはテーブルの分離の是非についていかが思われますか。どちらでもいいといえばそうなのかもしれませんが…ちなみに、生徒テーブルに必要なカラムはすべて先生テーブルのカラムに含まれテーブルいます。