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