Jump to content

imgrooot

Members
 View Profile  See their activity

  • Posts

    383

  • Joined

  • Last visited

  • Days Won

    1

 Content Type 

Posts posted by imgrooot

    Can someone show me a proper way to do this query function?

    in PHP Coding Help

    Posted

    4 hours ago, kicken said:

    A few rows of the raw data from the tables you're trying to query, or a similar representative set of data.  An example of the output you want to generate using the given data would help as well.

    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.

    Can someone show me a proper way to do this query function?

    in PHP Coding Help

    Posted

    11 minutes ago, Barand said:

    Can you post some sample data and the results you expect from that data?

    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.

    Can someone show me a proper way to do this query function?

    in PHP Coding Help

    Posted

    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 do I combine values of same col inside a foreach loop?

    in PHP Coding Help

    Posted

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

     

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

    in PHP Coding Help

    Posted · Edited by 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.

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

    in PHP Coding Help

    Posted

    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. 

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

    in PHP Coding Help

    Posted · Edited by 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?

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

    in PHP Coding Help

    Posted · Edited by 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.

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

    in PHP Coding Help

    Posted · Edited by 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);

    }
  }
}

     

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

    in PHP Coding Help

    Posted

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

     

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

    in PHP Coding Help

    Posted

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

     

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted · Edited by imgrooot

    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>

     

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted

    12 hours ago, Barand said:

    Adding to dropdown... 

    <script type='text/javascript'>
    $().ready(function() {
        
        $("#term").keyup(function() {
            var term = $(this).val()
            $.get(
                "",
                {"term":term},
                function(resp) {
                    $("#city").html("")
                    $.each(resp, function(k,v) {
                        var city = $("<option>", {"value":v.cityid, "text": v.cityname + ', ' + v.provincename + ', ' + v.countryname})
                        $("#city").append(city)
                    })
                },
                "JSON"
            )
        })
    })
</script>
</head>
<body>
    <form>
        Search<br>
        <input type='text' name='term' id='term' style='width: 400px;'> <br>
        <select name='city' id='city' size='10' style='width: 400px;'></select>
    </form>
</body>
</html>

     

    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.

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted

    1 hour ago, Barand said:

    BTW, over half your php code was creating unnecessary variables and transferring data from one array to another just to rename the keys. All you needed was 

      $get_city_select = $db->prepare("SELECT c.city_id as cityid
                                      , c.city_name as cityname
                                      , p.province_name as provincename
                                      , co.country_name as countryname
                                 FROM city c
                                   LEFT JOIN province p ON c.province_id = p.province_id
                                   LEFT JOIN country co ON p.country_id = co.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);
  exit(json_encode($result_city_select));

     

    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.

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted

    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. 

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted · Edited by imgrooot

    On 7/12/2021 at 6:47 PM, Barand said:

    It looks like you are not returning valid JSON in your ajax response.

    If you are retrieving multiple rows then you need to send back an encoded array of rows (the whole result set) and then process it as such.

    You are encoding each row thus returning a list of several json encode array strings

    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.

    GROUP BY not working properly when used with multiple joined tables

    in PHP Coding Help

    Posted

    19 minutes ago, mac_gyver said:

    GROUP BY some_col consolidates all the rows having the same some_col value into a single row, so, for each user, there would only be one row in the result set. if you want the rows for each user adjacent to each other in the result set, add an ORDER BY term (almost every select query should have one) and/or index/pivot the data when you retrieve it using the user_id as the main array index, to produce an array of sub-arrays for each user.

    as to why you are only getting the data for a single (the last user), your code inside the loop is needlessly copying variables to other variables, overwriting any previous data, until finally, after the end of the loop, you are left with only the last row of data. once you have fetched all the data into an appropriately named variable, $result_records, simply loop over that variable to produce the output that you want, no need to create 12 lines of code coping data from one variable to another for nothing.

    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.

    GROUP BY not working properly when used with multiple joined tables

    in PHP Coding Help

    Posted · Edited by imgrooot

    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? 

    Is there a better way to do php ajax live search from the database?

    in PHP Coding Help

    Posted

    45 minutes ago, Barand said:

    Very good!. In which case var_dump() would give 

    string(5) "foot%"

    But if, say, an extra character (maybe a space) had crept in there, it would show something like 

    string(6) "foot %"

    Your mission, should you choose to accept it, it to determine if $param_term really contains what you think it should contain. It's part of a process called "debugging".

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

}

     

×
×
  • 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.