カテゴリ、ツリー構成の孫まであるものをHTML出力がどんなに悩んでもできません。

Question

いつもお世話になっております。

今回のご質問なのですがカテゴリを最終的に

<ul>
	<li><a href="あああ">あああ(コンテンツ数)</a></li>
	<li>
		<dl>
			<dt><a href="あああ">あああ(コンテンツ数)</a></dt>
			<dd>
				<ul>
					<li><a href="いいい">-いいい(コンテンツ数)</a></li>
					<li><a href="いいい">-いいい(コンテンツ数)</a></li>
					<li><a href="いいい">-いいい(コンテンツ数)</a></li>
				</ul>
			</dd>
		</dl>
	</li>
	<li><a href="あああ">あああ(コンテンツ数)</a></li>
	<li>
		<dl>
			<dt><a href="あああ">あああ(コンテンツ数)</a></dt>
			<dd>
				<ul>
					<li><a href="いいい">-いいい(コンテンツ数)</a></li>
					<li>
						<dl>
							<dt><a href="いいい">-いいい(コンテンツ数)</a></dt>
							<dd>
								<ul>
									<li><a href="ううう">-ううう(コンテンツ数)</a></li>
									<li><a href="ううう">-ううう(コンテンツ数)</a></li>
									<li><a href="ううう">-ううう(コンテンツ数)</a></li>
								</ul>
							</dd>
						</dl>
					</li>
					<li><a href="いいい">-いいい(コンテンツ数)</a></li>
				</ul>
			</dd>
		</dl>
	</li>
	<li><a href="あああ">-あああ(コンテンツ数)</a></li>
</ul>

といった状態が最終形態となります。

テーブルの構成は

現在は
id(INT)
sub_id(varchar)←ツーリー構成の詳細
num(INT)←コンテンツ数
name(varchar)←コンテンツの名前
となっております。

実際には
id|sub_id|num|name|sort
|:--|:--:|--:|--:|
|01|/1/|10|あああ|1|
|02|/2/|10|あああ|2|
|03|/2/3/|10|いいい|1|
|04|/2/4/|10|いいい|2|
|05|/2/5/|10|いいい|3|
|06|/6/|10|あああ|3|
|07|/7/|10|あああ|4|
|08|/7/8/|10|いいい|1|
|09|/7/9/|10|いいい|2|
|10|/7/9/10/|10|ううう|1|
|11|/7/9/11/|10|ううう|2|
|12|/7/9/12/|10|ううう|3|
|13|/7/13/|10|いいい|1|
|14|/14/|10|あああ|5|

このように上から順番がキレイになっていたらアレですけどもテーブルも乱雑になっている場合どうすればいいのでしょうか？
例えば
id|sub_id|num|name|sort
|:--|:--:|--:|--:|
|01|/1/|10|あああ|1|
|02|/2/|10|あああ|2|
|03|/3/|10|あああ|3|
|04|/4/|10|あああ|4|
|05|/5/|10|あああ|5|
|06|/2/6/|10|いいい|1|
|07|/2/7/|10|いいい|2|
|08|/2/8/|10|いいい|3|
|09|/4/9/|10|いいい|1|
|10|/4/10/|10|いいい|2|
|11|/4/11/|10|いいい|3|
|12|/4/10/12/|10|ううう|3|
|13|/4/10/13/|10|ううう|1|
|14|/4/10/14/|10|ううう|2|

一応取得に関しては問題なくできすべて後は並べるだけだとは思いますが。。。
どなたかご教授宜しくお願いいたします。
一応考えたのが
foreachを親、子、孫とかけて孫にいくにつれて中で中に入れることぐらいしか想像がつきません。。。

Answer 1

• なぜ最初の階層はul-liなのに、２段目はdl-dt-ddとなり

3段目でul-liにもどっているのでしょうか？

• コンテンツ数とは何を指しますか？
• 13番目の要素のidが14で、14番目の要素とかぶっていますがよいのですか？

基本的にご提示されている「経路列挙モデル」より「隣接モデル」で
データをもち「入れ子集合モデル」に変換して利用するほうがラクです

隣接モデルのsample

隣接モデルでは、親のidだけ保存します。
親がいない＝NULLが先頭になります
（入れ子集合モデル用にlevelと、l(左)、r(右)を用意してあります）

CREATE TABLE tbl(id INT NOT NULL UNIQUE,
parent_id INT NULL,
name varchar(64),
sort int,
level int NOT NULL,
l int not null,
r int not null); /*修正*/

