【MySQL】2つのテーブルをJOINで結合した例

mysql

よくPHPでログインしてメッセージを投稿する簡単なアプリケーションを見ることがあります。その際はMySqlで登録したユーザーを管理する為のテーブルと投稿されたメッセージを管理する為のテーブルを作成するかと思います。今回はその２つのテーブルを結合して表示させるサンプルを防備ログとして残す。

ただし、今回はあくまで2つのテーブルを結合させることが目的なのでPHPによるログインの実装などは載せないことにする

実際にやりたいこと

今回の実装するにあたり以下のテーブルを作成しました。

  • 登録したユーザー名などを管理する為のUSERSテーブル
  • 投稿したメッセージなどを管理する為のMESSAGESテーブル

それを踏まえた上で、ユーザーがメッセージを投稿するとメッセージ内容とログインしているユーザー名を表示させます。（ユーザー名とメッセージは別々のテーブルで管理）

以下がサンプルの動画です。
ユーザー名とメッセージは別々のテーブルで管理してますが、メッセージとユーザーネームが表示されています。これはテーブルを結合させている訳ですがこちらのサンプルを載せていきたいと思います。

ユーザーを管理するテーブル

では実際にテーブルを紹介しますが、ユーザーを管理するテーブル名をusersとして作成。
idは登録した際の自動的に加算されるカラムですが、今回はユーザー名が登録されるusernameのカラムを結合で表示させたいところです。

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| mail     | varchar(20) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

メッセージを管理する為のテーブル

メッセージを管理する為のテーブル名をmessagesとして以下のように作成。
idは、メッセージが投稿された際に自動的に加算さえるカラムです。
messageは投稿されたメッセージを保存するカラムです。
user_idは、ユーザーテーブルのid（自動的に加算される）を登録する為のカラムで今回はここがポイントになります。

+---------+--------------+------+-----+---------------------+----------------+
| Field   | Type         | Null | Key | Default             | Extra          |
+---------+--------------+------+-----+---------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id | int(11)      | NO   |     | NULL                |                |
| message | varchar(255) | NO   |     | NULL                |                |
| posted  | timestamp    | NO   |     | current_timestamp() |                |
+---------+--------------+------+-----+---------------------+----------------+

結合するポイントとは？

では、実際に上記の2つのテーブルを結合させて、投稿したユーザーとメッセージを一覧に表示させにはどうしたらいいのか？

それは、usersテーブルにあるidカラムとmessagesテーブルのuser_idカラムを結合の条件にするところがポイントとなります。
以下の画像を参考にしていただきたいのですが、以下の例だとusersテーブルの「id」が「2」のユーザーがmessagesテーブルだと２つあります。これは「id」が「2」のユーザーが2回投稿したことになります。

2つのテーブルの関連性

実際にPHPでどのようなSQL文で結合させるのか？

では実際にPHPではどのようにテーブルを結合させているのかというと以下のようなSQLです。
＊order by posted desc に関してpostedが投稿した時間のカラムで並び替えを行ってますがこちらの説明は省きます。

//テーブルを結合して読み込み
$sql="select messages.id,message,username from messages join users on messages.user_id=users.id order by posted desc";

取り出したい情報は、以下です。

  • 投稿数を表示したいのでmessagesテーブルのID 「messages.id」カラム
  • 投稿したメッセージ　messagesテーブルの「message」カラム
  • ユーザー名を表示させたいのでusersテーブルの「username」カラム

messages.id に関しては「id」だけだとusersテーブルにも「id」カラムが存在するので、messagesテーブルを指すように「.」で繋げます。

このように表示させるにはJOINを使ってテーブルを結合する必要があります。
JOINを構文は以下です。

SELECT カラム１,カラム2, FROM テーブル名 JOIN テーブル名　ON 結合の条件　　

結合条件に関しては、「on messages.user_id=users.id」という部分で、messagesテーブルのuser_idとusersテーブルのidカラムが等しいかどうかを条件とし、合致する場合は結合されて1つのテーブルのように指定したカラムの情報が取り出せるようになります。