1:nのテーブル構造で取り出せる情報を制御したい

良いタイトルが思いつかずこんなタイトルになってしまいました...

動的なページの制作なのですが、全部でテーブルが５つあります。

user_dataテーブル
user_skillテーブル
user_qualiテーブル
user_langテーブル
user_prテーブル

問題点が２つあり、まず１つめは同じ表示が２回されてしまっていることです。

原因は分かっていて、
user_dataテーブルに対して各テーブルがｎなので
１：ｎが成立しているためだと思います。

それを回避する方法が分からないのです。
下記のようにしたいのですが...

スキルは複数あることを想定しているので、
その兼ね合いもどうしたらよいか分からず...

もうひとつの問題は、
テーブルは違うが同じカラム名だった場合、どうやって取り出すか？
です。

下記にソース記述しますが、

の分部で、yearというカラムはuser_skillにもuser_qualiにも存在しますがテーブルは違います。
user_skillテーブルのyearカラムをskill_yearにすればいいだけの話かもしれませんが、
テーブルは違うが同じカラム名だった場合の取り出し方も知っておきたいです。

作りかけですがソースはこうなってます。

php
1$en = filter_input(INPUT_GET, 'EN');
2$sql = "SELECT UD.id,created,name5,name6,birth_year,birth_month,birth_day,gender,pref,city,
3skill,US.year,
4quali,UQ.year,day,
5lang,lang_deg,
6major_category,major_skill,cont_date,introduction
7FROM user_data UD
8LEFT JOIN user_skill US ON UD.id = US.user_id
9LEFT JOIN user_quali UQ ON UD.id = UQ.user_id
10LEFT JOIN user_lang UL ON UD.id = UL.user_id
11LEFT JOIN user_pr UP ON UD.id = UP.user_id
12WHERE UD.id = '$en' ";
13$stmt = $pdo->prepare($sql);
14$stmt->execute();
15
16
17var_dump($sql);
18
19
20foreach ($stmt as $row) {
21				echo '<br /><br />';
22				echo 'EngineerNo.'.$row['id'].'<br />';
23				echo '<img src="" alt="">';
24				$birth = $row['birth_year'].sprintf('%02d', $row['birth_month']).sprintf('%02d', $row['birth_day']);
25				echo '年齢：'.floor((date("Ymd")-$birth)/10000).'歳';
26				echo ' ｜ ';
27				echo '所在：'.$row['pref'].' '.$row['city'];
28				echo ' ｜ ';
29				echo '専攻カテゴリー：'.$row['major_category'];
30				echo ' ｜ ';
31				echo '専攻スキル：'.$row['major_skill'];
32				echo ' ｜ ';
33				echo '契約可能時期：'.$row['cont_date'].'<br /><br />';
34				echo 'PR'.$row['introduction'].'<br /><br />';
35				echo '<hr>';
36
37?>
38<table>
39	<thead>
40		<tr>
41			<th>スキル</th>
42			<th>経験年数</th>
43		</tr>
44	</thead>
45	<tbody>
46		<tr>
47			<td><?php echo $row['skill'] ?></td>
48			<td><?php echo $row['US.year'] ?></td>
49		</tr>
50	</tbody>
51</table>
52<?php
53			}
54?>

テーブル構造は下記の通りです。

