PHP MySQL Ajax Live Search

Welcome to a tutorial on how to search and display results from the database using PHP and MySQLPHP MySQL Ajax Live Search.

In this tutorial we’re going to create a live search box that will search the Movies table and show the results asynchronously. But, first of all, we need to create this table.

Search records using php ajax

Step 1: Creating the Database Table

-- Database: `test`

CREATE TABLE `search_records` (
  `id` int(6) NOT NULL,
  `movie_name` varchar(255) NOT NULL,
  `movie_release` varchar(255) NOT NULL,
  `image_path` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `search_records` (`id`, `movie_name`, `movie_release`, `image_path`) VALUES
(1, 'The Road Trick', '2011', 'p1.PNG'),
(2, 'Wynonna Earp', '2001', 'p2.PNG'),
(3, 'The Balled of Hug', '1998', 'p3.PNG'),
(4, 'Greys Anatomy', '2013', 'p4.PNG'),
(5, 'Step up 2', '1987', 'p5.PNG'),
(6, 'Liquid Science', '1991', 'p6.PNG'),
(7, 'The Stair Case', '2005', 'p8.PNG'),
(8, 'The Vietnam War', '2021', 'p9.PNG'),
(9, 'The Covenan', '2011', 'p10.PNG'),
(10, 'Marcella', '2018', 'p12.PNG');

Step 3: Creating the database connection

Create a file named db.php

db.php





<?php 
$conn = mysqli_connect("localhost","root","","test");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}
?>

Step 3: Creating the Search Form

Now, let’s create a simple web interface that allows user to live search the names of Movies available in our search_records table, just like an autocomplete or typeahead.

index.php

<?php

include('db.php')
?>


<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Example</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container mt-4">
  <h6 className="text-center text-success mt-5"><b>Search Movie by Name</b></h6>
    <div class="input-group mb-4 mt-3">
     <div class="form-outline">
        <input type="text" id="getMovieName"/>
    </div>
  </div>                   
  <table class="table table-striped">
    <thead>
      <tr>
        <th>Movie ID</th>
        <th>Movie Name</th>
        <th>Release Date</th>
        <th>Movie Poster</th>
      </tr>
    </thead>
    <tbody id="showSingleMovie">
      <?php  
            $sql = "SELECT * FROM search_records";
            $query = mysqli_query($conn,$sql);
            while($row = mysqli_fetch_assoc($query))
            {
              echo"<tr>";
               echo"<td><h6>".$row['id']."</h6></td>";
               echo"<td><h6>".$row['movie_name']."</h6></td>";
               echo"<td>".$row['movie_release']."</td>";
               echo"<td><img src='images/".$row['image_path']."' style='height:30px;' ></td>";  
              echo"</tr>";   
            }
        ?>
    </tbody>
  </table>
</div>
</body>
</html>
<script>
  $(document).ready(function(){
   $('#getMovieName').on("keyup", function(){
     var getMovieName = $(this).val();
     $.ajax({
       method:'POST',
       url:'searchMovie.php',
       data:{movie_name:getMovieName},
       success:function(response)
       {
            $("#showSingleMovie").html(response);
       } 
     });
   });
  });
</script>

Step 4: Creating search request file for PHP

<?php 
  include("db.php");

   $movie_name = $_POST['movie_name'];

   $sql = "SELECT * FROM search_records WHERE movie_name LIKE '$movie_name%'";  // or %$movie_name% <- Search for any alphabet
   $query = mysqli_query($conn,$sql);
   $data='';
   while($row = mysqli_fetch_assoc($query))
   {
       $data .=  "<tr><td>".$row['id']."</td><td>".$row['movie_name']."</td><td>".$row['movie_release']."</td><td><img src='images/".$row['image_path']."' style='height:30px;' ></td></tr>";
   }
    echo $data;
 ?>

Now Run the code in browser

Subscribe to My Programming YouTube Channel Before Downloading the code :

Please Read our more Articles

Export MySQL Data To Excel in PHP

How to Load millions record in React

Building an Image Gallery with Laravel and React

How To Create Charts In React With Database

Leave a Reply

Your email address will not be published. Required fields are marked *