Jump to content

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


imgrooot
 Share

Go to solution Solved by imgrooot,

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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%";

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

}

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...
Posted (edited)
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 by imgrooot
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution
Posted (edited)

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 by imgrooot
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

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