Q&A
このDDLだとエラー出ますね。
(7 'タイトルA-2', 4, 0);
↑カンマがない
前提条件・実現したいこと
下記表の『title』というテーブルの中にあるデータを使います。
| id | title_name | mae | ato |
|---|---|---|---|
| 1 | タイトルA | 0 | 2 |
| 2 | タイトルB | 1 | 3 |
| 3 | タイトルC | 2 | 0 |
| 4 | タイトルA-1 | 0 | 7 |
| 5 | タイトルA-1-1 | 0 | 6 |
| 6 | タイトルA-1-2 | 5 | 1 |
| 7 | タイトルA-2 | 4 | 0 |
『mae』キーは現在のidにて、前のidになるものを参照しています。
『ato』キーは現在のidにて、後のidになるものを参照しています。
例えば、id=2の時のmaeキー=1は、id=1を。atoキー=3はid=3を参照します。
このmaeキーとatoキーを参照して、下記のようにタイトルを並び替えたいです。
成功例：
タイトルA
タイトルA-1
タイトルA-1-1
タイトルA-1-2
タイトルA-2
タイトルB
タイトルC
問題点
maeキーとatoキーを使って条件分岐しても、titleの表の順番通りしか表示できません。
失敗：
タイトルA
タイトルB
タイトルC
タイトルA-1
タイトルA-1-1
タイトルA-1-2
タイトルA-2
ソース
list_logic.php
php
1<!DOCTYPE html> 2<html lang="ja"> 3 4<head> 5 <meta charset="UTF-8"> 6 <meta http-equiv="X-UA-Compatible" content="IE=edge"> 7 <meta name="viewport" content="width=device-width, initial-scale=1.0"> 8 <title>仮ページ</title> 9 10 <?php 11 12 require("./mysqlcon.php"); 13 14 $dbh = new PDO( 15 $dsn, 16 $db_user, 17 $db_password, 18 array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET `utf8`") 19 ); 20 21 // タイトルのDBを習得 22 $sql = 'SELECT * FROM title'; 23 24 $stmt = $dbh->prepare($sql); 25 $stmt->execute(); 26 $depth = $stmt->fetchAll(PDO::FETCH_ASSOC); 27 ?> 28</head> 29 30<body> 31 <?php 32 foreach($depth as $val){ 33 if($val['mae'] == 0){ 34 if($val['mae'] == 0 && $val['ato'] != 0){ 35 echo $val['title_name'] . '<br>'; 36 }else{ 37 echo $val['title_name'] . '<br>'; 38 } 39 }else{ 40 echo $val['title_name'] . '<br>'; 41 } 42 43 } 44 ?> 45</body> 46 47</html>
mysqlcon.php
php
1<?php 2 //mysqlへの接続 3$dsn = 'mysql:dbname=test;host=localhost'; 4$db_user = 'root'; 5$db_password = ''; 6?>
データベース
mysql
1-- 2-- テーブルの構造 `title` 3-- 4 5CREATE TABLE `title` ( 6 `id` int(11) NOT NULL, 7 `title_name` varchar(30) NOT NULL, 8 `mae` int(11) NOT NULL, 9 `ato` int(11) NOT NULL 10) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 11 12-- 13-- テーブルのデータのダンプ `title` 14-- 15 16INSERT INTO `title` (`id`, `title_name`, `mae`, `ato`) VALUES 17(1, 'タイトルA', 0, 2), 18(2, 'タイトルB', 1, 3), 19(3, 'タイトルC', 2, 0), 20(4, 'タイトルA-1', 0, 7), 21(5, 'タイトルA-1-1', 0, 6), 22(6, 'タイトルA-1-2', 5, 0), 23(7 'タイトルA-2', 4, 0); 24 25ALTER TABLE `title` 26 ADD PRIMARY KEY (`id`); 27 28-- 29-- AUTO_INCREMENT for dumped tables 30-- 31 32-- 33-- AUTO_INCREMENT for table `title` 34-- 35ALTER TABLE `title` 36 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; 37COMMIT; 38
試してみたこと
php
1 <?php 2 foreach($depth as $val){ 3 if($val['mae'] == 0){ 4 if($val['mae'] == 0 && $val['ato'] != 0){ 5 echo $val['title'] . '<br>'; 6 }else{ 7 echo $val['title'] . '<br>'; 8 } 9 }else{ 10 echo $val['title'] . '<br>'; 11 } 12 13 } 14 ?>
の部分で、条件分岐を増やしても同じでした。
最後に
『mae』キーが0の時に、階層の始めを設定。『atoキー』が0の時に、階層の終わりを設定するのではないかと考えております。
ですが、それ以降、どうすれば成功例のように並び替えることが出来るかが分かりません。
皆様方のお力をお借りできればと思います。
>『mae』キーは現在のidにて、前のidになるものを参照しています。
『ato』キーは現在のidにて、後のidになるものを参照しています。
ん－素直にこれを理解すると、「成功例」が出るようなデータ構成になってないように見えるのですが・・・。
もう少し「親」「子」を明確にされるような構成にした方が良いと思います。
その辺整理すればSQLだけでソート出来てPHPは出力するだけで済みます。
m.ts10806様
コメント頂きありがとうございます。
MySQLのデータ構造については、まだ「親」「子」のような関係が出来ておりませんでした。
色々調べながら、やっていこうと思います。
MySQLのバージョンが8.0以降なら、ナイーブツリーを再帰により経路取得が出来ますので、その経路順にorder byすればイケます。
sazi様
コメント頂きありがとうございます。
MySQLも日々進歩しているのですね。
今自分が使っているMySQLのバージョンを見てみようと思います。
回答2件
0
ベストアンサー
いまのデータ管理方法では何をやっても無駄でしょう
ちゃんとやるなら入れ子集合モデルという方式をつかいます。
sample
SQL
1create table tbl (id int primary key,name varchar(30),parent_id int,level int,l int,r int); 2insert into tbl(id,name,parent_id) values 3(1,'タイトルA',null), 4(2,'タイトルB',null), 5(3,'タイトルC',null), 6(4,'タイトルA-1',1), 7(5,'タイトルA-1-1',4), 8(6,'タイトルA-1-2',4), 9(7,'タイトルA-2',1);
procedure
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 12UPDATE tbl SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1 13WHERE parent_id IS NULL 14ORDER BY id; 15 16OPEN CUR; 17REPEAT 18FETCH CUR INTO a; 19IF NOT done THEN 20SET @id=a; 21SET @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=?'; 22 23PREPARE stmt from @sql; 24EXECUTE stmt USING @id; 25SET @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=?'; 26 27PREPARE stmt from @sql; 28EXECUTE stmt USING @id; 29END IF; 30UNTIL done END REPEAT; 31CLOSE CUR; 32END 33// 34DELIMITER ;
実行
SQL
1call set_lr;
表示
SQL
1select * from tbl order by l;
投稿2021/05/26 00:51
編集2021/05/26 05:45
総合スコア116835
yambejp様。
コメント頂きありがとうございます。
入れ子集合モデルという方式があるんですね。
こちらのサイトに入れ子集合モデルについて解説しているところがあります。
なので、このサイトで入れ子集合モデルについて学習しようと思います。
https://gihyo.jp/dev/serial/01/sql_academy2/000501
入れ子集合モデルのサンプル上げておきました
データを追加・修正するたびにプロシージャを実行する必要があるので
ご注意ください
yambejp様
なにからなにまで、お手数かけていただきありがとうございます。
早速サンプルを用いて学習いたします。
0
maeキーとatoキーを使って条件分岐しても、titleの表の順番通りしか表示できません。
データベースはINSERTされる内容の順序を保証しません。Excelとは違います。
表示する時って、SELECT文を使っているのでは？
ORDER BYを指定しない限り表示順は保証されません。
SQL
1SELECT ．．．ORDER BY で表示したい列1, 列2 ...
などと指定してください。
投稿2021/05/25 11:15
総合スコア16417
あなたの回答
tips
プレビュー
