###実現したいこと
mysql に登録した商品マスターのカテゴリー名をPHPでリストメニューを作りたい。(Part.3)

正直なところ、前回の質問で回答していた内容をまだ理解できていません
とても難しいですsqlは
ここまでくるとPHP内で処理した方がいいのか、SQLだけで処理できるのか判断がつかなくなりました。
助言お願いします。

初心者です。

###用意したmysql

hs_id	ct_01	ct_02	ct_03	ct_04	ct_05	ct_06	name	URL
1001	A	AA	AAA	A-ct4		name1001	http://www
1002	A	AA	BBB	A-ct4		name1002	http://www
1003	A	AA	BBB	B-ct4		name1003	http://www
1004	A	AA	CCC	A-ct4		name1004	http://www
1005	A	BB	BBB	A-ct4		name1005	http://www
1006	A	BB	BBB	A-ct4		name1006	http://www
1007	A	BB	CCC	B-ct4		name1007	http://www
1008	B	AA	AAA	A-ct4	A-ct5	A-ct6	name1008	http://www
1009	B	BB	AAA	B-ct4	B-ct5	name1009	http://www
1010	B	BB	BBB	A-ct4	C-ct5	name1010	http://www

を読み込みsqlにて下記のhtmlを書き出したいです。
前回部分的に質問したのですが、理解できない部分があり悩んでいます。

html
1<ul>
2  <li><a href="#" class="item-arrow">A</a>
3    <ul class="level2">
4    <li><a href="#" class="item-arrow">AA</a>
5      <ul class="level3">
6      <li><a href="#" class="item-arrow">AAA</a>
7        <ul class="level4">
8        <li><a href="#" class="item-arrow">A-ct4</a>
9          <ul class="level5">
10          <li><a href="URL">name1001</a></li>
11          </ul>
12        </li>
13        </ul>
14      </li>
15      <li><a href="#" class="item-arrow">BBB</a>
16        <ul class="level4">
17        <li><a href="#" class="item-arrow">A-ct4</a>
18          <ul class="level5">
19          <li><a href="URL">name1002</a></li>
20          </ul>
21        </li>
22        <li><a href="#" class="item-arrow">B-ct4</a>
23          <ul class="level5">
24          <li><a href="URL">name1003</a></li>
25          </ul>
26        </li>
27        </ul>
28      </li>
29      <li><a href="#" class="item-arrow">CCC</a>
30        <ul class="level4">
31        <li><a href="#" class="item-arrow">A-ct4</a>
32          <ul class="level5">
33          <li><a href="URL">name1004</a></li>
34          </ul>
35        </li>
36        </ul>
37      </li>
38      </ul>
39    </li>
40    <li><a href="#" class="item-arrow">BB</a>
41    	<ul class="level3">
42      	<li><a href="#" class="item-arrow">BBB</a>
43        	<ul class="level4">
44          	<li><a href="#" class="item-arrow">A-ct4</a>
45            	<ul class="level5">
46              	<li><a href="URL">name1005</a></li>
47                <li><a href="URL">name1006</a></li>
48              </ul>
49            </li>
50          </ul>
51        </li>
52        <li><a href="#" class="item-arrow">CCC</a>
53        	<ul class="level4">
54          	<li><a href="#" class="item-arrow">B-ct4</a>
55            	<ul class="level5">
56              	<li><a href="URL">name1007</a></li>
57              </ul>
58            </li>
59          </ul>
60        </li>
61      </ul>
62    </li>
63    </ul>
64  </li><!-- A -->
65  <li>B
66    <ul class="level2">
67    <li><a href="#" class="item-arrow">AA</a>
68    	<ul class="level3">
69      	<li><a href="#" class="item-arrow">AAA</a>
70        	<ul class="level4">
71          	<li><a href="#" class="item-arrow">A-ct4</a>
72            	<ul class="level5">
73              	<li><a href="#" class="item-arrow">A-ct5</a>
74                	<ul class="level6">
75                  	<li><a href="#" class="item-arrow">A-ct6</a>
76                    	<ul class="level7">
77                      	<li><a href="URL">name1008</a></li>
78                      </ul>
79                    </li>
80                  </ul>
81                </li>
82              </ul>
83            </li>
84          </ul>
85        </li>
86      </ul>
87    </li>
88    </ul>
89  </li><!-- B -->
90</ul>

###試したsql文

