How do I combine values of same col inside a foreach loop?

[imgrooot]

Basically what I'm trying to do is retrieve users' balance from a table. Instead of showing individual balances, I would like to combine their individual balance into a total balance value for that table. I was wondering how do I achieve that? I'm assuming I have to use arrays of some sort?

Here's my code.

$total_balance = 100;

$find_balance = $db->prepare("SELECT recipient, balance FROM user_earnings WHERE balance >= :balance ORDER BY posted_date DESC LIMIT 1");
$find_balance->bindParam(':balance', $total_balance);
$find_balance->execute();
$result_balance = $find_balance->fetchAll(PDO::FETCH_ASSOC);
if(count($result_balance) > 0) {
  foreach($result_balance as $row) {
    $get_recipient = $row['recipient'];
    $get_balance   = $row['balance'];

    echo $get_balance;
  }
}

 

[requinix]

You mean you want the SUM of the balances? Perhaps GROUPed BY the recipient?

[imgrooot]

25 minutes ago, requinix said:

You mean you want the SUM of the balances? Perhaps GROUPed BY the recipient?

Yes I want the combined sum of balance of all users. Group by won't work. 

[Barand]

Do you mean

SELECT SUM(balance) as total FROM user_earnings;

which gives ...

3 hours ago, imgrooot said:

a total balance value for that table.

 

[imgrooot]

6 hours ago, Barand said:

Do you mean

SELECT SUM(balance) as total FROM user_earnings;

which gives ...

 

Here's the new code. Doesn't seem to sum the balance. Still shows individual balances. Also remember that I would like to use this SUM value outside the foreach loop.

$total_balance = 100;

$find_balance = $db->prepare("SELECT recipient, SUM(balance) as total FROM user_earnings WHERE balance >= :balance ORDER BY posted_date DESC LIMIT 1");
$find_balance->bindParam(':balance', $total_balance);
$find_balance->execute();
$result_balance = $find_balance->fetchAll(PDO::FETCH_ASSOC);
if(count($result_balance) > 0) {
  foreach($result_balance as $row) {
    $get_recipient = $row['recipient'];
    $get_balance   = $row['total'];
  }
}
var_dump($get_balance);

 

[ginerjm]

But that leaves out the elimination of certain records whose individual balance is not > the given parm in the where clause?

Of course I don't know why the concern on posted_date nor why the limit is 1.....

 

[imgrooot]

8 minutes ago, ginerjm said:

But that leaves out the elimination of certain records whose individual balance is not > the given parm in the where clause?

Of course I don't know why the concern on posted_date nor why the limit is 1.....

 

I think I should post my full code here. The SUM does work if it was just that query but actually that query is inside another query, which is why it's showing me individual records. How do I fix this?

And the reason for the posted_date and limit 1 is because I'm retrieving their last more entry of their earnings. That last entry shows their latest balance. 

$total_balance = 100;

$get_records = $db->prepare("SELECT user_id, wallet_address FROM users WHERE active = :active");
$get_records->bindValue(':active', 1);
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    $user_id        = $row['user_id'];
    $wallet_address = $row['wallet_address'];

    if(!empty($wallet_address)) {

      $find_balance = $db->prepare("SELECT recipient, SUM(balance) as total FROM user_earnings WHERE recipient = :recipient AND balance >= :balance ORDER BY posted_date DESC LIMIT 1");
      $find_balance->bindParam(':recipient', $user_id);
      $find_balance->bindParam(':balance', $total_balance);
      $find_balance->execute();
      $result_balance = $find_balance->fetchAll(PDO::FETCH_ASSOC);
      if(count($result_balance) > 0) {
        foreach($result_balance as $row) {
          $get_recipient = $row['recipient'];
          $get_balance   = $row['total'];
        }

      }
      var_dump($get_balance);

    }
  }
}

 

Edited January 27, 2022 by imgrooot

[Barand]

You have shown us several queries that don't give you what you want. Thus we know what you don't want.

We need to know what you do want.

What output would you want to see from this test table and why...

