Databases
StackOverflow2013
Postsdbo
POSum php array (created from mysql results) depending on mysql values in another mysql column
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSum php array (created from mysql results) depending on mysql values in another mysql column
    text
    copied!<p>One table called <code>18_7_ChartOfAccounts</code> looks like this:</p> <pre><code>ID | AccountNumber ------------- 1 | 2310 2 | 2380 3 | 2610 </code></pre> <p>Another table called <code>2_1_journal</code> looks like this:</p> <pre><code>ID | Amount | DebitAccount -------------------------- 1 | 26.03 | 2310 2 | 200.00 | 2310 3 | 3.63 | 2380 4 | 119.83 | 2380 5 | 33.86 | 2610 6 | 428.25 | 2610 </code></pre> <p><strong>Aim is to get results that looks like this:</strong></p> <pre><code>DebitAccount 2310 total is: 226.03 DebitAccount 2380 total is: 123.46 DebitAccount 2310 total is: 462.11 </code></pre> <p>226.03 in this example is total of 26.03 + 200.00</p> <p>At first mysql code</p> <pre><code>$query = "SELECT j.Amount, j.DebitAccount FROM 18_7_ChartOfAccounts AS c LEFT JOIN 2_1_journal AS j ON (c.AccountNumber = j.DebitAccount)"; $sql = $db-&gt;prepare($query); $sql-&gt;execute(); $data = $sql-&gt;fetchAll(PDO::FETCH_ASSOC); </code></pre> <p>With <code>print_r($data);</code> get long list of arrays like</p> <pre><code>[31] =&gt; Array ( [Amount] =&gt; 26.03 [DebitAccount] =&gt; 2310 [32] =&gt; Array ( [Amount] =&gt; 200.00 [DebitAccount] =&gt; 2310 </code></pre> <p>If in mysql query use <code>SUM(j.Amount)</code> then get only one total amount (suppose total amount of Column <code>Amount</code>).</p> <p>With </p> <pre><code>foreach($data as $result){ if(strlen($result['Amount']) &gt; 0 ) { echo "Amount ". $result['Amount']. "Account name ". $result['DebitAccount']. "&lt;br&gt;"; print_r (array_sum($result)); } } </code></pre> <p>Get something like this</p> <pre><code>Amount 123.97Account name 2310 2433.97Amount 26.03Account name 2310 2336.03Amount 200.00Account name 2310 </code></pre> <p>Any ideas how to get necessary results (marked bold)?</p> <p><strong>Update</strong></p> <p>Changed $query to </p> <pre><code>$query = "SELECT SUM(j.Amount), j.DebitAccount FROM 18_7_ChartOfAccounts AS c LEFT JOIN 2_1_journal AS j ON (c.AccountNumber = j.DebitAccount) group by j.DebitAccount"; </code></pre> <p>with <code>print_r($data);</code> get array like this</p> <pre><code>Array ( [0] =&gt; Array ( [SUM(j.Amount)] =&gt; [DebitAccount] =&gt; ) [1] =&gt; Array ( [SUM(j.Amount)] =&gt; 110900.16 [DebitAccount] =&gt; 2310 ) [2] =&gt; Array ( [SUM(j.Amount)] =&gt; 3660.86 [DebitAccount] =&gt; 2380 ) </code></pre> <p>With array seems all works. Now with foreach changed to <code>echo "Amount ". $result['SUM(j.Amount)']. " Account name ". $result['DebitAccount']. "&lt;br&gt;";</code></p> <p>Get </p> <pre><code>Amount 110900.16 Account name 2310 Amount 3660.86 Account name 2380 Amount 85247.40 Account name 2610 </code></pre> <p>Seems also ok. Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload