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.

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

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>

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.

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.

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.

So I took a few days break and now back tackling this problem. I believe I understand what you're saying. So here's my new php code that encodes array of rows. Now the issue is that it's showing "undefined" value when I do the console log. I don't get any errors though. So the issue is now definitely with the ajax code. Can you edit it so I can see what I did wrong? // BACKEND-SEARCH.PHP if(isset($_POST["term"])){ $param_term = $_POST["term"] .'%'; $get_city_select = $db->prepare("SELECT cities.city_id, cities.city_name, provinces.province_id, provinces.province_name, countries.country_id, countries.country_name FROM cities LEFT JOIN provinces ON cities.province_id = provinces.province_id LEFT JOIN countries ON provinces.country_id = countries.country_id WHERE city_name LIKE :param"); $get_city_select->bindParam(':param', $param_term); $get_city_select->execute(); $result_city_select = $get_city_select->fetchAll(PDO::FETCH_ASSOC); if(count($result_city_select) > 0) { $arr = array(); //create empty array foreach($result_city_select as $row) { $s_city_id = $row['city_id']; $s_city_name = $row['city_name']; $s_province_id = $row['province_id']; $s_province_name = $row['province_name']; $s_country_id = $row['country_id']; $s_country_name = $row['country_name']; $arr[] = array ( "cityid" => "{$s_city_id}", "cityname" => "{$s_city_name}", "provincename" => "{$s_province_name}", "countryname" => "{$s_country_name}" ); } echo json_encode($arr); } } // AJAX 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) { console.log(data.cityname); var citydata = '<div class="output-results"><p class="output-p1">' + data.cityname + '</p><p class="output-p2">' + data.provincename + ', '+ data.countryname + '</p></div>'; var citydata2 = '' + data.cityname + ', ' + data.provincename + ', ' + data.countryname + ''; $("#display-cities").html(citydata).show(); $( ".output-results" ).click(function() { $("#city-box-id").val(data.cityid); $("#city-box").val(citydata2); $("#display-cities").html(citydata).hide(); }); } }); } }); }); </script> EDIT: Btw when I console.log(data), it returns the values like this "(58) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]". So the data is working. It seems like I need a proper way to access these values inside an array.

Actually I found the issue. My query works fine. The issue was that only 1 user in my database matched all three parameters. Which is why it was only showing a single result. So it's all good now. Having said that if you would like to modify my query to show your example, that'd be great.

Back with a new problem. I have 8 tables interconnected. Table#1 - Users user_id | name Table#2 - user_categories id | user_id | category_id Table#3 - user_cities id | user_id | city_id Table#4 - user_dates id | user_id | dates_available Table#5 - categories category_id | category_name Table#6 - cities city_id | city_name Table#7 - provinces province_id | province_name Table#8 - categories country_id | country_name Each user will have multiple categories, cities and available dates listed in these tables. I simply want to retrieve and list each user and their data on a page. Here's my query. $url_city = 1; $url_category = 2; $url_date = '2021-07-19'; $find_records = $db->prepare("SELECT user_categories.*, categories.*, user_cities.*, cities.*, provinces.*, countries.*, user_dates.*, users.* FROM users LEFT JOIN user_categories ON users.user_id = user_categories.user_id LEFT JOIN user_cities ON users.user_id = user_cities.user_id LEFT JOIN user_dates ON users.user_id = user_dates.user_id LEFT JOIN categories ON user_categories.category_id = user_categories.category_id LEFT JOIN cities ON user_cities.city_id = cities.city_id LEFT JOIN provinces ON cities.province_id = provinces.province_id LEFT JOIN countries ON provinces.country_id = countries.country_id WHERE user_cities.city_id = :city_id AND user_categories.category_id = :category_id AND user_dates.date_available = :date_available GROUP BY users.user_id"); $find_records->bindParam(':city_id', $url_city); $find_records->bindParam(':category_id', $url_category); $find_records->bindParam(':date_available', $url_date); $find_records->execute(); $result_records = $find_records->fetchAll(PDO::FETCH_ASSOC); if(count($result_records) > 0) { foreach($result_records as $row) { $user_id = $row['user_id']; $name = $row['name']; $country_id = $row['country_id']; $country_code = $row['country_code']; $country_name = $row['country_name']; $province_id = $row['province_id']; $province_code = $row['province_code']; $province_name = $row['province_name']; $city_id = $row['city_id']; $city_name = $row['city_name']; $category_id = $row['category_id']; $category_name = $row['category_name']; } } There are no errors but the above query would only return a single row with only 1 "user" despite having multiple users in the "users" table. If I remove the GROUP BY, then it'll return multiple rows of the same user instead of all the relevant users. So what do you think I am doing wrong with my query?

I understand what you're saying but I am unable to locate the extra space. I tried the same LIKE query outside of the ajax search and it does return more than a single result. It's only when used with the ajax that something goes wrong. Here's my full code. Perhaps you can spot something I'm doing wrong. INDEX.PHP <form action="" method="POST"> <input type="text" id="city-box" class="type-input" name="sel-city" autocomplete="off" value="" placeholder="Type City Name" /> <input type="hidden" id="city-box-id" name="sel-city-hidden" value=""/> <div id="display-cities"></div> </form> JAVASCRIPT <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 citydata = '<div class="output-results"><p class="output-p1">' + data.cityname + '</p><p class="output-p2">' + data.provincename + ', '+ data.countryname + '</p></div>'; $("#display-cities").html(citydata).show(); $( ".output-results" ).click(function() { var id = $(this).attr('id'); $("#city-box-id").val(data.cityid); $("#city-box").val(data.cityname); $("#display-cities").html(citydata).hide(); }); } }); } }); }); </script> BACKEND-SEARCH.PHP if(isset($_POST["term"])){ $param_term = $_POST["term"] .'%'; $get_city_select = $db->prepare("SELECT cities.city_id, cities.city_name, provinces.province_id, provinces.province_name, countries.country_id, countries.country_name FROM cities LEFT JOIN provinces ON cities.province_id = provinces.province_id LEFT JOIN countries ON cities.country_id = countries.country_id WHERE city_name LIKE :param"); $get_city_select->bindParam(':param', $param_term); $get_city_select->execute(); $result_city_select = $get_city_select->fetchAll(PDO::FETCH_ASSOC); if(count($result_city_select) > 0) { foreach($result_city_select as $row) { $s_city_id = $row['city_id']; $s_city_name = $row['city_name']; $s_province_id = $row['province_id']; $s_province_name = $row['province_name']; $s_country_id = $row['country_id']; $s_country_name = $row['country_name']; echo json_encode (array("cityid" => "{$s_city_id}", "cityname" => "{$s_city_name}", "provincename" => "{$s_province_name}", "countryname" => "{$s_country_name}")); } } }

If my input text was "foot", then the $param_term would result in "foot%";