INSERT INTO tbl(id,parent_id, name,sort) VALUES
(1,null,'0',1),
(2,null,'1',2),
(3,2,'1-1',3),
(4,2,'1-2',4),
(5,2,'1-3',5),
(6,null,'2',6),
(7,null,'3',7),
(8,7,'3-1',8),
(9,7,'3-2',9),
(10,9,'3-2-1',10),
(11,9,'3-2-2',11),
(12,9,'3-2-3',12),
(13,7,'3-3',13),
(14,null,'4',14);

id	parent_id	name	sort	level	l	r
1	NULL	0	1	0	0	0
2	NULL	1	2	0	0	0
3	2	1-1	3	0	0	0
4	2	1-2	4	0	0	0
5	2	1-3	5	0	0	0
6	NULL	2	6	0	0	0
7	NULL	3	7	0	0	0
8	7	3-1	8	0	0	0
9	7	3-2	9	0	0	0
10	9	3-2-1	10	0	0	0
11	9	3-2-2	11	0	0	0
12	9	3-2-3	12	0	0	0
13	7	3-3	13	0	0	0
14	NULL	4	14	0	0	0

入れ子集合モデルのsample(プロシージャをPHPに置き換え版)

SQLのプロシージャがどうしても作れないのであれば、以下PHPで代替できます

  $pdo->beginTransaction();
  $sql ="UPDATE tbl SET level=0,l=0,r=0;\n";
  $stmt= $pdo->query($sql);
  $sql ="UPDATE tbl SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1 WHERE parent_id IS NULL ORDER BY id;";
  $stmt= $pdo->query($sql);
  $sql ="SELECT id FROM tbl WHERE level=0 ORDER BY parent_id ASC,id DESC;";
  $stmt2 = $pdo->query($sql);
  while($row=$stmt2->fetch()){
    $sql ='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a2.r=a2.r+2,a3.r=a3.r+2 WHERE a1.parent_id=a2.id AND a2.l<a3.r AND a1.id=?;';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$row["id"]]);
    $sql ='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a3.l=a3.l+2 WHERE a1.parent_id=a2.id AND a2.l<a3.l and a3.id!=a1.id AND a1.id=?;';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$row["id"]]);
  }
  
  $sql ='SELECT name,level,(SELECT COUNT(*)-1 FROM tbl AS t2 WHERE t2.l BETWEEN t1.l AND t1.r) AS child FROM tbl AS t1 ORDER BY l;';
  $stmt = $pdo->query($sql);
  $rows=$stmt->fetchAll();
  $pdo->commit();// 実はロールバックしてもOK $pdo->rollback();

入れ子集合モデルのsample

隣接モデルを入れ子集合モデルに変換するために
以下のプロシージャを作っておきます。

DROP PROCEDURE IF EXISTS SET_LR;
DELIMITER //
CREATE PROCEDURE SET_LR()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR 
SELECT id FROM tbl WHERE level=0 ORDER BY parent_id ASC,sort DESC,id DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
UPDATE tbl SET level=0,l=0,r=0;
/* level,l,rを初期化*/

UPDATE tbl SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
WHERE parent_id IS NULL
ORDER BY sort ASC,id ASC;
/* 先頭データのlevelを1とし、l,rを１ずつ足していく*/

OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @id=a;
SET @sql='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a2.r=a2.r+2,a3.r=a3.r+2 WHERE a1.parent_id=a2.id AND a2.l<a3.r AND a1.id=?';
/* 子のlに親の1+1、子のrに親のl+2,子のlevelに親のlevel+1親のrを2増やす、親のlより大きいrを2増やす */

PREPARE stmt from @sql;
EXECUTE stmt USING @id;
SET @sql='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a3.l=a3.l+2 WHERE a1.parent_id=a2.id AND a2.l<a3.l and a3.id!=a1.id AND a1.id=?';
/* 親の1より大きいlの内、子以外のものを2増やす */

PREPARE stmt from @sql;
EXECUTE stmt USING @id;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

プロシージャの呼び出し
元データを追加・削除・更新する度にプロシージャを呼び出してコンバートしてください

CALL SET_LR;

※プロシージャ実行後

id	parent_id	name	sort	level	l	r
1	NULL	0	1	1	1	2
2	NULL	1	2	1	3	10
3	2	1-1	3	2	4	5
4	2	1-2	4	2	6	7
5	2	1-3	5	2	8	9
6	NULL	2	6	1	11	12
7	NULL	3	7	1	13	26
8	7	3-1	8	2	14	15
9	7	3-2	9	2	16	23
10	9	3-2-1	10	3	17	18
11	9	3-2-2	11	3	19	20
12	9	3-2-3	12	3	21	22
13	7	3-3	13	2	24	25
14	NULL	4	14	1	27	28

nameを階層で表示し、含まれる子要素数を表示
COUNT1は直接の子要素数のみ、COUNT2は孫以降の要素数も含む

