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

imgrooot · July 12, 2021

I am using ajax live search to retrieve data from MySQL table. It works fine. However I noticed that it only returns a single result that matches the entire word instead of multiple results that match only the first couple letters.

For e.g.

DB Table

----------------

1. Foot

2. Foot Path

3. Football

----------------

If I type "foo" in the ajax search field, it won't return any results. But if I type "foot", it'll return "Foot". It won't return the other two results that contain the letters "Foot".

Here's my query

$param_term = $_POST["term"] . '%';

$get_data = $db->prepare("SELECT name FROM table WHERE title LIKE :param");
$get_data->bindParam(':param', $param_term);
$get_data->execute();

I was wondering how can I improve the above query so that it looks for all potential results based on partial matching of letters?

mac_gyver · July 12, 2021

it's acting like the submitted search value has some extra character(s) being added to it. it could also be that the code fetching and outputting the result is dropping value(s). if you cannot determine what's causing the issue, you will need to post all the code that it would take for someone to reproduce the problem.

Barand · July 12, 2021

Does this improve things?

$get_data->bindParam(':param', $param_term, PDO::PARAM_STR);

EDIT: nm - that's the default anyway.

Does var_dump($param_term) give the expected resul?

Edited July 12, 2021 by Barand

imgrooot · July 12, 2021

1 hour ago, Barand said:
Does this improve things?
$get_data->bindParam(':param', $param_term, PDO::PARAM_STR);
EDIT: nm - that's the default anyway.

Does var_dump($param_term) give the expected resul?

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

Barand · July 12, 2021

32 minutes ago, imgrooot said:

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

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".

imgrooot · July 12, 2021

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

}

Barand · July 12, 2021

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

imgrooot · July 23, 2021

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.

Edited July 23, 2021 by imgrooot

imgrooot · July 23, 2021

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.

Barand · July 23, 2021

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

imgrooot · July 23, 2021

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.

Barand · July 24, 2021

8 hours ago, imgrooot said:

Now the only thing left is to display multiple city rows in the div dropdown

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>

imgrooot · July 24, 2021

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.

imgrooot · July 24, 2021

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>

Edited July 24, 2021 by imgrooot

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information