PHP Getting SUM of Array Values While Comparing

Question

I have an array of Information States And Totals. I want to see how to get the Total of all the Totals for each state.

I know I have to use a loop but I am not sure which - what would I do?

This is what I have so far. It's making the array from a database.

$WorkFind = mysql_query(
  "SELECT Customers.State, WorkOrders.Total 
   FROM WorkOrders 
   INNER JOIN Customers 
   ON WorkOrders.CID=Customers.ID 
   WHERE WorkOrders.Type <> 'Warranty' 
   AND (WorkOrders.Status <> 'Closed' 
   OR WorkOrders.Status <> 'Cancelled') 
   AND WorkOrders.TransferID='0' 
   ORDER BY Customers.State ASC");

while ($Work = mysql_fetch_array($WorkFind)) { 
}

The while loop has made the array but I'm unsure what to put into it.

I'm using PHP 5.3 and MYSQL 5.2

How does your array look like? What is the problem with [`foreach`](http://www.php.net/foreach)? — kero, Dec 14 '13 at 22:54
You can format code blocks by indenting them with 4 spaces (or select the code and click on the curly braces above the text field) — Pascal Ockert, Dec 14 '13 at 23:17
Have you thought about using SQL [`SUM()`](https://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html)? — kero, Dec 14 '13 at 23:41
[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/q/12859942). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — Tobi Nary, Apr 06 '16 at 14:52
@kero sorry for the late reply this was old code and the code below by Pascal got it working. I don't usually code in MySQL like that but I would have to figure out the syntax to do the SUM() using PHP and PDO. — JukEboX, Jan 10 '17 at 15:13
@JukEboX Late is an understatement .. Jk, glad to hear you got it working - no matter how — kero, Jan 15 '17 at 20:58

Pascal Ockert · Accepted Answer · 2013-12-20T00:07:06.483

1

Try this:

$WorkFind = mysql_query("SELECT Customers.State AS State, WorkOrders.Total AS Total -- ...");

$total = array();

while ($Work = mysql_fetch_array($WorkFind)) {
  $state = $Work['State'];

  if (!isset($total[$state])) {
    $total[$state] = 0;
  }

  $total[$state] += $Work['Total'];
}

// format as simple table

echo '<table><tr><th>State</th><th>Total</th></tr>';

foreach ($total as $state_name => $state_total) {
  echo '<tr><td>' . $state_name . '</td><td>' . $state_total . '</td></tr>';
}

echo '</table>';

edited Dec 20 '13 at 00:07

answered Dec 14 '13 at 23:12

Pascal Ockert

984
5
10

That only gives me the total of all the work total. I need the total for each state. – JukEboX Dec 15 '13 at 15:27
Sorry, I missed that point. You can use an array, I added it to the code above. – Pascal Ockert Dec 16 '13 at 21:09
One more question. How do I format this if it comes out like this. Array ( [CT] => 35774.56 [DE] => 1779 [FL] => 9519.87 [MA] => 11875.82 [MD] => 87722.98 [NJ] => 212389.21 [NY] => 194354.32 [PA] => 31449.43 [RI] => 2872.56 [VA] => 61398.72 ) – JukEboX Dec 18 '13 at 23:47
1

You can use a foreach loop. I added one in the answer to output a table with the calculated values. With ```foreach ($total as $state_name => $state_total)``` you iterate over the ```$total``` array and set a variable ```$state_name``` to the current element's key (CT, DE, FL, ...) and a second variable ```$state_total``` to the element's value. – Pascal Ockert Dec 20 '13 at 00:11

PHP Getting SUM of Array Values While Comparing

1 Answers1