title: PHP MySQL hierarchy data optimization
body: <p>I have a table employees, which contains columns : <strong>employee_id</strong>, <strong>name</strong>, <strong>employee_manager_id</strong>.
  <strong>employee_manager_id</strong> references to <strong>employee_id</strong>. It's a hierarchal data.</p>
  
  <p>I have this output using PHP but couldn't achieve it using only one mySQL query. As of now, I need to process the data in PHP using recursive function so i can achieve this kind output.</p>
  
  <pre><code>Sample Array Output 
  0 =&gt; (
                  employee_id =&gt; 2,
                  name =&gt; Jerald,
                  employee_manager_id =&gt; 1,
                  depth =&gt; 1
              ),
          1 =&gt; (
                  employee_id =&gt; 3,
                  name =&gt; Mark,
                  employee_manager_id =&gt; 2,
                  depth =&gt; 2
              ), 
          2 =&gt; (
                  employee_id =&gt; 6,
                  name =&gt; Cyrus,
                  employee_manager_id =&gt; 3,
                  depth =&gt; 3
              ), 
          3 =&gt; (
                  employee_id =&gt; 4,
                  name =&gt; Gerby,
                  employee_manager_id =&gt; 2,
                  depth =&gt; 2
              )
  </code></pre>
  
  <p>As of now, this is my recursive function in PHP to achieve the output above.</p>
  
  <pre><code>function get_employees_by_hierarchy( $_employee_id = 0, $_depth = 0, $_org_array = array() ) {
      if ( $this-&gt;org_depth &lt; $_depth ) {
          $this-&gt;org_depth = $_depth;
      }
  
      $_depth++;
      $_query = "SELECT * FROM employees WHERE ";
  
      if ( !$_employee_id ) {
          $_query .= "employee_manager_id IS NULL OR employee_manager_id = 0";
      }
      else { 
          $_query .= "employee_manager_id = " . $this-&gt;dbh-&gt;quoteSmart( $_employee_id );
      }
      $_result = $this-&gt;query( $_query );
  
      while ( $_row = $_result-&gt;fetchRow() ) {
          $_row['depth'] = $_depth;
          array_push( $_org_array, $_row );
          $_org_array = $this-&gt;get_employees_by_hierarchy(
              $_row['employee_id'],
              $_depth,
              $_org_array
          );
      }
      return $_org_array;
  }
  </code></pre>
  
  <p>My question is, is there anyway so I can achieve the array output i want using just one mysql query?
  If not possible in mysql query, is there anymore to optimize in my current code?</p>
  
  <p>Any help would greatly be appreciated.</p>
  
  <p>Thanks</p>