title: is it possible to write this recursive loop in a MySQL stored procedure
body: <p>hi i am using <code>codeigniter</code> and <code>MySQL</code> i have a table structure like this .</p>
  
  <p><img src="https://i.stack.imgur.com/3NAPV.gif" alt="enter image description here"></p>
  
  <p>i am getting all the child values when i pass the parent title . </p>
  
  <p>this the code </p>
  
  <pre><code>&lt;?php &lt;br&gt;
  // $parent is the parent of the children we want to see &lt;br&gt;
  // $level is increased when we go deeper into the tree, &lt;br&gt;
  //        used to display a nice indented tree &lt;br&gt;
  function display_children($parent, $level) { &lt;br&gt;
      // retrieve all children of $parent &lt;br&gt;
      $result = mysql_query('SELECT title FROM tree '. &lt;br&gt;
                             'WHERE parent="'.$parent.'";'); &lt;br&gt;
   &lt;br&gt;
      // display each child &lt;br&gt;
      while ($row = mysql_fetch_array($result)) { &lt;br&gt;
          // indent and display the title of this child &lt;br&gt;
          echo str_repeat('  ',$level).$row['title']."\n"; &lt;br&gt;
   &lt;br&gt;
          // call this function again to display this &lt;br&gt;
          // child's children &lt;br&gt;
          display_children($row['title'], $level+1); &lt;br&gt;
      } &lt;br&gt;
  } &lt;br&gt;
  ?&gt;
  </code></pre>
  
  <p>when i pass <code>display_children('',0);</code> i get </p>
  
  <pre><code>Food&lt;br&gt;
    Fruit&lt;br&gt;
      Red&lt;br&gt;
        Cherry&lt;br&gt;
      Yellow&lt;br&gt;
        Banana&lt;br&gt;
    Meat&lt;br&gt;
      Beef&lt;br&gt;
      Pork
  </code></pre>
  
  <p>to display the ‘Fruit’ subtree, you would run <code>display_children('Fruit',0);</code></p>
  
  <ul>
  <li>as you can see this is a recursive function . when the sub levels are growing , iti is inefficient , and make the query very slow . </li>
  </ul>
  
  <p>so i am planning to write a stored procedure , because it is efficient . is it possible ?? please show me an example code if it is possible , thanks in advance ................... </p>