少し前にクイズ作成アプリを公開しました。
データベースと連携してクイズ問題や解答を表示する仕組みなので、日々SQLとの戦いでした。
色々な山場があったものの、中でもテーブルの「内部結合」でかなり苦労しました🥺
この記事では、僕の経験をもとにして、PHPで開発する際にテーブルを内部結合してデータを取得する具体的な方法について説明したいと思います。
実装したいこと
- 複数のテーブルから情報を取得して、一つの表にまとめる
こちらが完成品です。
- テーブルをまとめる = テーブルの結合 さらに、
- 共通項目のみ抽出したい→内部結合
ですね。
「内部結合」で検索してみると、理論的な説明記事はたくさんありますよね。
しかし、いざ記事をもとに作ろうとしても、前提条件が少し異なっていたり、うまく動作しなかったりと、苦労しました。
僕は複数の記事を読み漁ってなんとか実装しましたが、丸一日ほどかかってしまいました。。
このような経験から、ポイントを絞った上でのシンプルな解説であれば需要があるかなと思い、僕が書いたコードをシェアしたいと思います。
具体的には、次のような場合に的を絞って記事を書いています。
- ・PHPを使った実装
- ・複数のテーブルからデータを取得したい
- ・取得したデータを使って1つの表を作成したい
とはいえ、予備知識がないと難しいと思いますので、一応SQLの基礎を勉強したことがある方を想定しています。
内部結合の対象
テーブルを内部結合するのですが、イメージしやすくするために、フロントエンドの実装からお話しします。
クイズ選択画面
解答画面
結果画面
上記の画面では、主に次の3つのテーブルからデータを取得＆更新しています。
- ・options
- ・answer
- ・questions
それぞれのテーブルの詳細です。
questionsテーブル
- ・question_id: 問題ID
- ・question_details: 問題の内容
- ・quiz_id: クイズのID
optionsテーブル
- ・option_id: 選択肢のID
- ・option_details: 選択肢の内容
- ・option_name: 選択肢の名前(ex. ア)
- ・question_id: 対応する問題のID
answerテーブル
- ・answer_id: 解答ID
- ・question_id: 対応する問題のID
ちなみに、、
- ＊option_id = answer_idのとき正解ですね。
実行したいこと。
問題を解き終わったときに、以下の3つの情報をまとめた表を表示したい。
- 1.クイズの全問題
- 2. 正解選択肢
- 3.正解選択肢の内容
しかし、上記の3つの情報を持つテーブルは存在しません。
したがって、複数のテーブルから情報を集める必要があります。
具体的には、テーブルを結合して1つの大きな仮想テーブルを作成し、そのテーブルから情報を取得します。
イメージは湧いてきましたか？
より具体性を持たせるために、実際のデータを使って説明します。
questionsテーブル
optionsテーブル
answerテーブル
この3つのテーブルを結合して、次のような仮想テーブルを作ります。
ポイント
- 各テーブルの共通項目を通じて、テーブル同士を結合する
answerテーブルとquestionsテーブルの共通項。
- answerテーブルのquestion_id = questionsテーブルのquestion_id
answerテーブルとoptionsテーブルの共通項。
- answerテーブルのanswer_id = optionsテーブルのoption_id
この2つの共通項を通じて、3つのテーブルを結合できそうです。
コードを書いて内部結合する
テーブルを内部結合するためのコードを書いていきましょう！
手順１：データベースに接続
<?php
$dbh = new PDO('mysql:host=ホスト名;dbname=データベース名, ‘ユーザー名’, ‘パスワード’);手順２：テーブルを結合
共通項目を利用して3つのテーブルを結合します。
ここが一番大変でした。。
$sql = "SELECT
options.option_details AS answer,
options.question_id AS question_id,
options.option_name AS option_name,
questions.question_deitails AS question,
questions.quiz_id AS quiz_id
FROM answer
INNER JOIN questions
ON answer.question_id = questions.question_id
INNER JOIN options
ON answer.answer_id = options.option_id
WHERE quiz_id ='" . $quiz_id . "'";
$stmt = ($dbh->prepare($sql));
$stmt->execute();これで結合テーブルが出来上がりました。
実際には存在しないテーブルですので、一時的な仮想テーブルを作成しているというイメージですね。
手順３：配列にデータを格納
次に配列を生成し、while文を使い、結合テーブルのデータを格納します。
$answers = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$answers[]=array(
'answer' =>$row['answer'],
'question' =>$row['question'],
'question_id'=>$row['question_id'],
'option_name'=>$row['option_name']
);
}?>
（＊すみません、上記の$quiz_id＝クイズIDは別途取得済みとさせてください。）
手順４：取得データを表示する
あとはforeachを使ってデータを表示すればOKですね。
<table class="quiz-table">
<tr>
<th>#</th>
<th>問題</th>
<th>答え</th>
<th>選択肢</th>
</tr>
<?php $c=1; foreach ($answers as $value): ?>
<tr>
<td>
<?php echo $c++ ?>
</td>
<td>
<?php echo $value['question'] ?>
</td>
<td>
<?php echo $value['answer'] ?>
</td>
<td>
<?php echo $value['option_name'] ?>
</td>
</tr>
<?php endforeach ?>
</table>
これで、記事の最初に見た結果画面が完成します。
ぜひクイズを実際に解いて確認してみてください！！
