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

Question

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

} 


?>

I assume you want to display a message if the result is empty. If so, you can do that in ajax itself. Within the ajax success function, check whether the data is empty or not. — Roby Raju Oommen, Feb 10 '22 at 02:46
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 10 '22 at 12:10
@Dharman I already mention that should use prepare to be secure. but I think you want me to add that feature to the code as well when I am refactoring it. I added the prepare statement as well. thanks — sajjad rezaei, Feb 10 '22 at 14:04

sajjad rezaei · Accepted Answer · 2022-02-10T14:02:00.153

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>

I don't want the user to click a button or press enter to display the $query. Is there any way to display the another query when user is not enter any value? I basically want to display all data when there is no value in the input field. — QQ10, Feb 10 '22 at 03:17
So you need the query to be executed when a user loads the page? for example when users visit the page can see all the products and also can search for products with the input. some thing like that? — sajjad rezaei, Feb 10 '22 at 03:21
If the input is empty do you still want to add them to `#searchresult`? — sajjad rezaei, Feb 10 '22 at 03:30
yes, that is exactly what I am thinking. when users visit the page can see all the products and also can search for products with the input. But when user search the product. They can only see what they have searched. Not all products. — QQ10, Feb 10 '22 at 03:37
I got an error. Fatal error: Call to undefined method mysqli_stmt::get_result() in form_livesearch.php — QQ10, Feb 10 '22 at 14:20
@QQ10 It requires the `mysqlnd` driver. can you install and enable it on your system? what's your operating system? and what's your PHP version? — sajjad rezaei, Feb 10 '22 at 14:29
I did a little searched. get_result() is only available at PHP v5.3.0 or above. I think my is lower than that. I have to use bind_result() & fetch(). And I am using windows 10. — QQ10, Feb 10 '22 at 14:43
Yep, that also works. go for it. you can use `phpinfo()` to see which PHP version you are using. you can also use `PDO` which is more popular instead of `mysqli`. hope to be successful. let me know if you had further questions. — sajjad rezaei, Feb 10 '22 at 14:49

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

1 Answers1