SELECT * FROM tbl ORDER BY l

phpでデータをあつめて
$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
としたとき

$indent=3;
$l1=0;
foreach($rows as $key=>$row){
  $l2=$row["level"];
  $l3=($key<count($rows)-1)?$rows[$key+1]["level"]:0;
  if($l1>=$l2){
    print str_repeat(" ",($l2-1)*$indent);
  }
  if($l1<$l2){
    print PHP_EOL;
    print str_repeat(" ",($l2-1)*$indent);
    print "<ul>".PHP_EOL;
    print str_repeat(" ",($l2-1)*$indent);
  }
  print "<li>";
  print $row["name"];
  if($l2==$l3){
    print "</li>".PHP_EOL;;
  }
  if($l2>$l3){
    print "</li>".PHP_EOL;
    for($i=$l2-$l3-1;$i>0;$i--){
      print str_repeat(" ",($i)*$indent);
      print "</ul>".PHP_EOL;
      print str_repeat(" ",($i-1)*$indent);
      print "</li>".PHP_EOL;
    }
    print str_repeat(" ",($l3)*$indent);
    print "</ul>".PHP_EOL;
  }
  $l1=$l2;
}

ついでなので子要素の数を調べるにはこうです

SELECT name,level,(SELECT COUNT(*)-1 FROM tbl AS t2 WHERE t2.l BETWEEN t1.l AND t1.r) AS child FROM tbl AS t1 ORDER BY l

これを上記PHPの「print $row["name"];」の下の行に以下を追記するだけです

  if($row["child"]>0) print "(".$row["child"].")";

Comments

[space_sss]

ご解答ありがとうございます！
>>なぜ最初の階層はul-liなのに、２段目はdl-dt-ddとなり
3段目でul-liにもどっているのでしょうか？
子や孫をもつもののみdlでくくって表示させようと考えておりました！
>>コンテンツ数とは何を指しますか？
こちらは一応ブログコンテンツのシステムを作る為に作成しており各カテゴリに対していくつの投稿記事があるかを示しております！
>>13番目の要素のidが14で、14番目の要素とかぶっていますがよいのですか？
ご指摘ありがとうございます！こちらは記入ミスでした。修正させていただきました。

>>基本的にご提示されている「経路列挙モデル」より「隣接モデル」で
データをもち「入れ子集合モデル」に変換して利用するほうがラクです
そういった種類があるのですね。
そちらに関しては知識不足でした。。
そちらに関しては調べてみます。

[yambejp]

階層モデルのsample挙げておきました

[space_sss]

ご解答ありがとうございます。
上から順番に確認させていただいております。
プロシージャを作るやプロシージャの呼び出し等記載されていますがこれはいったい何なのでしょうか？
一応調べたのですがつかったことのなかったものなので．．．
文面的にはリセットや整理するものなのかな？とは思うのですが...
どのように実行されるようなものなのでしょうか？
質問ばかりで大変申し訳ありません。

[yambejp]

タグに「phpMyAdmin」があったのでMySQLの質問と認識していましたが
違いましたか？
プロシージャとはSQLで動かすプログラムです。（正確にはストアドプロシージャ）
私が提示したものをphpMyAdminで、当該するデータベース上のコンソールに
はりつけて実行すれば、プロシージャが登録されます。
以降そのプロシージャを呼び出せば、定形の処理がされるわけです。
今回は、テーブルのコンバートをプロシージャでやっています。

参考：
https://dev.mysql.com/doc/refman/5.6/ja/stored-routines.html
https://dev.mysql.com/doc/refman/5.6/ja/create-procedure.html

[space_sss]

ご丁寧に画回答ありがとうございます！
質問内容は間違いありません！
ストアドプロシージャといった言葉を聞いたことがわりませんでした。大変申し訳ありません。
コンソールとは後黒い画面のことののでしょうか？
今までphpadminを使用する際黒い画面自体を使用したことがなかったのですがそちらに上記のものをいれ実行するとプロシージャが登録されるといった事なのでしょうか？

[yambejp]

phpMyAdminの場合は、黒くはないと思います
DBを選んだ状態で「SQL」タブを押すと
「クエリを実行する」という入力エリアが表示されるので
そこに貼り付けて「実行」してください
最近のphpMyAdminであれば、どんなプロシージャが登録されているか
可視化できる仕組みになっているはずです

[退会済みユーザー]

横レス失礼します。

> 「ストアドプロシージャといった言葉を聞いたことがわりませんでした。」って書かれていますが・・・
こちらの質問への回答で書かれていますね。あなたの質問ですよ。
https://teratail.com/questions/63426

[space_sss]

