UNION ALLで結合したSELECT文で、複数のテーブルからデータを取得したい

前提・実現したいこと

テキストを参考に、ショッピングサイトを作成している初学者です。
商品をいくつか選んでカートに入れ、「カートを見る」ボタンをクリックすると、遷移したページでその内容が一覧できるようになっています。テキストではデータベースの商品テーブルがひとつだけですが、アレンジして商品のカテゴリごとに複数のテーブルを作り、UNION ALLで結合してみたところ、思ったように動かず躓いています。各テーブルのカラムはすべて同じもので統一してあります。

発生している問題・エラーメッセージ

例えばソースコードのように３つのテーブル（category1, 2, 3）を作成すると、選んだ商品が表示されず、なぜか最初のテーブル（category1）の最初の商品のレコードが、選んだ商品の数だけ繰り返して表示されます。

エラーメッセージ

該当のソースコード

PHP
ソースコード
<?php
session_start();
session_regenerate_id(true);
?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>SHOP</title>
</head>
<body>

<?php
$cart=$_SESSION['cart'];
$max=count($cart);

$dsn='mysql:dbname=shop;host=localhost;charset=utf8';
$user='root';
$password='';
$dbh=new PDO($dsn,$user,$password);
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

foreach($cart as $key=>$val)
{
  $sql='SELECT code,name,price FROM category1 
  UNION ALL
  SELECT code,name,price FROM category2 
  UNION ALL
  SELECT code,name,price FROM category3 
  WHERE code=?';
	$stmt=$dbh->prepare($sql);
	$data[0]=$val;
	$stmt->execute($data);

	$rec=$stmt->fetch(PDO::FETCH_ASSOC);

	$pro_name[]=$rec['name'];
	$pro_price[]=$rec['price'];
}
$dbh=null;
?>

カートの中身<br>
<br>
<?php for($i=0;$i<$max;$i++)
	{
?>
	<?php print $pro_name[$i]; ?>
	<?php print $pro_price[$i]; ?>円
	<br>
<?php
	}
?>
</body>
</html>

試したこと

SESSION関数には、選んだ商品の一意のcodeが渡されてくるので、その後の変数の値をvar_dumpで調べてみました。すると、$data[0]=$val;と$stmt->execute($data);の$dataには確かに選んだ商品のcodeが配列で入っていましたが、その下の$recには最初のテーブル（category1）の最初の商品のレコードの配列が、選んだ商品の数だけ入っていました。
$dataに選んだ商品のcodeが入っているので、UNION自体に問題があるわけではないと思います。また、$rec=$stmt->fetch(PDO::FETCH_ASSOC);もテーブルがひとつの時は正しく動いたので、これ自体にも問題があるわけではないと思います。そうするとフェッチの設定が、UNION結合にした場合、思うように動いていないということでしょうか。原因と改善方法についてアドバイスを頂けると幸いです。

補足情報（FW/ツールのバージョンなど）

XAMPP for WIndows 7.3.19 / PHP 7.3.19

takasima20

2020/10/28 11:03

この SQL だと WHERE が category3 にしかかかってなくないスか?

schuloco

2020/10/29 03:17 編集

早速のご指摘有難うございました。

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

回答1件

ベストアンサー

テキストではデータベースの商品テーブルがひとつだけですが、アレンジして商品のカテゴリごとに複数のテーブルを作り、UNION ALLで結合してみた

態々正規化されているテーブルを壊しているから、余計なunionが必要になるんです。
先ずは、そこから正しましょう。
カテゴリコードを商品テーブルに追加すれば、テーブルを分ける必要がなくなります。

質問のSQLではwhere 条件はcategory3に対してのみです。
一つ一つに条件を付けるか、以下の様に一旦括ったものに対しての条件にします。

SQL
1select * from (
2  SELECT code,name,price FROM category1 
3  UNION ALL
4  SELECT code,name,price FROM category2 
5  UNION ALL
6  SELECT code,name,price FROM category3
7) mrg 
8 WHERE code=?

投稿2020/10/28 11:42

編集2020/10/28 12:48

sazi

総合スコア25173

schuloco

2020/10/28 21:32

早速のご指摘有難うございました。 $dataまでは選んだ商品のcodeが正しく入っていたので、UNION ALLはこれでいいのかと思っていました。逆に何故かと疑問が生じました。余計なことをしたのは、商品数が多い大手のショッピングサイトなどではデータベースをどのように管理しているのだろうという発想からです。すべてがひとつのテーブルに集中したら支障が出るのではないかと。素人の発想ですが。

sazi

2020/10/29 03:45 編集

カテゴリーは別途マスターを作成し、それに対して商品から外部参照するのが一般的です。正規化に於いて何がキーで何が属性なのかを明確にするようにします。

schuloco

2020/10/29 03:13

マスターを外部参照するのが一般的とのこと、再度の回答有難うございます。自分でもよく調べて、恥ずかしくないデータベースを作れるよう努めたいと思います。

sazi

2020/10/29 03:53

> すべてがひとつのテーブルに集中したら支障が出るのではないかと。正規化をすると、逆に全てが一つに集中はせず、キーに応じた分散がされます。それでもデータ件数が多いとレスポンスは問題になります。そういった場合、正規化は崩さずパーティショニングするか、NoSQLで対応します。最初から膨大な件数が見込まれるなら、それなりの設計者が担当する事になるでしょうね。

schuloco

2020/11/15 06:59

今日新たな質問を投稿した際に、上記コメントに気づきました。見過ごしていて失礼しました。データベースも初心者なので、パーティショニングやNoSQLも恥ずかしながら初めて聞く言葉です。しかし興味はあるので、これから知識を増やしていきたいと思います。改めてアドバイスを有難うございました。

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