+----+-----------+---------+-------------+
| id | recipient | balance | posted_date |
+----+-----------+---------+-------------+
|  1 |         1 |   80.00 | 2022-01-09  |
|  2 |         2 |  100.00 | 2022-01-09  |
|  3 |         3 |  120.00 | 2022-01-09  |
|  4 |         1 |   85.50 | 2022-01-16  |
|  5 |         2 |  101.25 | 2022-01-16  |
|  6 |         3 |  113.25 | 2022-01-16  |
|  7 |         1 |   70.00 | 2022-01-23  |
|  8 |         2 |   80.00 | 2022-01-23  |
|  9 |         3 |  150.00 | 2022-01-23  |
+----+-----------+---------+-------------+

 

[imgrooot]

8 minutes ago, Barand said:

You have shown us several queries that don't give you what you want. Thus we know what you don't want.

We need to know what you do want.

What output would you want to see from this test table and why...

+----+-----------+---------+-------------+
| id | recipient | balance | posted_date |
+----+-----------+---------+-------------+
|  1 |         1 |   80.00 | 2022-01-09  |
|  2 |         2 |  100.00 | 2022-01-09  |
|  3 |         3 |  120.00 | 2022-01-09  |
|  4 |         1 |   85.50 | 2022-01-16  |
|  5 |         2 |  101.25 | 2022-01-16  |
|  6 |         3 |  113.25 | 2022-01-16  |
|  7 |         1 |   70.00 | 2022-01-23  |
|  8 |         2 |   80.00 | 2022-01-23  |
|  9 |         3 |  150.00 | 2022-01-23  |
+----+-----------+---------+-------------+

 

So in the above table it shows the same recipient in multiple rows. Basically I would like to retrieve the "balance" from the newest row for each user. Based on your table, here are the rows that qualify for that.

+----+-----------+---------+-------------+
| id | recipient | balance | posted_date |
+----+-----------+---------+-------------+
|  7 |         1 |   70.00 | 2022-01-23  |
|  8 |         2 |   80.00 | 2022-01-23  |
|  9 |         3 |  150.00 | 2022-01-23  |
+----+-----------+---------+-------------+

From here on, I would like to combine the balance into a single value for all three users. And I would like to be able to echo this "value($300)" outside the foreach loop.

I hope that makes sense.

Edited January 27, 2022 by imgrooot

[Barand]

Assuming the latested posted_date may not be the same for every recipient we first need to know what each recipient's latest date is.

This is done with a table subquery...

    (
    SELECT recipient
         , MAX(posted_date) as posted_date
    FROM user_earnings
    GROUP BY recipient
    ) latest

We then query the earnings table joined to this subquery matching on the recipient and date so we only process the earnings with the latest date

