3

I have a live search where it pulls all the relevant information from the database and displays it in the table relevant to the search using Ajax so it doesn't refresh the page, I have it so after each search it resets back to nothing until it receives another input but I want it to display what it normally does (all the information).

Before input is received: http://prntscr.com/hnmui8

After input is received: http://prntscr.com/hnmv0r

After input is removed: http://prntscr.com/hnmv53

What is want it to look like after inputs removed: http://prntscr.com/hnmvhr

index.php

    <!DOCTYPE html>
<html>
    <head>
        <title>Webslesson Tutorial | Autocomplete Textbox using Bootstrap Typehead with Ajax PHP</title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.2/bootstrap3-typeahead.min.js"></script>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    </head>
    <body>
        <br /><br />
        <div class="container" style="width:600px;">
        <h2 align="center">Ajax live data search using Jquery PHP MySql</h2>
        <div class="form-group">
            <div class="input-group">
                <span class="input-group-addon">Search</span>
                <input type="text" name="search_text" id="search_text" placeholder="Search by Customer Details" class="form-control" />
            </div>
        </div>
        <br />
        <div id="result">
            <div class='table-responsive'>
                <table class='table table-bordered'>
                <tr>
                    <th>Customer name</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Potal code</th>
                    <th>Country</th>
                </tr>
                <?php
                    include('db.php');
                    $customers = DB::query('SELECT * FROM live');
                    foreach($customers as $p){
                      echo '<tr>
                              <td>'.$p["name"].'</td>
                              <td>'.$p["address"].'</td>
                              <td>'.$p["city"].'</td>
                              <td>'.$p["postCode"].'</td>
                              <td>'.$p["country"].'</td>
                            </tr>';
                    }

                    ?>
            </div>
        </div>
    </body>
</html>
<script>
    $('#search_text').keyup(function(){
      var txt = $(this).val();
      if(txt != ''){
        $.ajax({
          url: "fetch.php",
          method: "POST",
          data:{search:txt},
          dataType: "text",
          success:function(data){
            $('#result').html(data);
          }
        });
      }else{
        $('#result').html('');
    });
</script>

fetch.php

<?php
$connect = mysqli_connect("localhost", "root", "", "ajax");
$output = '';
$sql = "SELECT * FROM live WHERE name LIKE '%".$_POST['search']."%'";
$result = mysqli_query($connect, $sql);

if(mysqli_num_rows($result) > 0){
  $output .= "<h4 align='center'>Search result</h4>";
  $output .= "<div class='table-responsive'>
                <table class='table table-bordered'>
                  <tr>
                    <th>Customer name</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Potal code</th>
                    <th>Country</th>
                  </tr>";

 while($row = mysqli_fetch_array($result)){
   $output .= '
               <tr>
                <td>'.$row["name"].'</td>
                <td>'.$row["address"].'</td>
                <td>'.$row["city"].'</td>
                <td>'.$row["postCode"].'</td>
                <td>'.$row["country"].'</td>
               </tr>
              ';
 }
 echo $output;
}else{
  echo "There are no customers.";
}

?>

Thanks, Ethan

Ethan
  • 67
  • 1
  • 9
  • You should know that your fetch ajax is missing a `}` in your `else` statement also try removing the else statement to see if it helps – JeanPaul98 Dec 14 '17 at 22:27
  • Removing the else statement makes what was in the search stay there and not disappear, let alone reset it. – Ethan Dec 14 '17 at 22:31
  • So question. If you let the search search with '', would it return all the data? Just like when the page first loaded? Side note, it looks like your mysql statement is prone to sql injection attacks. You need to use prepared statements in order to safeguard against this. If you're not aware of what this is, I'd suggest searching about it, or opening another question about it after this one is solved. – Taplar Dec 14 '17 at 22:32
  • you could set a class on the original data (`display:none`) when you have results and remove when there are none? – Professor Abronsius Dec 14 '17 at 22:37
  • Did you check Network Section of the Developer Console on your browser? I guess that, `$_POST['search']` generates error on your endpoint when the input is empty. Reason; when input is empty txt will be equal to empty string, $.ajax or server is ignoring that. So; `$_POST['search']` will be undefined in this case which may result in HTTP 500 error. – Tuğca Eker Dec 14 '17 at 22:40
  • 2
    @TuğcaEker their keyup binding checks to see if the input is empty and only makes the ajax request if it is **not** empty. – Taplar Dec 14 '17 at 22:54
  • Thanks @Taplar, I updated my answer accordingly. Still it may be beneficial to apply this check on server side to provide freshness of data. – Tuğca Eker Dec 14 '17 at 23:00
  • @TuğcaEker no disagreement there, just sorta seemed like you went off on a tangent a little, :P – Taplar Dec 14 '17 at 23:02
  • You load all the data from the db when the page is generated, so searching via ajax seems like a waste of network and server resources. Cant you give each table element a class and bind an onkeyup function to search for ``s with no matching elements and set those to `display:none` or add a class which hides them? Then if input length = 0 remove class or remove display css? – miknik Dec 14 '17 at 23:14

2 Answers2

4

You could save your original dataset into a variable and if the input is '', than instead of setting the html content to '', you could restore the content from the variable like so:

var originalData = $('#result').html();
$('#search_text').keyup(function(){
  var txt = $(this).val();
  if(txt != ''){
    $.ajax({
      url: "fetch.php",
      method: "POST",
      data:{search:txt},
      dataType: "text",
      success:function(data){
        $('#result').html(data);
      }
    });
  } else {
    $('#result').html(originalData);
  }
});
dferenc
  • 7,918
  • 12
  • 41
  • 49
  • 1
    Yoooo thanks so much. Guess common sense isn't so common. – Ethan Dec 14 '17 at 23:08
  • @EthanPatchell Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted. – dferenc Dec 15 '17 at 09:37
1

Last Update: I (lately) realized that your JS code already have an empty string check (thanks to @Taplar). @dferenc already posted correct answer for your case. But still if you want to be sure that your list is "fresh" you can follow my solution.

When you send an empty string to the server as a value of the parameter, it may be ommited. So, you should add a conditional check for that.

Use

$queryWord = isset($_POST['search']) ? $_POST['search'] : '';
$sql = "SELECT * FROM live WHERE name LIKE '%".$queryWord."%'";

or (after PHP7, you can use null coalescing operator)

$queryWord =  $_POST['search'] ?? '';
$sql = "SELECT * FROM live WHERE name LIKE '%".$queryWord."%'";

instead of

$sql = "SELECT * FROM live WHERE name LIKE '%".$_POST['search']."%'";

Important Note: Beware of SQL Injections always! Protect your code against injections. You may start from here.

Important Note 2: I suggest you to use "Developer Console" feature of browsers. All commonly-used browsers have inline-tool for developers to debug, trace network requests and do some magic.

Tuğca Eker
  • 1,493
  • 13
  • 20