imgrooot

Gotcha. That makes more sense. This is what the mysql table looks like. user_referrals table referral_id | sponsor | user_id | posted_date --------------------------------------------- 1 1 2 2022-02-01 2 1 3 2022-02-01 3 1 4 2022-02-01 4 2 5 2022-02-02 5 2 6 2022-02-02 6 3 7 2022-02-03 Using the table above, I would be retrieving the following data results. - Sponsor #1 has 3 direct referrals(level 1) - Referral #2 has 2 direct referrals(level 2) - Referral #3 has 1 direct referral(level 2) In total, It would generate 3 referrals for level 1 and 3 referrals for level 2. I hope this clears it up for you guys.

What would the sample data test look like? The queries are simply based on a user_id. And the output is just the number of rows/referrals.

I'm sorry. Did I do something wrong? I gave you the info as requested.

It basically returns in digits. So for level 1, if the sponsor has 10 referrals, it'll return "10". Each row in "user_referrals" table is counted as a referral. The query is simply counting each rows and combining them. It only becomes an issue when I tried to combine these rows on the 2nd level of the query. It'll return "0" when it should be returning more than that.

Basically, there are two level referrals. I would like to get the count number for both. 1. Sponsor's referrals 2. Referrals' referral This is my code. Level 1 works of course but I'm unable to get the correct data on level 2. I was wondering if there is a more efficient way to do this query? // LEVEL 1 REFERRALS $root_user_id = 50; $find_referrals = $db->prepare("SELECT user_id FROM user_referrals WHERE sponsor = :sponsor"); $find_referrals->bindParam(':sponsor', $root_user_id); $find_referrals->execute(); $result_referrals = $find_referrals->fetchAll(PDO::FETCH_ASSOC); if(count($result_referrals) > 0) { $count_1 = 0; foreach($result_referrals as $key=>$row) { $user_id = $row['user_id']; $count_1 += 1; // LEVEL 2 REFERRALS $find_referrals_2 = $db->prepare("SELECT user_id FROM user_referrals WHERE sponsor = :sponsor"); $find_referrals_2->bindParam(':sponsor', $user_id); $find_referrals_2->execute(); $result_referrals_2 = $find_referrals_2->fetchAll(PDO::FETCH_ASSOC); if(count($result_referrals_2) > 0) { $count_2 = 0; foreach($result_referrals_2 as $key=>$row) { $count_2 += 1; } } else { $count_2 = 0; } } } else { $count_1 = 0; $count_2 = 0; } $level_1_refs = $count_1; $level_2_refs = $count_2;

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.

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); }

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.

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.

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

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?

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.

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); } } }

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);

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