php
1<?PHP
2require_once("./js/nic_MYDB.php");
3$pdo = db_connect();
4
5// 01-02 ct-0102
6$sql= "SELECT ct_02,COUNT(*) AS count FROM products_table where hs_id >= 1001 AND hs_id <= 1999 GROUP BY ct_02 ORDER BY hs_id ASC ";
7$stmh0102 = $pdo->query($sql);
8
9$sql= "
10SELECT REPLACE(
11    CONCAT(  '<li><a href=\"#\" class=\"item-arrow\">', IFNULL( T1.ct_03,  '' ) 
12      ,  '</a><ul ><li><a href=\"#\" class=\"item-arrow\">'
13      , REPLACE( GROUP_CONCAT( DISTINCT T1.ct_04,  '</a></li><li>' ) ,  ',',  '' ) 
14      , '</li></ul></li>' ) 
15      , '</li><li></li></ul></li>'
16      ,  '</li></ul></li>' ) AS html_data
17from    products_table T1
18where   T1.hs_id >= 1001
19AND    T1.hs_id <= 1999
20GROUP BY T1.ct_03
21ORDER BY T1.ct_03
22;";
23$stmh0104a = $pdo->query($sql);
24?>
25
26<nav id="dd">
27<ul>
28<li class="font18"><a href="http://www.nikkohm.com/test/#" class="active">Part Search</a>
29	<!-- 01 -->
30  <ul class="level1" style="display: block;">
31	<!-- 01-01 -->
32  <li><a href="#" class="item-arrow">Power</a>
33  
34<?PHP
35echo '<!-- 01-02 -->';
36echo "<ul class=\"level2\" style=\"display: none;\">";
37while ($row = $stmh0102->fetch(PDO::FETCH_ASSOC)) {
38	echo "<li><a href=\"#\" class=\"item-arrow\">";
39	echo htmlspecialchars($row["ct_02"]);
40	echo "</a>";
41	
42	echo "<!-- 01-03 -->";
43	echo "<ul class=\"level2\" style=\"display: none;\">";
44	while ($row = $stmh0104a->fetch(PDO::FETCH_ASSOC)) {
45		echo ($row["html_data"]);
46	}
47	echo "</ul><!-- 01-03 -->";
48	echo "</li>";
49}
50echo "</ul><!-- 01-02 -->";
51?>
52
53  </li><!-- 01-01 -->
54  <!-- 01-02 -->
55  <li><a href="#" class="item-arrow">Shunt</a>
56  </li><!-- 01-02 -->
57  </ul><!-- 01 -->
58</li><!-- Part Search -->
59</ul>

###試した結果のhtml

html
1<nav id="dd">
2  <ul>
3    <li class="font18"><a href="http://www.nikkohm.com/test/#" class="active">Part Search</a> 
4      <!-- 01 -->
5      <ul class="level1" style="display: block;">
6        <!-- 01-01 -->
7        <li><a href="#" class="item-arrow">Power</a> 
8          
9          <!-- 01-02 -->
10          <ul class="level2" style="display: none;">
11            <li><a href="#" class="item-arrow">A</a><!-- 01-03 -->
12              <ul class="level2" style="display: none;">
13                <li><a href="#" class="item-arrow">AA</a>
14                  <ul class="level2" style="display: none;">
15                    <li><a href="#" class="item-arrow">AAA</a></li>
16                    <li>BBB</a></li>
17                    <li>CCC</a></li>
18                  </ul>
19                </li>
20                <li><a href="#" class="item-arrow">BB</a>
21                  <ul class="level2" style="display: none;">
22                    <li><a href="#" class="item-arrow">BBB</a></li>
23                  </ul>
24                </li>
25              </ul>
26              <!-- 01-03 --></li>
27          </ul>
28          <!-- 01-02 --> 
29        </li>
30        <!-- 01-01 --> 
31        <!-- 01-02 -->
32        <li><a href="#" class="item-arrow">Shunt</a> </li>
33        <!-- 01-02 -->
34      </ul>
35      <!-- 01 --> 
36    </li>
37    <!-- Part Search -->
38  </ul>
39</nav>

tomari_perform様に教えていただいたsqlに追加したのですが
どこが悪いのか BBB、CCCにリンクが付きません。
よろしくお願い致します。

テスト-A

テスト-B

テストーC