user_data | CREATE TABLE user_data (
id int(11) NOT NULL AUTO_INCREMENT,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
email varchar(50) NOT NULL,
password varchar(255) NOT NULL,
name1 varchar(255) DEFAULT NULL,
name2 varchar(255) DEFAULT NULL,
name3 varchar(255) DEFAULT NULL,
name4 varchar(255) DEFAULT NULL,
name5 varchar(255) DEFAULT NULL,
name6 varchar(255) DEFAULT NULL,
middle_name varchar(255) DEFAULT NULL,
birth_year varchar(255) NOT NULL,
birth_month varchar(255) NOT NULL,
birth_day varchar(255) NOT NULL,
gender char(2) NOT NULL,
first_post varchar(255) NOT NULL,
last_post varchar(255) NOT NULL,
pref varchar(255) NOT NULL,
city varchar(255) NOT NULL,
town varchar(255) NOT NULL,
building varchar(255) DEFAULT NULL,
tel1 varchar(255) NOT NULL,
tel2 varchar(255) NOT NULL,
tel3 varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8 |

user_skill | CREATE TABLE user_skill (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
skill varchar(255) DEFAULT NULL,
year varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8 |

user_quali | CREATE TABLE user_quali (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
quali varchar(255) NOT NULL,
year int(11) NOT NULL,
day int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |

user_lang | CREATE TABLE user_lang (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
lang varchar(255) NOT NULL,
lang_deg varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 |

user_pr | CREATE TABLE user_pr (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
major_category varchar(255) NOT NULL,
major_skill varchar(255) NOT NULL,
cont_date varchar(255) NOT NULL,
introduction text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |

【追記】
重複したカラムを取得する方法はASで別名を付けることで解決しました。
２回表示されているものは下記のSQL文に修正することで解決しました。

sql
1SELECT UD.id,created,name5,name6,birth_year,birth_month,birth_day,gender,pref,city,
2skill,US.year AS skill_year,
3quali,UQ.year AS quali_year,day,
4lang,lang_deg,
5major_category,major_skill,cont_date,introduction
6FROM user_data UD
7LEFT JOIN user_skill US ON UD.id = US.user_id
8LEFT JOIN user_quali UQ ON UD.id = UQ.user_id
9LEFT JOIN user_lang UL ON UD.id = UL.user_id
10LEFT JOIN user_pr UP ON UD.id = UP.user_id
11WHERE UD.id = '$en'
12GROUP BY UD.id;

２回表示されなくなった結果、本当は２つあるスキルが１つしか表示されなくなりました。

GROUP BYでスキルが１つしかない状態ですが、実際にはDBに２フィールド登録されています。

これはどう対処したらよろしいのでしょうか？

規模を小さくして考えるとこうなります。

+-----------+------+----+----------+------------+
| ninjya_id |name | id | skill_id | skill_name |
+-----------+------+----+----------+------------+
| 1 |ナルト| 1 | 1 | 螺旋丸 |
+-----------+------+----+----------+------------+
| 1 |ナルト| 2 | 1 |超大玉螺旋丸|
+-----------+------+----+----------+------------+
| 2 |サスケ| 3 | 2 | 千鳥 |
+-----------+------+----+----------+------------+
| 2 |サスケ| 4 | 2 | 天照 |
+-----------+------+----+----------+------------+
| 2 |サスケ| 5 | 2 | 月読 |
+-----------+------+----+----------+------------+

【名前】
ナルト

【技】
螺旋丸
超大玉螺旋丸

とか

【名前】
サスケ

【技】
千鳥
天照
月読

とか表示させたいのです。

技（私の場合ではskillとyear）は人によって複数あるので、ある分だけを表示させたいです。

6/27 0:28 追記

GROUP_CONCATでやりたいことに似たことができました。

$en = filter_input(INPUT_GET, 'EN');
$sql = "SELECT name.id, name AS 'キャラ名', user_id,GROUP_CONCAT(skill) AS '技',GROUP_CONCAT(level) AS 'レベル' FROM name LEFT JOIN skill ON name.id = skill.user_id WHERE name.id = '$en' GROUP BY name.id";
$stmt = $pdo->prepare($sql);
$stmt->execute();

var_dump($sql);

foreach ($stmt as $row) {
	echo '<br /><br />';
	echo 'キャラ名:'.$row['キャラ名'].'<br />';
	echo 'キャラ名:'.$row['技'].'<br />';
	echo 'キャラ名:'.$row['レベル'].'<br />';
}

これは、$row['レベル']内に該当のものがすべて入ってるのですべて出力されますが

キャラ名：ナルト
技名レベル
螺旋丸 LV50
螺旋手裏剣 LV80

という風に分けて表示したいので、
$row['技']内や$row['レベル']内に該当のものがすべて入ってるイメージではなく

螺旋丸なら螺旋丸
螺旋手裏剣なら螺旋手裏剣
ひとつひとつに割り当てられてるイメージでした。

echo 'キャラ名:'.$row['キャラ名'].' ';
echo $row['技-1'].' ';
echo $row['技-2'].' ';
echo $row['レベル-1'].' ';
echo $row['レベル-2'].' ';

のような。

とりあえず分けて表示したいのですがどうしたらよいでしょうか？

mpyw

2016/06/26 12:46

SQLインジェクション脆弱性とXSS脆弱性がありますね…（回答するほどでもないのでご自分でお考えください）

行動規範の内容に同意します

回答2件

SQL分けたらどうですか？
もとのIDだけのシンプルなjoinだけならともかく、集計だの
index効かない条件抽出しだすと
DBとのやり取り回数が増えての負荷増より、DBの処理が軽くなることもありえます。

投稿2016/06/27 00:02

退会済みユーザー

総合スコア0

earnest_gay

2016/06/27 02:52

回答ありがとうございます。 SQL分けるとは、どうのようにでしょうか？ちょっとイメージができず...

退会済みユーザー

2016/06/27 03:23

まず、IDからユーザーのデータだけ１レコード取りますそれとは別に IDからuser_skillのデータを取るSQLで複数ㇾコードを取得して表示すればいいじゃないですか。　同じ名前の項目の衝突もなくなりますよね。

earnest_gay

2016/06/27 03:59

ありがとうございます！

行動規範の内容に同意します

ベストアンサー

MySQL 重複する項目名をSELECT

投稿2016/06/26 11:36

退会済みユーザー

総合スコア0

earnest_gay

2016/06/26 12:05

ありがとうございます！ものすごい分かりやすいです(笑) SELECTのところでASで別名にすればいいのですね... ただ、２回表示されてしまっている方の回避方法が分かりません...

退会済みユーザー

2016/06/26 12:06

先ほど、回答しましたが… GROUP BY です

退会済みユーザー

2016/06/26 12:08

https://www.google.co.jp/search?q=mysql+join+%E3%82%AB%E3%83%A9%E3%83%A0%E5%90%8D%E3%80%80%E9%87%8D%E8%A4%87&ie=utf-8&oe=utf-8&client=firefox-b&gfe_rd=cr&ei=pcVvV8mIBtPD8AeC8JGgBg

earnest_gay

2016/06/26 12:53

色々試しているのですがGROUP BYでまとめたあと、skillやyearの複数のレコードが取り出せません...質問に追記させていただきました。

退会済みユーザー

2016/06/26 13:08

「mysql 集約関数」で検索してみてください。「MariaDB 集約関数」だと記事があまり見つからないと思いますので。 group_concat

earnest_gay

2016/06/26 15:39

すいません... group_concatでやりたいことに似たことはできましたが、詳細を本質問に追記させて頂きましたので、お時間あるときにみていただきたいです...

退会済みユーザー

2016/06/26 15:40

ここからはプログラム側で加工する方が処理の流れはすっきりするでしょう。

earnest_gay

2016/06/26 16:30 編集

echo '技名:'.$row['技'].' '; とひとつの、カラムで技名:螺旋丸,螺旋手裏剣と２つ表示されるのに技名：螺旋丸レベル：LV50 技名：螺旋手裏剣レベル：LV80 と間にまたがって表示させることはできなくないですか？プログラム側で加工するというのが、それが**できる**、と伺えるのであれこれ考えてみましたが今の自分の頭では無理です... それができるということはまず $row['技']に入っている螺旋丸,螺旋手裏剣これらを分けるところから始まると思います。それがどうしてもわからないのです。今の自分にはこれぐらいしか思いつきません... foreach ($stmt as $row) { echo ' '; echo 'キャラ名:'.$row['キャラ名'].' '; $skill =$row['技']; $skill1 = mb_substr($skill, 0, 3); $skill2 = mb_substr($skill, 4, 5); $level =$row['レベル']; $level1 = mb_substr($level, 0, 4); $level2 = mb_substr($level, 5, 4); echo ' '; echo '技名:'.$skill1.' '; echo 'レベル:'.$level1.' '; echo ' '; echo '技名:'.$skill2.' '; echo 'レベル:'.$level2.' '; } これだと、技内容やレベルによっても都度切り取り指定しなければなりませんし、３件４件と変動した時に対応できず全然動的ではないですね...

退会済みユーザー

2016/06/27 03:05

explode() を使って配列にしましょう。

earnest_gay

2016/06/27 03:59

がりがとうございます！

行動規範の内容に同意します

あなたの回答