mySQL & PHP Looping -

i have been given access third parties database , wish create tool using information. database designed original purpose very large , segregated. need complete following task:

from the below schema, need complete following tasks:

look item in invtypes, check both invtypematerials , ramtyperequirements see if materials need build item. if yes, each of materials in invtypes, , again repeat process see if in turn need components. loop keeps going until the check on both invtypematerials , ramtyperequirements false, can 5 or 6 loops, 5 or 6 items per loop check 1561 loops assuming 1 loop original item, 5 loops per material of there 5, 5 times.

now tried complete code , came follow:

$materiallist = array();  function getlist($dbc, $item) {      global $materiallist;     // obtain initial material list     $materials = materiallist($dbc, $item);      // each row in database     while ($material == mysqli_fetch_array($materials)) {         // check if there sub materials required         if (sublist($dbc, $material['id'])) {             // if recurse on list given quantity (it has done once)             ($i = 0; $i < $material['qty'] - 1; $i++) {                 if (!sublist($dbc, $material['id'])) {                     break;                 }             }         } else {             // if there no further materials base material add array.             $materiallist .= array(                 "name" => $mmaterial['name'],                 "qty" => $mmaterial['qty'],                 "id" => $material['id']             );         }     }      return $materiallist; }  function sublist($dbc, $item) {      global $materiallist;     // query material incase require further building     $mmaterials = materiallist($dbc, $item['id']);      // if database returns rows, must have more sub-materials required     if (mysqli_num_rows($mmaterials) > 0) {         // check sub-materials see if intern require futher materials         if (sublist($dbc, $material['id'])) {             // if function returns true iterate on list given quantity (its done once before)             ($i = 0; $i < $material['qty'] - 1; $i++) {                 if (!sublist($dbc, $material['id'])) {                     break;                 }             }         } else {             // if database returns 0 rows object base material add array.             $materiallist .= array(                 "name" => $mmaterial['name'],                 "qty" => $mmaterial['qty'],                 "id" => $material['id']             );             return true;         }     } else {         return false;     } }  function materiallist($dbc, $item) {      // query     $query = "  select i.typeid id, i.typename name,  m.quantity qty                 invtypes                 left join invtypematerials m                 on m.materialtypeid = i.typeid                 left join ramtyperequirements r                 on r.typeid = i.typeid                 groupid not in(278,269,278,270,268) , m.typeid = $item";     $snippets = mysqli_query($dbc, $query) or die('error: ' . mysqli_error($dbc));      return $snippets; } 

as im sure have noticed code breaks every programming law there when comes recursive database calls. not practical in sublist() calls continually until finds it's false. sql isn't strong suite, cannot life of me work out how on problem.

any pointers helpful, i'm not asking of re-write entire code me, if have ideas should consider grateful.

as generic solution following:

• for every typeid, gather both invtypematerials , ramtyperequirements
• from gathered data, create new select query , continue cycle

initial query

select t.*, m.materialtypeid, m.quantity m_quantity, r.requiredtypeid, r.quantity r_quantity invtypes t left join invtypematerials m using (typeid) left join ramtyperequirements r using (typeid) <conditions select types> 

i've made guess @ data tables required load; expand necessary.

the materialtypeid , requiredtypeid non-null matches rows , null otherwise.

keep table of types have loaded before, faster reference. second query replace condition `where t.typeid in ()

let me know if makes sense , whether it's close what's useful :)