PHP 8 AJAX Live Data Search with MySQL Tutorial
PHP 8 AJAX Live search tutorial; In this quick example, we will share with you how to profoundly build a simple live data search in PHP with the help of the MySQL database.
The live data search module is exorbitantly helpful in web and mobile applications; it makes your site visitors life facile by providing them top-notch user experience with utmost profoundness in scanning the content they are looking for.
A search experience has to be immaculate; the high volume of data can easily be searched and scanned through autocomplete search box in php mysql. Ajax live search box shows the desired results to the user based on their searching inputs.
Read more: PHP AJAX Select2 Multi-Search Tutorial
PHP AJAX Live Search with MySQL Database Example
In this PHP jQuery Ajax live search tutorial, we will explain how to create live data search and display search results from the MySQL database.
Generate Table and Add Test Data in Database
If you do not have a database table ready, use the following SQL query to create a Songs table with id and song_name table properties.
Further, add some data into the Song table to check that data using the jQuery live search box in PHP.
CREATE TABLE Songs (
id int not null,
song_name varchar(50) not null);
INSERT INTO songs VALUES(1,'Metallica');
INSERT INTO songs VALUES(2,'Megadeth');
INSERT INTO songs VALUES(3,'Anthrax');
INSERT INTO songs VALUES(4,'Eric Clapton');
INSERT INTO songs VALUES(5,'ZZ Top');
INSERT INTO songs VALUES(6,'Van Halen');
INSERT INTO songs VALUES(7,'Lynyrd Skynyrd');
INSERT INTO songs VALUES(8,'ACDC');
INSERT INTO songs VALUES(9,'Black Album');
INSERT INTO songs VALUES(10,'Master of Puppets');
INSERT INTO songs VALUES(11,'Endgame');
INSERT INTO songs VALUES(12,'Peace Sells');
INSERT INTO songs VALUES(13,'The Greater of 2 Evils');
INSERT INTO songs VALUES(14,'Reptile');
INSERT INTO songs VALUES(15,'Greatest Hits');MySQL Database Connection
Inside your PHP project folder, you need to create a database folder and 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 database/db.php file:
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "php_db";
$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 ajax-live-search.php file; we will import the database connection within this file.
Additionally, create the query to fetch all the data from the Songs table and print it on the view. If the result doesnt found, then show the alert message to the user that data not found.
Update the ajax-live-search.php file:
<?php
require_once "./database/db.php";
if (isset($_POST['query'])) {
$query = "SELECT * FROM Songs WHERE song_name LIKE '{$_POST['query']}%' LIMIT 100";
$result = mysqli_query($connection, $query);
if (mysqli_num_rows($result) > 0) {
while ($res = mysqli_fetch_array($result)) {
echo $res['song_name']. "<br/>";
}
} else {
echo "
<div class='alert alert-danger mt-3 text-center' role='alert'>
Song not found
</div>
";
}
}
?>Create Ajax Live Database Search in PHP
In the final step, we have to implement the live search eventually. We will use the Bootstrap UI for styling the search component and also import 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>Ajax PHP MySQL Live Search Example</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" style="max-width: 555px">
<div class="card-header alert alert-warning text-center mb-3">
<h2>PHP MySQL Live Search</h2>
</div>
<input type="text" class="form-control" name="live_search" id="live_search" autocomplete="off"
placeholder="Search ...">
<div id="search_result"></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").keyup(function () {
var query = $(this).val();
if (query != "") {
$.ajax({
url: 'ajax-live-search.php',
method: 'POST',
data: {
query: query
},
success: function (data) {
$('#search_result').html(data);
$('#search_result').css('display', 'block');
$("#live_search").focusout(function () {
$('#search_result').css('display', 'none');
});
$("#live_search").focusin(function () {
$('#search_result').css('display', 'block');
});
}
});
} else {
$('#search_result').css('display', 'none');
}
});
});
</script>
</body>
</html>
Conclusion
Throughout this PHP MySQL example, we described how to create live data search in PHP using AJAX quickly; we also shared how to retrieve data from the MySQL database and display data results in live search.
We believe you liked this basic tutorial and will share it with others.
