mySQL & PHP Looping

Question

I have been given access to a third parties database and wish to create a tool using their information. The database designed for their original purpose is very very large and segregated. I need to complete the following task:

From the the below Schema, I need to complete the following tasks:

Look up the item in the invTypes, check both the invTypeMaterials and ramTypeRequirements to see if any materials are need to build the item. If yes, then look up each of those materials in invTypes, and again repeat the process to see if those in turn need components. This loop keeps going until the the check on both the invTypeMaterials and ramTypeRequirements is False, this can be 5 or 6 loops, but 5 or 6 items per loop to check so could be 1561 loops assuming 1 loop for original item, then 5 loops per material of which there is 5, 5 times.

Now I tried to complete the code and came up with the follow:

$materialList = array();

function getList($dbc, $item) {

    global $materialList;
    // Obtain initial material list
    $materials = materialList($dbc, $item);

    // For each row in the database
    while ($material == mysqli_fetch_array($materials)) {
        // Check if there are any sub materials required
        if (subList($dbc, $material['ID'])) {
            // If so then recurse over the list the given quantity (it has already done it once)
            for ($i = 0; $i < $material['Qty'] - 1; $i++) {
                if (!subList($dbc, $material['ID'])) {
                    break;
                }
            }
        } else {
            // If there are no further materials then this is the base material so add to the array.
            $materialList .= array(
                "Name" => $mMaterial['Name'],
                "Qty" => $mMaterial['Qty'],
                "ID" => $material['ID']
            );
        }
    }

    return $materialList;
}

function subList($dbc, $item) {

    global $materialList;
    // Query the material incase it require further building
    $mMaterials = materialList($dbc, $item['ID']);

    // If the database returns any rows, then it must have more sub-materials required
    if (mysqli_num_rows($mMaterials) > 0) {
        // Check the sub-materials to see if they intern require futher materials
        if (subList($dbc, $material['ID'])) {
            // If the function returns true then iterate over the list the given quantity (its already done it once before)
            for ($i = 0; $i < $material['Qty'] - 1; $i++) {
                if (!subList($dbc, $material['ID'])) {
                    break;
                }
            }
        } else {
            // if the database returns 0 rows then this object is the base material so add to 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 AS ID, i.typeName AS Name,  m.Quantity AS Qty
                FROM invTypes AS i
                LEFT JOIN invTypeMaterials AS m
                ON m.materialTypeID = i.typeID
                LEFT JOIN ramTypeRequirements AS r
                ON r.typeID = i.typeID
                WHERE groupID NOT IN(278,269,278,270,268) AND m.typeID = $item";
    $snippets = mysqli_query($dbc, $query) or die('Error: ' . mysqli_error($dbc));

    return $snippets;
}

As im sure you have all noticed this code breaks about every programming law there is when it comes to recursive database calls. Not really practical especially in that subList() calls itself continually until it finds it's false. SQL isn't my strong suite, but I cannot for the life of me work out how to get over this problem.

Any pointers would be very helpful, I'm certainly not asking any of you to re-write my entire code for me, but if you have any ideas as to what I should consider I would be grateful.

Answer 1

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 :)

Answer 2

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.