MySQL and PHP Looping

Question

I have been granted access to a database of third parties and you want to create a tool using their information. The database intended for their original purpose is very large and divided. I need to perform the following task:

From the diagram below, I need to perform the following tasks:

Look at the element in invTypes, check both invTypeMaterials and ramTypeRequirements to see if any materials are needed to create the element. If so, look at each of these materials in invTypes and repeat the process again to see if the components need it. This cycle continues until the verification of both invTypeMaterials and ramTypeRequirements is False, it can be 5 or 6 cycles, but 5 or 6 elements for each cycle to check, so there can be 1561 cycles, assuming 1 cycle for the original element , then 5 cycles into the material of which 5, 5 times.

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

$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 I’m sure, you all noticed that this code breaks down about every programming law when it comes to recursive database calls. Not very practical, especially in that subList() calls itself constantly until it finds it false. SQL is not my strong set, but I can’t figure out for my whole life how to overcome this problem.

Any pointers would be very helpful, of course I do not ask any of you to rewrite all my code for me, but if you have any ideas as to what I should consider, I would be grateful.

Answer 1

As a general solution, I would do the following:

• For each typeID , collect from both invTypeMaterials and ramTypeRequirements
• From the collected data, you create a new SELECT query and continue the loop

Original request

 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 just figured out what data from the extra tables is needed to load; expand if necessary.

materialTypeID and requiredTypeID will be non-zero for string matches and null otherwise.

Save the table of types that you have already downloaded before, for quick reference. Then for the second query, you replace the condition with something like `WHERE t.typeID IN ()

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

Answer 2

It seems that recursion is inevitable here. I join Jack's answer, just expand it with PHP code :)

I must warn you that I never executed it, so debugging will be required, but I hope you get this 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 overwrite func to return data.

We can also introduce some depth restrictions to avoid too much recursion.

As a rule, I would say that this is not a very convenient (for this task) organization of database data. This is convenient for storing data recursively, like this, but as you can see, it leads to an unknown number of iterations and database queries to get all the dependencies. And it can be expensive (PHP ↔ MySQL ↔ PHP ↔ ...), at each iteration we lose time, especially if the database is on a remote server, as in your case.

Of course, it would be great to rebuild the data structure to be able to immediately get all the requirements, but, as I understand it, you have read-only access to the database. The second solution that comes to my mind is the MySQL recursive stored procedure, which is also not possible here.

In some cases (not usually) it is useful to get as much data as possible in one query and work with it locally to reduce the number of iterations. It is difficult to say whether this is possible here because I do not know the size of the database, structure, etc., But, for example, if all the necessary dependencies are stored in one group, and the groups are not very large, it might be faster to get the whole group information in a single request to a PHP array, and then collect information from this array locally. But - this is just an assumption, and he needs testing and verification.