2

I am using php to make directory tree list. Directory(data) are from a MySQL database table but I do not have the idea to write the sql to get the records with the order I want:

Create sql of the table:

CREATE TABLE section (
  section_id tinyint(4) NOT NULL auto_increment,
  name varchar(500) default NULL,
  parent_id tinyint(4) default NULL,
  lineage varchar(45) default NULL,
  level tinyint(4) default NULL,
PRIMARY KEY  (section_id)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

And the insert SQL of data:

INSERT INTO section (
section_id,name,lineage,parent_id,level)
VALUES 
('1', 'Dashboard', '1', '0', '1'), 
('2', 'Slider', '2', '0', '1'), 
('3', 'Column', '3', '0', '1'),
('4', 'Column list', '3-4', '3', '2'), 
('5', 'Add column', '3-5', '3', '2'), 
('6', 'Permission', '6', '0', '1'), 
('7', 'Permission Group', '6-7', '6', '2'), 
('8', 'User List', '6-8', '6', '2'), 
('9', 'Section permission', '6-9', '6', '2'), 
('10', 'Add permission', '6-7-10', '7', '3'), 
('11', 'Add user', '6-8-11', '8', '3'), 
('12', 'Add section', '6-9-12', '9', '3');

All records of the table which's created:

section_id     name                parent_id  level
-----------------------------------------------------
1              Dashboard           0          1
2              Slider              0          1
3              Column              0          1
4              Column list         3          2
5              Add column          3          2
6              Permission          0          1
7              Permission Group    6          2
8              User List           6          2
9              Section permission  6          2
10             Add permission      7          3 
11             Add user            8          3 
12             Add section         9          3

I would like to use a/some SQL get the rows in this order:

section_id     name                parent_id  level
-----------------------------------------------------
1              Dashboard           0          1
2              Slider              0          1
3              Column              0          1
4              Column list         3          2
5              Add column          3          2
6              Permission          0          1
7              Permission Group    6          2
10             Add permission      7          3 
8              User List           6          2
11             Add user            8          3 
9              Section permission  6          2
12             Add section         9          3

For making this directory tree:

- Dashboard
- Slider
- Column
  - Column list
  - Add column
- Permission
  - Permission Group
    - Add permission
  - User List
    - Add user
  - Section permission
    - Add Section

I have an idea using many SQL to get the directories and store the data into an php object. And this is the sql to get sub-directory:

SELECT * FROM tbl_section AS a1 WHERE a1.parent_id = 0;
SELECT * FROM tbl_section AS a1 WHERE a1.parent_id = 1;
SELECT * FROM tbl_section AS a1 WHERE a1.parent_id = 2;
SELECT * FROM tbl_section AS a1 WHERE a1.parent_id = 3;

and so on.

However, it needs run the select sql 100 times if there is 100 directories, i don't think it's a good method, any others idea?

user2446447
  • 31
  • 5
  • 1
    As you're discovering, relational databases struggle to model hierarchical or tree-like data structures. In fact, it's one of the most [complicated and technical questions in the field](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database). – Bacon Bits Jan 06 '15 at 04:50
  • Thanks you for your comment and I found a method works for me on your link provided. Although that may not is the most dynamic way but it works for me. Maybe I paste that as the answer of question for public. – user2446447 Jan 06 '15 at 09:57
  • @BaconBits: it's actually not that hard if you are not using MySQL which is one of the very few relational databases that still don't support recursive queries. –  Jan 06 '15 at 13:05
  • @a_horse_with_no_name Oh, recursive CTEs certainly help, but they're not the easiest to understand. IMX, most people seem to just copy the code and don't understand what the logic does, which is not a good idea overall. Recursive CTEs don't always perform all that well, either. I've seen people change them into [sprocs with temp tables](http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx) and get better performance. Ultimately, you're doing a lot more *programming* in SQL, which is usually a sign you're doing something less correct with the set-based language. – Bacon Bits Jan 06 '15 at 15:34

1 Answers1

1

This method (Lineage Column) may not is the best and most dynamic way to list out the directory tree which get data from MySQL table, but this is quite easy and fast:

Related question in stackoverflow:
What are the options for storing hierarchical data in a relational database?
Referenced article: Lineage Column (a.k.a. Materialized Path, Path Enumeration) http://www.ferdychristant.com/blog/articles/DOMM-7QJPM7


This is the Select SQL by using "Lineage Column" method:

SELECT c.section_id, c.name, c.lineage, c.level, c.parent_id, 
    (SELECT COUNT(*) FROM section 
     where section.lineage 
     LIKE (CONCAT(c.lineage,'%')) AND 
     section.lineage!=c.lineage) as replies
FROM section as c
order by c.lineage;

And it returns the record with the order i want:

section_id  name                lineage     level  parent_id   replies
----------------------------------------------------------------------
1           Dashboard           1           1      0           0
2           Slider              2           1      0           0
3           Column              3           1      0           2
4           Column list         3-4         2      3           0
5           Add column          3-5         2      3           0
6           Permission          6           1      0           6
7           Permission Group    6-7         2      6           1
10          Add permission      6-7-10      3      7           0
8           User List           6-8         2      6           1
11          Add user            6-8-11      3      8           0
9           Section permission  6-9         2      6           1
12          Add section         6-9-12      3      9           0

This is the php code to echo the directory mentioned at the question:

<?php
CONST SERVERNAME = "";  //your mysql server name
CONST USERNAME   = "";  //your mysql user name
CONST PASSWORD   = "";  //your mysql password
CONST DATABASE   = "";  //your mysql database name

// Create connection
$conn = new mysqli(SERVERNAME, USERNAME, PASSWORD, DATABASE);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

   $sql = "SELECT c.section_id, c.name, c.lineage, c.level, c.parent_id, 
        (SELECT COUNT(*) FROM section 
         where section.lineage 
         LIKE (CONCAT(c.lineage,'%')) AND 
         section.lineage!=c.lineage) as replies
    FROM section as c
    order by c.lineage";

    $html = "";
    foreach ($conn->query($sql) as $row) {
        switch ($row['level']){
            case "1": $html.="-".$row['name'] . "<br />"; break;
            case "2": $html.="&nbsp;&nbsp;-".$row['name'] . "<br />"; break;
            case "3": $html.="&nbsp;&nbsp;&nbsp;&nbsp;-".$row['name'] . "<br />"; break;
        } 
    }
    print $html;
?>

Result of the php code:

-Dashboard
-Slider
-Column
  -Column list
  -Add column
-Permission
  -Permission Group
    -Add permission
  -User List
    -Add user
  -Section permission
    -Add section
Community
  • 1
  • 1
user2446447
  • 31
  • 5