Skip to content

Live data search using ajax. How to display another query when input is empty [closed]

I am trying to create a live search using ajax, jquery, php and mysql. The user enter some inputs, it send the search to form_livesearch.php. I got that part worked. Else if the input is empty, then display other query. (I need help with this part)

                 <div id="container" class="col-md-12"> 
                    <div class="row">
                      <h2>Quick Search</h2>
                      <input class='form-control' type="text" id='live_search' placeholder='Search our inventory'>

                      <br>
                      <br>

                      <h2 class="" id="searchresult">    
                      </h2>    
                    </div>
                  </div> 


    $(document).ready(function(){
       
       $("#live_search").keyup(function(){
                             
            var input = $(this).val();
            
            
            if(input != ""){
                
                $.ajax({
                    
                    url:"form_livesearch.php",
                    method:"POST",
                    data:{input:input},
                    
                    success:function(data){
                        
                        $("#searchresult").html(data);
                        $("#searchresult").css("display","block");
                        
                    } 
                    
                    
                });
    
                
                
            } else {
                
                 // If the input field is empty
                 // How display another php query here?
    
            }
           

                              
       });
       
       
       
   }); 

Here is the php and mysql I am trying to display when the input field is empty.

    <?php                 
                      
                      
    $query = "SELECT * FROM `my_db` . `my_table` WHERE s_category = 'policy' ORDER BY id ASC";
    
    $result = mysqli_query($db,$query);
                      
    if(!$result){
                    
        die("Query Failed " . mysqli_error($db));
        
    }                 
    
    if(mysqli_num_rows($result) > 0){
    ?>
                      
       <h3>Policies</h3>
            <ul>

               <?php
                    
                    while($row = mysqli_fetch_assoc($result)){
                        
                        $id = $row['id'];
                        $s_url = $row['s_url'];
                        $s_name = $row['s_name'];
                        $s_category = $row['s_category'];  
                        
                        ?>
                                            
                            <li><a href="<?php echo $s_url ?>"><?php echo $s_name?></a> <img src="https://www.xxxxxxx.xxx/xxxx/images/pdf.gif" alt="PDF"></li>
                   
                        <?php
                    }
               ?>
                
            </ul>
            
       <?php
            
    }
                      
?>                    

form_livesearch.php:

if(isset($_POST['input'])){

$input = $_POST['input'];
    
    //to prevent from mysqli injection
    // x'='x
    $input = stripcslashes($input);
    $input = mysqli_real_escape_string($db, $input);
    
    $input = str_replace('%', ' @', $input);
    $input = str_replace("'", ' @', $input);
    
    $query = "SELECT * FROM `my_db` . `my_table` WHERE s_name LIKE '%{$input}%' ORDER BY id ASC";
    
    $result = mysqli_query($db,$query);
    
    if(mysqli_num_rows($result) > 0){?>
       
       <table class="table table-bordered table-striped mt-4">
<!--
           <thead>
               <tr>
                   <th>id</th>
                   <th>name</th>
               </tr>
           </thead>
-->
           <tbody>
               <?php
                
                while($row = mysqli_fetch_assoc($result)){
                    
                    $id = $row['id'];
                    $s_url = $row['s_url'];
                    $s_name = $row['s_name'];
                    $s_category = $row['s_category'];
                    
                
                    
                    ?>
                    
                    <tr>
                        <td style="font-size: 14px;"><a href="<?php echo $s_url; ?>"><?php echo $s_name;?></a> <img src="https://www.xxxxx.xxxx/xxxxx/images/pdf.gif" alt="PDF"></td>
                    </tr>
                    
                    
                    
                    <?php
                }
           
    
            ?>
       </tbody>
   </table>
   
   
   <?php 
    
}else{
    
    echo "<h6 class='text-danger text-center mt-3'>No data Found</h6>";
}

} 


?>

Answer

You should handle this stuff in the PHP file. and by the way, the input can not be empty as you put the ajax in keyup event. it just happened when the user use the backspace to delete what he search.

So the form_livesearch.php PHP file should be something like this.

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);



$output = "";
if(isset($_POST['input'])){

    $input = $_POST['input'];
    if(!empty($input)){

        $input = str_replace('%', ' @', $input);
        $input = str_replace("'", ' @', $input);

        $input = "%$input%"; // prepare the $input variable 
        $query = "SELECT * FROM `my_db` . `my_table` WHERE s_name LIKE ? ORDER BY id ASC";
        $stmt = $conn->prepare($query); 
        $stmt->bind_param("s", $input); // here we can use only a variable
        $stmt->execute();
        

    }else{
        $query = "SELECT * FROM `my_db` . `my_table` WHERE s_category = 'policy' ORDER BY id ASC";
        $stmt = $conn->prepare($query); 
        $stmt->execute();
    }

    $result = $stmt->get_result(); // get the mysqli result
    
    
    if($result->num_rows > 0){ 
       
        if(empty($input))
            $output = '<table class="table table-bordered table-striped mt-4"><tbody>';
        else
            $output = '<h3>Policies</h3><ul>';
                
                while($row = $result->fetch_assoc()){
                    
                    $id = $row['id'];
                    $s_url = $row['s_url'];
                    $s_name = $row['s_name'];
                    $s_category = $row['s_category'];
                
                
                    if(empty($input))
                        $output .= '
                            <tr>
                                <td style="font-size: 14px;"><a href="' . $s_url . '">' . $s_name .'</a> <img src="https://www.xxxxx.xxxx/xxxxx/images/pdf.gif" alt="PDF"></td>
                            </tr>';
                    else
                        $output .= '<li><a href="' . $s_url . '">' . $s_name . '</a> <img src="https://www.xxxxxxx.xxx/xxxx/images/pdf.gif" alt="PDF"></li>';
                    
                }
           
    
        if(empty($input))
            $output .= '</tbody></table>';
        else
            $output .= '</ul>';
   
        echo $output;
    
    }else{
    
        echo "<h6 class='text-danger text-center mt-3'>No data Found</h6>";
    }

} 


?>

You can use a separate file to handle 2 types but as they are all about products it’s better to have one file.

It’s a good practice to return the data and let the frontend build the HTML output but if you want to build HTML in the PHP file, it’s better to wrap them in a string.

Also, use the prepare statement of MySQLi to prevent SQL injection. take a look at this example for more information.

And the html file should be something like this:

<div id="container" class="col-md-12"> 
    <div class="row">
      <h2>Quick Search</h2>
      <input class='form-control' type="text" id='live_search' placeholder='Search our inventory'>

      <br>
      <br>

      <h2 class="" id="searchresult">    
      </h2>    
    </div>
  </div> 

<script type="text/javascript">
    $(document).ready(function(){
    // will execute once the page load
       getData();
       $("#live_search").keyup(function(){
                             
            let input = $(this).val();
            getData(input);
        
       });
   }); 


function getData(input = ''){

    $.ajax({
                    
        url:"form_livesearch.php",
        method:"POST",
        data:{input:input},
        
        success:function(data){
            
            $("#searchresult").html(data);
            $("#searchresult").css("display","block");
            
        } 
                    
                    
    });         


}
</script>