>>phpMyAdminの場合は、黒くはないと思います
DBを選んだ状態で「SQL」タブを押すと
「クエリを実行する」という入力エリアが表示されるので
そこに貼り付けて「実行」してください
最近のphpMyAdminであれば、どんなプロシージャが登録されているか
可視化できる仕組みになっているはずです

反応が遅れてしまい大変申し訳ありません。。
SQL文で入力したのですが下記のエラーがでてしまして、
SQL query:

DELIMITER ;

MySQL のメッセージ: ドキュメント
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
もしかしたらSQL部分に直接入力することではないのか？と思いまして。。

[space_sss]

>>Kosuke_Shibuya様
以前質問させていただいた回答に記載されていましたね。
後で調べようと思っていましたが放置気味になってしまっていました。
せっかく回答いただいたのに理解できず大変申し訳ない気持ちです。
そちらもこの機会にしっかり勉強したいと思います・・・

[yambejp]

あれ、おかしいですね
「DROP PROCEDURE IF EXISTS SET_LR;」から
「DELIMITER ;」まで今回ですと38行コピーして
ペーストするだけでいけるはずなんですけど・・・
ちなみにMySQLのバージョンはいくつをご利用ですか？

[space_sss]

MySQLのバージョンは5.7.18です！
もしかして隣接モデルのsampleが実行できなかったので予想で最後の行の
int not null);
を
r int not null);
にしてしまったからでしょうか？？

[yambejp]

すみませんtypoです。
「r int not null);」が正しいので修正しました。

一応ただしく動いた場合のリストの状況を載せました。
MySQLの5.7ならストアド・プロシージャは利用できるので
問題ないはずなのですけどね・・・

ついでなので、子要素数のとり方も追記して最終形まで記載しておきました
なんとかプロシージャを動かせると良いのですが・・・

[yambejp]

改めて・・・
プロシージャを使用せず、すべてphp側で処理をする方法を途中に追記しました

[space_sss]

返信が遅れてもうしわけありません．．．
もう一度実行して試してみます！

[space_sss]

連絡が遅れましたが
もう一度phpadminのSQLで上記のものをたたきましたがやはり
SQL query:

DELIMITER ;

MySQL のメッセージ: ドキュメント
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
とエラーが出てしまいます。
ただページを更新してみるとプロシージャといった項目が追加されていました！

BEGIN
DECLARE a INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT id FROM tbl WHERE level=0 ORDER BY parent_id ASC,sort DESC,id DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
UPDATE tbl SET level=0,l=0,r=0;
/* level,l,rを初期化*/

UPDATE tbl SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
WHERE parent_id IS NULL
ORDER BY sort ASC,id ASC;
/* 先頭データのlevelを1とし、l,rを１ずつ足していく*/

OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @id=a;
SET @sql='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a2.r=a2.r+2,a3.r=a3.r+2 WHERE a1.parent_id=a2.id AND a2.l<a3.r AND a1.id=?';
/* 子のlに親の1+1、子のrに親のl+2,子のlevelに親のlevel+1親のrを2増やす、親のlより大きいrを2増やす */

PREPARE stmt from @sql;
EXECUTE stmt USING @id;
SET @sql='UPDATE tbl as a1,tbl as a2,tbl as a3 SET a3.l=a3.l+2 WHERE a1.parent_id=a2.id AND a2.l<a3.l and a3.id!=a1.id AND a1.id=?';
/* 親の1より大きいlの内、子以外のものを2増やす */

PREPARE stmt from @sql;
EXECUTE stmt USING @id;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END

といった定義になったので問題なく成功したように思えます！

[yambejp]

前回提示したとおりPHPで代替することもできます
プロシージャもしくはPHPでコンバートした上で
最終型のUL-LIの構造で問題ないか確認ください
構造が問題なければCSSや整形し、場合によっては
javascriptで動きをつけることになると思います

[space_sss]

ありがとうございます！
問題なく表示等も完了いたしました！
長々とした質問となってしまいましたがお付き合いいただきましてありがとうございます！
今回この事により 入れ子集合モデルといったものを学ぶことができました！
本当に感謝いたします！

Answer 2

せっかくなのでヒントだけにとどめておきます。

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `parentId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '親ID',
  `orderNumber` int(11) unsigned NOT NULL COMMENT '表示順',
  `label` varchar(32) NOT NULL DEFAULT '' COMMENT 'ラベル',
  PRIMARY KEY (`id`),
  UNIQUE KEY `label` (`label`),
  KEY `parentId` (`parentId`),
);

Comments

[space_sss]

いつもご解答ありがとうございます。

実力不足で、こちらがどうヒントになっているかが全く想像つきませんでした。
他にも何かヒントをいただけないでしょうか？