The live data search module is very helpful in web and mobile applications, it makes your site visitors get a good user experience with searching the required content they are looking for.
Users can easily search the autocomplete search box in php mysql from the high volume of data. Ajax live search box shows the desired results to the user based on their searching inputs.
PHP AJAX Live Search with MySQL Database Example
In this PHP jQuery Ajax live search tutorial, I will explain how to create live data search and display search results from the MySQL database.
Create Table and Add Demo Data in Database
If you do not have a database table ready, use the following SQL query to create a technologies table with id and tech_name table properties and add some data into the technologies table to check that data using the jQuery live search box in PHP.
CREATE TABLE technologies ( id INT NOT NULL AUTO_INCREMENT , tech_name VARCHAR(255) NOT NULL , PRIMARY KEY (id) );
INSERT INTO technologies VALUES(1,'HTML');
INSERT INTO technologies VALUES(2,'CSS');
INSERT INTO technologies VALUES(3,'JAVASCRIPT');
INSERT INTO technologies VALUES(4,'JQUERY');
INSERT INTO technologies VALUES(5,'PHP');
INSERT INTO technologies VALUES(6,'AJAX');
INSERT INTO technologies VALUES(7,'NODEJS');
INSERT INTO technologies VALUES(8,'EXPRESSJS');
INSERT INTO technologies VALUES(9,'ANGULARJS');
INSERT INTO technologies VALUES(10,'REACTJS'); MySQL Database Connection
Inside your PHP project folder, you need to create a db.php file within the directory, inside this file, we will make the PHP database connection with the MySQL database.
Add the following db connection code in db.php file:
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "test";
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if(!$connection){
die('Database connection error : ' .mysql_error());
}
?> MySQL Live Search Query
Inside your project directory, you have to create the live-search.php file, and import the database connection within this file.
Additionally, create the query to fetch all the data from the technologies table and print it on the view. If the result isn’t found, then show the alert message to the user that data is not found.
Update the below codes in live-search.php file:
<?php
require_once "./db.php";
if (isset($_POST['query'])) {
$query = "SELECT * FROM technologies WHERE tech_name LIKE '{$_POST['query']}%' LIMIT 100";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) > 0) {
echo "<h5 class='mb-2'>Technologies</h5><ul>";
while ($res = mysqli_fetch_array($result)) {
echo "<li>" .$res['tech_name']. "</li>";
}
echo "</ul>";
} else {
echo "<div class='alert alert-danger mt-3 text-center' role='alert'>No results...</div>";
}
}
?> Integrate Ajax Live Database Search in PHP
I used the Bootstrap UI for styling the search component and also importing the jQuery through CDN. Use the AJAX to make the POST request to fetch the data from the database.
Create the index file, and update the below code in the index.php file.
<!DOCTYPE html>
<html>
<head>
<title>Live Search Example Using Ajax PHP MySQL</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container mt-5">
<div class="row justify-content-center">
<div class="col-6">
<div class="card">
<div class="card-header alert alert-primary text-center mb-3">
<h2>PHP Live Search From MYSQL</h2>
</div>
<div class="card-body px-5 py-3">
<input type="text" class="form-control mb-3" name="live_search" id="live_search" autocomplete="off" placeholder="Search ...">
<div id="results"></div>
</div>
</div>
</div>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#live_search").on('keyup', function () {
var query = $(this).val();
if (query != "") {
$.ajax({
url: 'ajax-live-search.php',
method: 'POST',
data: {
query: query
},
success: function (data) {
$('#results').html(data);
$('#results').css('display', 'block');
//Add focusin and focusout jquery events if you want to show results only when input focused.
$("#live_search").focusout(function () {
$('#results').css('display', 'none');
});
$("#live_search").focusin(function () {
$('#results').css('display', 'block');
});
}
});
} else {
$('#results').css('display', 'none');
}
});
});
</script>
</body>
</html> Integrate Ajax Live Database Search in PHP
Throughout this PHP MySQL example, I described how to create live data search in PHP using AJAX quickly. I also shared how to retrieve data from the MySQL database and display data results in live search.
Note: Instead of using on keyup you can also use the on change jquery event. Refer below script codes.
$(document).ready(function () {
$("#live_search").on('change', function () {
var query = $(this).val();
if (query != "") {
$.ajax({
url: 'ajax-live-search.php',
method: 'POST',
data: {
query: query
},
success: function (data) {
$('#results').html(data);
$('#results').css('display', 'block');
}
});
} else {
$('#results').css('display', 'none');
}
});
});