Jump to content

imgrooot

Members
  • Posts

    378
  • Joined

  • Last visited

  • Days Won

    1

imgrooot last won the day on May 16 2018

imgrooot had the most liked content!

Recent Profile Visitors

3,297 profile views

imgrooot's Achievements

Advanced Member

Advanced Member (4/5)

1

Reputation

30

Community Answers

  1. 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.
  2. 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); }
  3. 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.
  4. 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.
  5. User_id and recipient are the same thing. Two different ways to identify the users in two separate tables.
  6. 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?
  7. 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.
  8. 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); } } }
  9. 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);
  10. Yes I want the combined sum of balance of all users. Group by won't work.
  11. 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; } }
  12. Alright so I figured it out. Based on your code, here's my new code. And in the backend-search.php, I had to change $_POST to $_GET. Now it works great. Thanks so much. <script type='text/javascript'> $().ready(function() { $('#display-cities').hide(); $("#city-box").keyup(function() { var term = $(this).val(); $.get("snippets/backend-search.php", {"term":term}, function(resp) { $("#display-cities").html("").show(); $.each(resp, function(k,v) { var citydata = '<div class="output-results"><p class="output-p1">' + v.cityname + '</p><p class="output-p2">' + v.provincename + ', '+ v.countryname + '</p></div>'; var citydata2 = '' + v.cityname + ', ' + v.provincename + ', ' + v.countryname + ''; $("#display-cities").append(citydata); $( ".output-results" ).click(function() { $("#city-box-id").val(val.cityid); $("#city-box").val(citydata2); $("#display-cities").html(citydata).hide(); }); }) }, "JSON" ) }) }) </script>
  13. I think I partially understand this code. Shouldn't there be ajax code somewhere that retrieves the city values? Can you please update my code with this solution? I'll keep trying to modify it myself in the mean time.
  14. Come on man, you could've told me this a few days ago. But I suppose did learn little more about arrays and json while rigorously working this problem. So it's all good. Thanks.
  15. Alright so I have a solution. Or at least half a solution. I found a way to access json array inside ajax code. Here's my updated code. <script> $(document).ready(function() { $('#display-cities').hide(); $("#city-box").keyup(function() { var name = $('#city-box').val(); if (name == "") { $('#display-cities').hide(); } else { $.ajax({ type: "POST", url: "snippets/backend-search.php", dataType: "json", cache: false, data: { term: name }, success: function(data) { var parsed = JSON.parse(JSON.stringify(data)); $.each(parsed, function (key, val) { var citydata = '<div class="output-results"><p class="output-p1">' + val.cityname + '</p><p class="output-p2">' + val.provincename + ', '+ val.countryname + '</p></div>'; var citydata2 = '' + val.cityname + ', ' + val.provincename + ', ' + val.countryname + ''; $("#display-cities").html(citydata).show(); $( ".output-results" ).click(function() { $("#city-box-id").val(val.cityid); $("#city-box").val(citydata2); $("#display-cities").html(citydata).hide(); }); }); } }); } }); }); </script> It works great. Now the only thing left is to display multiple city rows in the div dropdown.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.