PHP
1    $db_user = "***";	// ユーザー名
2    $db_pass = "***";	// パスワード
3    $db_host = "***";	// ホスト名
4    $db_name = "***";	// データベース名
5    $db_type = "mysql";	// データベースの種類
6
7    $dsn = "$db_type:host=$db_host;dbname=$db_name;charset=utf8";
8
9    try {
10      $pdo = new PDO($dsn, $db_user,$db_pass);
11      $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
12      $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
13      $sql ='SELECT name,level,url,link,(SELECT COUNT(*) FROM tbl AS t2 WHERE t2.l BETWEEN t1.l AND t1.r AND t2.level=t1.level+1) AS child FROM tbl AS t1 ORDER BY l';
14      $pdo->query($sql);
15      $rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
16      print get_tree($rows);
17    } catch(PDOException $Exception) {
18      die('エラー :' . $Exception->getMessage());
19    }
20
21function get_tree($rows){
22  $indent=3;
23  $l1=0;
24  ob_start();
25  foreach($rows as $key=>$row){
26    $l2=$row["level"];
27    $l3=($key<count($rows)-1)?$rows[$key+1]["level"]:0;
28    if($l1>=$l2){
29      print str_repeat(" ",($l2-1)*$indent);
30    }
31    if($l1<$l2){
32      print PHP_EOL;
33      print str_repeat(" ",($l2-1)*$indent);
34      $class=$row["level"]>1?" class=\"level{$row["level"]}\"":"";
35      print "<ul{$class}>".PHP_EOL;
36      print str_repeat(" ",($l2-1)*$indent);
37    }
38    print "<li>";
39    if($row["link"]==1 and $row["url"]!==""){
40      /* item-arrowクラスの付け方がおかしかったので修正 */
41      $class=$row["child"]>0?" class=\"item-arrow\"":"";
42      print "<a href=\"{$row["url"]}\"{$class}>{$row["name"]}</a>";
43    }else{
44      print $row["name"];
45    }
46    if($row["child"]>0) print "(".$row["child"].")";
47    if($l2==$l3){
48      print "</li>".PHP_EOL;;
49    }
50    if($l2>$l3){
51      print "</li>".PHP_EOL;
52      for($i=$l2-$l3-1;$i>0;$i--){
53        print str_repeat(" ",($i)*$indent);
54        print "</ul>".PHP_EOL;
55        print str_repeat(" ",($i-1)*$indent);
56        print "</li>".PHP_EOL;
57      }
58      print str_repeat(" ",($l3)*$indent);
59      print "</ul>".PHP_EOL;
60    }
61    $l1=$l2;
62  }
63  $str=ob_get_contents();
64  ob_end_clean();
65  return $str;
66}

テスト-C のSQLのみ実行

テスト-D

赤い部分に</li>

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

回答2件

ベストアンサー

この手の階層構造は、自分の直接の親情報だけを持つ「隣接リストモデル」が
一般的で、それを管理しやすいように「入れ子集合モデル」に変換して利用します。
ご提示の階層は「経路列挙モデル」に近いですが、カテゴリをカラムで管理
しているため、拡張性がわるく持ち方としていまいちです。

今回のテーブル構成はマストでしょうか？

隣接リストモデルから入れ子集合モデルへ

隣接リストモデル

SQL
1create table tbl(
2id int not null unique,
3parent_id int null,
4name varchar(30),
5url varchar(100),
6link tinyint,
7level int not null default 0,
8l int null,
9r int null
10);

入れ子集合モデルへのコンバータ

SQL
1DROP PROCEDURE IF EXISTS SET_LR;
2DELIMITER //
3CREATE PROCEDURE SET_LR()
4BEGIN
5DECLARE a INT DEFAULT 0;
6DECLARE done INT DEFAULT 0;
7DECLARE CUR CURSOR FOR 
8SELECT id FROM tbl WHERE level=0 ORDER BY parent_id ASC,id DESC;
9DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
10UPDATE tbl SET level=0,l=0,r=0;
11/* level,l,rを初期化*/
12
13UPDATE tbl SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
14WHERE parent_id IS NULL
15ORDER BY id;
16/* 先頭データのlevelを1とし、l,rを１ずつ足していく*/
17
18OPEN CUR;
19REPEAT
20FETCH CUR INTO a;
21IF NOT done THEN
22SET @id=a;
23SET @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=?';
24/* 子のlに親の1+1、子のrに親のl+2,子のlevelに親のlevel+1親のrを2増やす、親のlより大きいrを2増やす */
25
26PREPARE stmt from @sql;
27EXECUTE stmt USING @id;
28SET @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=?';
29/* 親の1より大きいlの内、子以外のものを2増やす */
30
31PREPARE stmt from @sql;
32EXECUTE stmt USING @id;
33END IF;
34UNTIL done END REPEAT;
35CLOSE CUR;
36END
37//
38DELIMITER ;

データ投入

