正規化されたデータを元に戻したい。

Question

お世話になります。少し意味合いが違いますが、タイトルが「正規化されたデータを元に戻したい」と変なこと言っていますことご了承ください。

他に適切な言い回しが思いつかなかったわけですが、正規なのようにタイプ別に振られたデータの取扱い、そのデータの加工を知りたく質問させて頂きます。

以下のように、type番号が振られた食品テーブルがあります。

no name type
1 イチゴ 1
2 バナナ 1
3 キュウリ 2
4 みかん 1
5 きゃべつ 2
6 和牛 3
7 レタス 2
8 黒豚 3
9 鶏肉 3

ちなみに、type1は果物、type2は野菜、type3は精肉です。

上記のSQLは以下の通りです。

CREATE TABLE `food` (
  `no` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `food` (`no`, `name`, `type`) VALUES
(1, 'イチゴ', 1),
(2, 'バナナ', 1),
(3, 'キュウリ', 2),
(4, 'みかん', 1),
(5, 'きゃべつ', 2),
(6, '和牛', 3),
(7, 'レタス', 2),
(8, '黒豚', 3),
(9, '鶏肉', 3);

対して、くだもの、やさい、精肉などを管理するfloorというテーブルがあり、

UPDATE floor SET area = 'イチゴ,バナナ,みかん' WHERE type = '1'

このようなSQLでtype別にデータを挿入したいと考えています。

※他に良い例えがないため、正規化を戻すような説明になっていますが、実際の用途は違うのです。

話が横道に反れました。
続けます。

そこで、

SELECT * FROM `food` ORDER BY type, no

といったSQLで

no name type
1 イチゴ 1
2 バナナ 1
4 みかん 1
3 キュウリ 2
5 きゃべつ 2
7 レタス 2
6 和牛 3
8 黒豚 3
9 鶏肉 3

とSELECTした時に、while()の中で廻しながら、(恥を忍んで公開します）

$name = $row["name"];
$type = $row["type"];

if($type != $mae_type AND is_array($name_array)){

    $names = implode(",", $name_array);

    $query2 = "UPDATE floor SET
     lot_img_sub = '$names' 
     WHERE lot_no = '$mae_type'";
    mysqli_query($mysqli, $query2);

    unset($name_array);
    $name_array = array();
}
else{

    if(isset($mae_type) AND $type == $mae_type){

        $name_array[] = $name;
    }
}


//繰り返し処理における次へのデータの引き渡し＠typeの比較用
$mae_type = $row["type"];

floorレコードに代入（UPDATE)しようと試していますが、思うような結果が得られずにおります。

色々と試している中で、floorへは違うTypeの違うものが代入されたり、数が足りなく代入されたりとしていますが、そもそも、こういった手法に誤りがおるのでは？という思いが多く残り質問させて頂きました。

お忙しい中恐縮ですが、アドバイスのほど頂戴出来れば幸いです。
よろしくお願いも仕上げます。

Answer 1

group_conncat()が活用できそうです。
値を取得するには以下です。

select type, group_concat(name order by no) as names
from food
group by type

updateの条件が良く分かりませんが、update文に上記を組み込めばupdateのSQLのみで更新も可能だと思います。

update floor t1 inner join (
  select type, group_concat(name order by no) as names
  from food
  group by type
) t2
on  t1.type=t2.type
set t1.lot_img_sub=t2.names
where lot_no = XXX

因みに、テーブル設計で分かっていて行う分には、「正規化を緩める」と言います。
この場合の言い回しは違うと思いますけど。
「行を集約」とか「配列化」とかかな。

Comments

[chapp]

saziさん

質問後、早々のアドバイスをありがとうございます。
しかもSQLまででご紹介いただき、さらに「UPDATE」も教えていただき恐縮しています。

今回お教えいただいた関数もそうですが、一度にUPDATE出来るとは、改めて知識が乏しいこと思い知らされました。

今回お教えいただいたUPDATEも含め、理解を深めたいと思います。
ありがとうございました。

Answer 2

タイトルは「縦並びを横並びにしたい」とでも修正されては？

MySQL GROUP_CONCAT
で横並びにSELECTできます。その結果をテーブルにUPDATEする必要があるのかは疑問です。
野菜が増えたり、肉類が減ったりすることもあるので必要な時にSELECTし直せば良いのでは？

Comments

[chapp]

Orlofskyさん

質問後、早々のアドバイスをありがとうございます。
こういった関数があるのですね。マニュアルも一通りみないと・・・改めて思い知らされました。
ありがとうございました。