mySQL & PHP Looping

As a generic solution I would do the following:

• For every typeID, gather from both invTypeMaterials and ramTypeRequirements
• From the gathered data, you create a new SELECT query and continue the cycle

Initial query

SELECT t.*, m.materialTypeID, m.quantity AS m_quantity, r.requiredTypeID, r.quantity AS r_quantity
FROM invTypes t
LEFT JOIN invTypeMaterials m USING (typeID)
LEFT JOIN ramTypeRequirements r USING (typeID)
WHERE <conditions to select the types>

I've just made a guess at which data from the extra tables are required to load; expand where necessary.

The materialTypeID and requiredTypeID will be non-null for matches rows and null otherwise.

Keep a table of types you have already loaded before, for faster reference. Then for the second query you replace the condition to something like `WHERE t.typeID IN ()

Let me know if this makes sense and whether it's even close to what's useful to you :)

Looks like here recursion is unavoidable. I join Jack's answer, just will extend it with PHP code :)

I must warn you that I never executed it, so it will need debugging, but I hope you will get the idea. :)

$checked_dependencies = array();
$materials            = array();

function materialList( $ids ) {
    // if we have an array of IDs, condition is ".. in (...)"
    if(is_array($ids)) {
        $condition = 'IN ('.implode(',',$ids).')';
        // add all to checked dependencies
        foreach($ids as $id) { $checked_dependencies[] = $id; }
    }else{
    // otherwise, checking for particular ID
        $condition = "= {$ids}";
        // add to checked dependencies
        $checked_dependencies[] = $ids;
    }

    $query = "SELECT t.*, 
                     m.materialTypeID, m.quantity AS m_quantity, 
                     r.requiredTypeID,                  
                     r.quantity AS r_quantity
              FROM invTypes t
              LEFT JOIN invTypeMaterials m ON t.typeId = m.typeId
              LEFT JOIN ramTypeRequirements r ON t.typeId = r.typeId
              WHERE t.typeID {$condition}";

    $res = mysqli_query($dbc, $query);

    // this will be the list of IDs which we need to get
    $ids_to_check = array();

    while($material = mysqli_fetch_assoc($res)) {
         $materialList[] = $material; // you can get only needed fields
         // if we didn't check the dependencies already, adding them to the list
         // (if they aren't there yet)
         if(!in_array($material['materialTypeId'], $checked_dependencies) 
            && !in_array($material['materialTypeId'], $ids_to_check)                 
            && !is_null($material['materialTypeId'])) {
              $ids_to_check[] = $material['materialTypeId'];
         }
         if(!in_array($material['requiredTypeId'], $checked_dependencies) 
         && !in_array($material['requiredTypeId'], $ids_to_check)
         && !is_null($material['requiredTypeId'])) {
              $ids_to_check[] = $material['requiredTypeId'];
         }
    }

    // if the result array isn't empty, recursively calling same func
    if(!empty($ids_to_check)) { materialList($ids_to_check); }

}

I used a global array here, but it's easy to re-write the func to return data.

Also we can put some depth limit here to avoid too much recursion.

Generally, I'd say it is not a very convenient (for this task) organization of DB data. It's kinda comfortable to store data recursively like that, but, as you see, it results in an unknown amount of iterations and requests to database to get all the dependencies. And that might be expensive (PHP <-> MySQL <-> PHP <->...), on each iteration we lose time, especially if the DB is on remote server as in your case.

Of course, would be great to re-arrange the data structure for possibility to get all requirements at once, but as I understand you have a read-only access to the database. Second solution which comes to my head is a recursive MySQL stored procedure, which is also impossible here.

In some cases (not generally) it is good to get as much data as possible in one query, and operate with it locally, to lessen the iterations number. It is hard to say if it is possible here, because I don't know the size of DB and the structure, etc, but e.g. if all required dependencies are stored in one group, and the groups aren't enormously large, maybe it might be faster to get all the group info in one request to a PHP array and then collect the info from that array locally. But - it is only a guess and it needs testing and checking.