結合したテーブルに同じ名前のカラムがあっても正しく取り出す方法【MySQL+PHP】

2018.09.29

2020.06.02

二つ以上のテーブルを参照してデータベースのデータを取り出す場合、SQLの「結合」という機能を使います。しかし、二つのテーブルに同じ名前のカラムが存在している場合、結合後のテーブルに同じ名前のカラムが二つ以上現れてしまいます。

テーブルの準備

今回は「test」というデータベースに「author」「book」という二つのテーブルを作成して動作を確認します。


CREATE TABLE author (
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
name varchar(30),
birth_year int
)
DEFAULT CHARSET=utf8;

INSERT INTO author (name, birth_year)
VALUES ("太宰治",1909), ("芥川龍之介",1892), ("夏目漱石",1867);


CREATE TABLE book (
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
name varchar(30),
author_id int
)
DEFAULT CHARSET=utf8;

INSERT INTO book (name, author_id)
VALUES ("こころ",3), ("河童",2), ("蜘蛛の糸",2), ("吾輩は猫である",3), ("人間失格",1);

上記SQL文を実行すると画像のような2つのテーブルが作成されます。

内部結合をやってみる

今回は二つのテーブルだけを参照するので、内部結合を使用します。（結合についての説明は省略します）


SELECT * FROM author
INNTER JOIN book ON author.id = book.author_id;

上記SQL文で結合をおこなうと画像のようなテーブルができあがります。

しかし「id」「name」というカラムが2つになってしまっているため、このままだとphpで正しく取り出すことができません。

AS句を使う


SELECT
author.name AS author,
book.name AS book
FROM author
INNER JOIN book ON author.id = book.author_id;

AS句はカラム名を任意に設定できるsql文です。

これで著作者名、本の名前が別々に取り出せるようになります。しかしこれだと2つ以上の本を書いている著者の場合、phpで出力しようとしても本の名前が一つしか表示されません。

GROUP_CONCAT関数を使う


SELECT
author.name AS author,
GROUP_CONCAT(book.name SEPARATOR ',') AS book
FROM author
INNER JOIN book ON author.id = book.author_id
GROUP BY author.id;

これでカンマ区切りで複数の本の名前が表示されるようになります。

実際にPHPで出力する


<?php
$host = 'localhost';
$dbname = 'test';
$dbuser = 'root';
$dbpass = 'password';

try {
 $dbh = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8mb4", $dbuser,$dbpass, array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
 var_dump($e->getMessage());
 exit;
}

// データ取得
$sql = "
SELECT
author.name AS author,
GROUP_CONCAT(book.name SEPARATOR ',') AS book
FROM author
INNER JOIN book ON author.id = book.author_id
GROUP BY author.id
";
$stmt = ($dbh->prepare($sql));
$stmt->execute();

//あらかじめ配列を生成しておき、while文で回します。
$novelist = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
 $novelist[]=array(
  'author' =>$row['author'],
  'book'=>$row['book']
 );
}

?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>テーブル結合のテスト</title>
</head>
<body>

<ul>
 <?php foreach ($novelist as $value): ?>
 <?php
 if(mb_strpos( $value['book'],"," )) { //カンマが含まれているか判定します。
 //含まれている場合、配列にしたあと区切って出力します。
 $value['book'] = explode(",",$value['book']);
 echo "<li>{$value['author']}は「{$value['book'][0]}」と「{$value['book'][1]}」という小説を書いています。</li>";
 } else {
 echo "<li>{$value['author']}は「{$value['book']}」という小説を書いています。</li>";
 }
 ?>
 <?php endforeach ?>
</ul>

</body>
</html>