title: Creating a directory tree with SQL in MySQL
body: <p>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:</p>
  
  <p>Create sql of the table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>And the insert SQL of data:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>All records of the table which's created:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I would like to use a/some SQL get the rows in this order:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For making this directory tree:</p>
  
  <pre><code>- Dashboard
  - Slider
  - Column
    - Column list
    - Add column
  - Permission
    - Permission Group
      - Add permission
    - User List
      - Add user
    - Section permission
      - Add Section
  </code></pre>
  
  <hr>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and so on.</p>
  
  <p>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?</p>