title: Finding Top Parent (Recursive)
body: <p>Product table:</p>
  
  <pre><code>id | id_parent | margin
  ------------------------
  1  | NULL      | 10
  2  | 1         | 20
  3  | NULL      | 15
  4  | 2         | 10
  5  | 4         | 25
  </code></pre>
  
  <p>From data above, <strong>how to get margin total of product tree (id: 5)</strong>? It doesn't have fixed depth, it is dynamic.</p>
  
  <p>I currently achieve this with iteration from PHP (recursive mysqli_query), but it consumes huge hosting memory (error 508).</p>
  
  <p>Here is the PHP function:</p>
  
  <pre><code>function get_margintotal($product) {
      global $link;
  
      $query = "SELECT id_parent, margin FROM `products` WHERE id = ".$product['id_parent'];
      if (!$temp_products = mysqli_query($link, $query)) {
          die(json_encode(array("errmsg" =&gt; "Selecting supplier's margin.")));
      }
      $temp_product = mysqli_fetch_assoc($temp_products);
      $temp_product['margin'] = $product['margin'] + $temp_product['margin'];
  
      if (isset($temp_product['id_parent'])) {
          return get_margintotal($temp_product);
      }
      else {
          return $temp_product['margin'];
      }
  }
  </code></pre>
  
  <p>So I'm wondering if dynamic iteration can be done in MySQL or how to optimise the function to decrease server memory usage?</p>
  
  <p>Thank you.</p>