$res = $db->query("SELECT  e.recipient
                         , e.balance
                         , e.posted_date
                    FROM user_earnings e
                        JOIN
                        (
                        SELECT recipient
                             , MAX(posted_date) as posted_date
                        FROM user_earnings
                        GROUP BY recipient
                        ) latest USING (recipient, posted_date)
                        ");
$results = $res->fetchAll();

echo '<pre>' . print_r($results, 1) . '</pre>';                    // view results

$total_balance = array_sum(array_column($results, 'balance'));     // get the total balance

printf('$%0.2f', $total_balance) ;                                 //->   $300.00

if you are are not interesting in seeing the individual latest records, you can straight to the total with

mysql> SELECT  SUM(balance) as total_balance
    ->  FROM user_earnings e
    ->      JOIN
    ->      (
    ->      SELECT recipient
    ->           , MAX(posted_date) as posted_date
    ->      FROM user_earnings
    ->      GROUP BY recipient
    ->      ) latest USING (recipient, posted_date);
+---------------+
| total_balance |
+---------------+
|        300.00 |
+---------------+

 

[imgrooot]

53 minutes ago, Barand said:

Assuming the latested posted_date may not be the same for every recipient we first need to know what each recipient's latest date is.

This is done with a table subquery...

    (
    SELECT recipient
         , MAX(posted_date) as posted_date
    FROM user_earnings
    GROUP BY recipient
    ) latest

We then query the earnings table joined to this subquery matching on the recipient and date so we only process the earnings with the latest date

$res = $db->query("SELECT  e.recipient
                         , e.balance
                         , e.posted_date
                    FROM user_earnings e
                        JOIN
                        (
                        SELECT recipient
                             , MAX(posted_date) as posted_date
                        FROM user_earnings
                        GROUP BY recipient
                        ) latest USING (recipient, posted_date)
                        ");
$results = $res->fetchAll();

echo '<pre>' . print_r($results, 1) . '</pre>';                    // view results

$total_balance = array_sum(array_column($results, 'balance'));     // get the total balance

printf('$%0.2f', $total_balance) ;                                 //->   $300.00

if you are are not interesting in seeing the individual latest records, you can straight to the total with

mysql> SELECT  SUM(balance) as total_balance
    ->  FROM user_earnings e
    ->      JOIN
    ->      (
    ->      SELECT recipient
    ->           , MAX(posted_date) as posted_date
    ->      FROM user_earnings
    ->      GROUP BY recipient
    ->      ) latest USING (recipient, posted_date);
+---------------+
| total_balance |
+---------------+
|        300.00 |
+---------------+

 

So your above query solves the problem of combining the balances into a total balance value. 

But it's still missing a few things.

1. Retrieve active users

2. Check to see if the active users have a wallet address

3. Retrieve recipients who's balance is greater than the set value

4. Retrieve not only the total balance but individual recipients and their balance.

So based on your code, here's the updated code.

$total_balance = 100;

$get_records = $db->prepare("SELECT user_id, wallet_address FROM users WHERE active = :active");
$get_records->bindValue(':active', 1);
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    $user_id        = $row['user_id'];
    $wallet_address = $row['wallet_address'];

    if(!empty($wallet_address)) {

      $res = $db->query("SELECT e.recipient, e.balance, e.posted_date
      FROM user_earnings e
        JOIN (
        SELECT recipient
             , MAX(posted_date) as posted_date
        FROM user_earnings
        WHERE e.balance >= :balance
        GROUP BY recipient
        ) latest USING (recipient, posted_date)
      ");
      $res->bindParam(':balance', $total_balance);
      $res->execute();
      $results = $res->fetchAll();
      
      $recipient = array_column($results, 'recipient');     // get the total balance
      $balance   = array_column($results, 'balance');
      
      foreach($recipient as $value) { // show each recipient
        echo $value, "\n";
      }
      foreach($balance as $value) { // show each recipient's balance
        echo $value, "\n";
      }
      
      $total_balance = array_sum(array_column($results, 'balance')); // get the total balance
      echo $total_balance, "\n"; //->   $300.00

    }
  }
}

There are a few things wrong with it.

1. The "WHERE" clause I added to your query gives an error "Unknown column 'e.balance'".

2. To retrieve the individual recipients and their balance, I added those two foreach loops. Not sure this if the correct method. Please advise.

3. Considering your query is inside another foreach loop, it's showing duplicate results. So perhaps you have a method to make this more efficient?

Edited January 27, 2022 by imgrooot

[Barand]

41 minutes ago, imgrooot said:

But it's still missing a few things.

I had no intention of rewriting your application - just showing you how to query the data you wanted.

[Barand]

2 hours ago, imgrooot said:

So in the above table it shows the same recipient in multiple rows. Basically I would like to retrieve the "balance" from the newest row for each user. Based on your table, here are the rows that qualify for that.

+----+-----------+---------+-------------+
| id | recipient | balance | posted_date |
+----+-----------+---------+-------------+
|  7 |         1 |   70.00 | 2022-01-23  |
|  8 |         2 |   80.00 | 2022-01-23  |
|  9 |         3 |  150.00 | 2022-01-23  |
+----+-----------+---------+-------------+

From here on, I would like to combine the balance into a single value for all three users. And I would like to be able to echo this "value($300)" outside the foreach loop.

No mention of balances over 100 or wallets when I asked what you wanted to do.

Is there any relationship between user_id and recipient?

[imgrooot]

36 minutes ago, Barand said:

No mention of balances over 100 or wallets when I asked what you wanted to do.

Is there any relationship between user_id and recipient?

User_id and recipient are the same thing. Two different ways to identify the users in two separate tables.

[ginerjm]

I"m curious.   Are you actually saving a "balance" amount on your records?  You are calling it balance but then you are adding them up so that sum total could not possibly be a balance.

[imgrooot]

3 minutes ago, ginerjm said:

I"m curious.   Are you actually saving a "balance" amount on your records?  You are calling it balance but then you are adding them up so that sum total could not possibly be a balance.

The full table includes deposit, withdrawal, balance. The above table Barand added, shows only the balance column. 

Which is I'm only retrieving the last row from each user because that showcases their current balance. 

[Barand]

3 hours ago, imgrooot said:

So perhaps you have a method to make this more efficient?

To make what more efficient. You still have told us what you are trying to do. What do the wallets have to do with the balance totals, for instance?

[imgrooot]

25 minutes ago, Barand said:

To make what more efficient. You still have told us what you are trying to do. What do the wallets have to do with the balance totals, for instance?

Sorry I thought that was clear. 

But simply put, those are the conditions to get rid of users who don't qualify for the list. So any user who is not active or doesn't have a wallet address(crypto) or doesn't have $100+ balance, does not make the list.

Edited January 27, 2022 by imgrooot

[ginerjm]

Again I don't know what the purpose of this table is.  It does however seem to be rather differently designed in that these records seem to represent transactions and the deposit and withdrawal columns are simply 'transaction amounts' and deserve only a single column.  The balance is always a calculated amount which then comes from the collection of transactions (records) whenever it is needed, and would not be stored anywhere.

[Barand]

Don't use two queries (and two loops).

Join to the users table and match on the criteria for accceptance (wallet and active)

SELECT  e.recipient
	 , e.balance
	 , e.posted_date
FROM user_earnings e
    JOIN users u ON e.recipient = u.user_id
                 AND wallet_address <> ''
                 AND active
                 AND balance > 100
	JOIN
	(
	SELECT recipient
		 , MAX(posted_date) as posted_date
	FROM user_earnings
	GROUP BY recipient
	) latest USING (recipient, posted_date)
;

 

[imgrooot]

15 hours ago, Barand said:

Don't use two queries (and two loops).

Join to the users table and match on the criteria for accceptance (wallet and active)

SELECT  e.recipient
	 , e.balance
	 , e.posted_date
FROM user_earnings e
    JOIN users u ON e.recipient = u.user_id
                 AND wallet_address <> ''
                 AND active
                 AND balance > 100
	JOIN
	(
	SELECT recipient
		 , MAX(posted_date) as posted_date
	FROM user_earnings
	GROUP BY recipient
	) latest USING (recipient, posted_date)
;

 

Thanks for updating the query. Think I understand it now.

Here is my updated query using your example. It shows both individual balance for each user and also the total balance for combined users. Seems to work fine now.

$total_balance = 100;

$get_records = $db->query("SELECT e.recipient, e.balance, e.posted_date, u.wallet_address
  FROM user_earnings e
    JOIN users u ON e.recipient = u.user_id
      AND wallet_address <> ''
      AND active = 1
      AND balance >= $total_balance
    JOIN (
      SELECT recipient, MAX(posted_date) as posted_date
      FROM user_earnings
      GROUP BY recipient
    ) latest USING (recipient, posted_date)
");
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  $ub = 0;
  foreach($result_records as $key => $row) {
    $recipient      = $row['recipient'];
    $wallet_address = $row['wallet_address'];
    $balance        = $row['balance'];

    $ub            += $balance;
    $total_balance  = $ub;

    var_dump($balance);

  }
  var_dump($total_balance);
}

 

[ginerjm]

You do realize that you are running your query twice?  And how it works surprises me .

[imgrooot]

1 hour ago, ginerjm said:

You do realize that you are running your query twice?  And how it works surprises me .

How am I running my query twice? Please showcase how you would redo my query?

And yes it seems to give me the results I want.

[ginerjm]

The call to 'query' is a query execution.  The call to execute is another form of running a query.  Dump the execute  and add some error checking to make sure your query is run before proceeding.  Something like:

if (!$get_records = $db->query(.....))
{
   handle a failure to run
}
now process your records