Struggling with concept of an associative array that maps userIDs to partIDs and re-order quantity for the part.
We have bunch of parts that I need to re-order, but I must keep track of which user needs what parts re-purchased for them. The list of UserIDs comes from one table, then the inventory_used comes from another table.
Suppose a list like this:
Example One:
UserID PartID Qty_Used
1 3 2
1 4 7
2 1 4
2 4 3
3 3 5
After creating an array with the above information, I must create a re-order form (table) for the parts. Therefore, ignoring the userID, group them by the partID, and sum up the total Qty (per part). The re-order table should look something like this:
Example Two:
PartID Qty_to_Reorder
1 4
3 7
4 10
I know I'm going to take a ton of downvotes for failing to show code, but I can't wrap my mind around this seemingly simple problem. (This is for my office, not a school project).
How do I:
(1) Structure the first array (what would the loop to create it look like?), and then
(2) Loop through that array to summarize/group partIDs => Qty for re-order report, as per 2nd example above?
For the first loop, i was thinking of something like this:
Loop through UserIDs {
Loop through PartIDs {
$arrReorder[UserID][PartID] = Qty_Used;
}
}
Is that correct? How would I loop through $arrReorder to sum-up the qty used for each partID, and get the re-order report (example 2)?
SELECT SUM(Qty_Used) AS total FROM mytable WHERE PartID=3
PS: Using PHP
<?php
$data = array();
$data[] = array("UserID" => 1, "PartID" => 3, "Qty_Used" => 2);
$data[] = array("UserID" => 1, "PartID" => 4, "Qty_Used" => 7);
$data[] = array("UserID" => 2, "PartID" => 1, "Qty_Used" => 4);
$data[] = array("UserID" => 2, "PartID" => 4, "Qty_Used" => 3);
$data[] = array("UserID" => 3, "PartID" => 3, "Qty_Used" => 5);
$PartID = 3;
$sum = 0;
foreach ($data as $arr) {
if ($arr['PartID'] == $PartID)
$sum += $arr['Qty_Used'];
}
echo $PartID."\t".$sum."
";
?>
Arrays have a key and value where the value can be another array. Determine what is the key value.
I am assuming you have users consuming parts and you have to re-order the parts from your supplier. No where the user is a customer and the user has a auto re-order policy.
What triggers a reorder? If re-order quantity is 10 and user uses 1, there should be nine in stock.
Create the partsUsed array elements, This is a bit tricky:
$partsUsed[$part][] = array($qtyUsed,$user);
The reason the empty brackets [] is there is to allow duplicate part numbers in the parts used, and still key part as the key.
The value is an array to key the association between user and parts.
what you end up with is a sequentially numbered secondary key where the value is just a throw away, but allows duplicate part numbers.
$partsUsed[3][] = array(2,1);
$partsUsed[4][] = array(7,1);
$partsUsed[1][] = array(4,2);
$partsUsed[4][] = array(3,2);
$partsUsed[3][] = array(5,5);
ksort($partsUsed); // sorts key on part number
var_export($partsUsed);
Result array (var_export): array ( 1 => array ( 0 => array ( 0 => 4, 1 => 2, ), ), 3 => array ( 0 => array ( 0 => 2, 1 => 1, ), 1 => array ( 0 => 5, 1 => 5, ), ), 4 => array ( 0 => array ( 0 => 7, 1 => 1, ), 1 => array ( 0 => 3, 1 => 2, ), ), )
Notice part 3 and 4 have two arrays.
$reorder[1] = 4 ;
$reorder[2] = 7 ;
$reorder[4] = 10 ;
var_export($reorder);
Result array:
array (
1 => 4,
2 => 7,
4 => 10,
)
Now not sure how to determine what gets reordered and how many.
I'll show how to get the values:
foreach($partsUsed as $part => $value){
foreach($value as $k => $v){
echo "Part $part: Qty:$v[0] User:$v[1]
";
$qtyUsed[$part] += $v[1];
}
}
var_export($qtyUsed);
Outputs:
Part 1: Qty:4 User:2
Part 3: Qty:2 User:1
Part 3: Qty:5 User:3
Part 4: Qty:7 User:1
Part 4: Qty:3 User:2
$qtyUsed
array (
1 => 2,
3 => 4,
4 => 3,
)
foreach ($qtyUsed as $part => $qty){
$order[$part] = $reorder[$part];
}
var_export($order);
Result $order:
array (
1 => 4,
3 => 7,
4 => 10,
)