Totaling multiple sums in one array

[zeusofolus]

I have a table with entries for ItemID and Amount ... I am querying this table and displaying the contents from the previous date by itemID and amount. I have the array grouping the items by Item ID what would like to be able to do is to get a total amount of ordered items for each ItemID.

For example what I have now is this:
Item 1 / Amount 12
Item 1 / Amount 50
------------------------------
Item 2 / Amount 5
Item 2 / Amount 7
Item 2 / Amount 3

and so on ... what I woul like to have is:

Item 1 / Total Amount: 62
------------------------------
Item 2 / Total Amount: 15

I have been sucessful in totaling the entire amount column for all items but not in getting the individual item totals.

My current code is as follows:

// Connect to the database 
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
  $query = 'SELECT o.OrderID, o.OrderDate, od.DetailItemName,od.DetailItemID,od.DetailQuantity, od.OrderID FROM orders AS o INNER JOIN orderdetails AS od USING(OrderID) WHERE TO_DAYS(OrderDate) = TO_DAYS(CURDATE() - INTERVAL 1 DAY) ORDER BY DetailItemID';
  $data = mysqli_query($dbc, $query);
  $Items = array();
  while ($row = mysqli_fetch_array($data)) {
    array_push($Items, $row);
  }
  mysqli_close($dbc);
  // Generate the  form by looping through the items array
  echo '<p>Total Order Amounts By Item For ' .date("Y/m/d", time()-86400) . ' </p>';
  $ItemID = $Items[0]['DetailItemID'];
  echo '<fieldset><legend>' . $Items[0]['DetailItemName'] . '</legend>';
  foreach ($Items as $Item) {
    // Only start a new fieldset if the ItemID has changed
    if ($ItemID != $Item['DetailItemID']) {
      $ItemID = $Item['DetailItemID'];
      echo '</fieldset><fieldset><legend>' . $Item['DetailItemName'] . '</legend>';
    }
	    // Display the Item and Total number of items form field
    echo 'Item Name ' . $Item['DetailItemName'] . '  / Amount ' . $Item['DetailQuantity'] . ' ';
  }
  echo '</fieldset>'

Thanks for looking,
Eric

[DangerDev]

Hi,
I am giving algo to do the job:
1. Sort the table row by Item ID.
2. Take two array arrayItemId to store Item Id and arrayAmountSum to store sum of amount.
3. Now look throw the data base rows. and sum the amount of rows where item id is same. and store item id in arrayItemId and sum of amount at arrayAmountSum , at the same index.

PS: above i have given hint.

Another method is to get the job done at throw the data base queries it self. You can search for it in google.

[zeusofolus]

I solved the issue using SQL instead of php it was very simple using the Sum() function ... Here is my query statement:

$sql = 'SELECT DetailItemName, Sum(DetailQuantity) AS total_quantity FROM orderdetails GROUP BY DetailItemName ;

Returns
---------------------
ItemName | total_amount
-----------------------------
Item1 | 35
-----------------------------
Item2 |12
-----------------------------
Etc...