SQL
1insert into tbl(id,parent_id,name,url,link) values
2( 1,null,'A','#',1),
3( 2,null,'B','#',1),
4( 3,1,'AA','#',1),
5( 4,1,'BB','#',1),
6( 5,2,'AA','#',1),
7( 6,2,'BB','#',1),
8( 7,3,'AAA','#',1),
9( 8,3,'BBB','#',1),
10( 9,3,'CCC','#',1),
11(10,4,'BBB','#',1),
12(11,4,'CCC','#',1),
13(12,5,'AAA','#',1),
14(13,6,'AAA','#',1),
15(14,6,'BBB','#',1),
16(15,7,'A-ct4','#',1),
17(16,8,'A-ct4','#',1),
18(17,8,'B-ct4','#',1),
19(18,9,'A-ct4','#',1),
20(19,10,'A-ct4','#',1),
21(20,11,'B-ct4','#',1),
22(21,12,'A-ct4','#',1),
23(22,13,'B-ct4','#',1),
24(23,14,'A-ct4','#',1),
25(24,21,'A-ct5','#',1),
26(25,22,'B-ct5','#',1),
27(26,23,'C-ct5','#',1),
28(27,24,'A-ct6','#',1),
29(28,15,'name1001','www',1),
30(29,16,'name1002','www',1),
31(30,17,'name1003','www',1),
32(31,18,'name1004','www',1),
33(32,19,'name1005','www',1),
34(33,19,'name1006','www',1),
35(34,20,'name1007','www',1),
36(35,27,'name1008','www',1),
37(36,25,'name1009','www',1),
38(37,26,'name1010','www',1);

コンバート

SQL
1CALL SET_LR;

階層状況の確認

SQL
1SELECT CONCAT( REPEAT( "__", LEVEL -1 ) , name ) AS name
2FROM area
3ORDER BY l

PHPへの書き出し

以前作った分の流用なので若干インデントのバグがありますが
出力するHTML文としてはさほど問題ないはずです
※ポイント

classの指定など最大限当初例示いただいたHTMLにあわせたつもりですが

おかしかったらうまく調整してください

linkが1でURLが空でなければアンカーが貼られます

あえてリンクを表示したくない場合レコードのlinkを0にするなどテストしてみてください

ただしhtmlspecialcharやurlencodeなどかけてないので注意
一応直下の要素数をカウントして表示しておきました

ちなみに、以下SQL文にすればカテゴリを含むすべての子要素数が表示されます

SELECT name,level,url,link,(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修正

PHP
1
2
3try{
4  /* PDOの接続は自身の環境に合わせて下さい */
5  $pdo = new PDO($dsn, $user,$password);
6  $sql ='SELECT name,level,url,link,(SELECT COUNT(*) FROM tbl AS t2 WHERE t2.l BETWEEN t1.l AND t1.r AND t2.level=t1.level+1) AS child FROM tbl AS t1 ORDER BY l';
7  /* 今回prepare処理は不要なのでqueryに変更しておきます
8  $stmt = $pdo->prepare($sql);
9  $stmt->execute([]);
10  */
11  $stmt = $pdo->query($sql);
12  $rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
13  /*当初gett_reeになっていたので修正します*/
14  print get_tree($rows);
15}catch(PDOException $e){
16  die($e->getMessage());
17}
18
19function get_tree($rows){
20  $indent=3;
21  $l1=0;
22  ob_start();
23  foreach($rows as $key=>$row){
24    $l2=$row["level"];
25    $l3=($key<count($rows)-1)?$rows[$key+1]["level"]:0;
26    if($l1>=$l2){
27      print str_repeat(" ",($l2-1)*$indent);
28    }
29    if($l1<$l2){
30      print PHP_EOL;
31      print str_repeat(" ",($l2-1)*$indent);
32      $class=$row["level"]>1?" class=\"level{$row["level"]}\"":"";
33      print "<ul{$class}>".PHP_EOL;
34      print str_repeat(" ",($l2-1)*$indent);
35    }
36    print "<li>";
37    if($row["link"]==1 and $row["url"]!==""){
38      $class=$row["child"]>0?" class=\"item-arrow\"":"";
39      print "<a href=\"{$row["url"]}\"{$class}>{$row["name"]}</a>";
40    }else{
41      print $row["name"];
42    }
43    if($row["child"]>0) print "(".$row["child"].")";
44    if($l2==$l3){
45      print "</li>".PHP_EOL;;
46    }
47    if($l2>$l3){
48      print "</li>".PHP_EOL;
49      for($i=0;$i<$l2-$l3-1;$i++){
50        print str_repeat(" ",($l2-$i-1)*$indent);
51        print "</ul>".PHP_EOL;
52        print str_repeat(" ",($l2-$i-2)*$indent);
53        print "</li>".PHP_EOL;
54      }
55      print str_repeat(" ",($l3)*$indent);
56      print "</ul>".PHP_EOL;
57      if($l3>0){
58      print str_repeat(" ",($l3-1)*$indent);
59      print "</li>".PHP_EOL;
60      }
61    }
62    $l1=$l2;
63  }
64  $str=ob_get_contents();
65  ob_end_clean();
66  return $str;
67}
68

投稿2017/08/01 07:26

編集2017/08/03 07:14

yambejp

総合スコア114769

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！