In this tutorial, we are learning about ajax live data search in PHP and MySQL tutorial and let's see
First, we need to Connect to the database table:
First, create a database table with some inserted data to search from. For this tutorial, we will create a table named "users" with the following fields: "id", "name", "email", and "phone". Already I created a Database and named as 'dailyaspirants'.
<?php
// Connect to the database
$host = 'localhost';
$dbname = 'dailyaspirants';
$user = 'root';
$pass = '';
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
try {
$pdo = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getCode());
}
?>
Create a PHP script: Create a PHP script that will handle the AJAX requests and return the search results. We will call this script "search.php". This script will accept a "search" parameter in the URL, which will contain the search string entered by the user.
if (isset($_GET['search'])) {
$search = $_GET['search'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :search OR email LIKE :search OR phone LIKE :search");
$stmt->execute(['search' => "%$search%"]);
$data = $stmt->fetchAll();
echo json_encode($data);
}
This code checks if there is a "search" parameter in the URL. If there is, it prepares a PDO statement that selects all rows from the "users" table where the "name", "email", or "phone" column matches the search parameter using the LIKE operator. The "%" signs in the search parameter are wildcards that match any character. The statement is then executed with the search parameter passed as a bound parameter array. The result set is fetched into an associative array, which is then encoded as a JSON object and echoed back to the client.
Read also: 8 Best PHP Development Tools free Download
Create a HTML:
<html>
<head>
<title>Ajax ajax live search in php mysql</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-GLhlTQ8iRABdZLl6O3oVMWSktQOp6b7In1Zl3/Jr59b6EGGoI1aFkw7cmDA6j6gD" crossorigin="anonymous">
</head>
<body>
<div class="container py-5" style="width:600px;">
<h1 class="text-center">Ajax Live Search in PHP and MYSQL</h1>
<div class="mb-3">
<form>
<label class="form-label">Enter the Search:</label>
<input class="form-control" type="text" id="search" placeholder="Search">
<div class="mb-3 py-5" id="results"></div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function() {
$('#search').keyup('input',function() {
var search = $(this).val();
if (search.length >= 2) {
$.ajax({
url: 'search.php',
data: {search: search},
type: 'GET',
dataType: 'json',
success: function(data) {
var html = '';
for (var i = 0; i < data.length; i++) {
html += '<div>' + data[i].name + ' - ' + data[i].email + ' - ' + data[i].phone + '</div>';
}
$('#results').html(html);
}
});
} else {
$('#results').html('');
}
});
});
</script>
</body>
</html>
This code sets up an HTML input element with an ID of "search" and a container element with an ID of "results". It also includes a jQuery script that binds an input event listener to the search input element. When the user types at least 2 characters into the input, an AJAX GET request is sent to the "search.php" script with the search parameter set to the value of the input. The response from the server is a JSON object, which is parsed and used to generate HTML elements for the search results. The HTML is then added to the "results.
