Auto completed search This tutorial we are going to learn how to develop auto completed search using PHP and jQuery. Some time large database value and user need some value quickly. So it really useful similar like google search engine.
Create Database table
--
-- Table structure for table `crud`
--
CREATE TABLE `crud` (
`firstname` varchar(30) NOT NULL,
`id` int(11) NOT NULL,
`lastname` varchar(30) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`reg_date` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `crud`
--
INSERT INTO `crud` (`firstname`, `id`, `lastname`, `email`, `reg_date`) VALUES
('arf', 5, 'trdtrrrr123123', 'erwe@gmail.com ', NULL),
('reaf', 6, 'er', 'rear.fr@gmail.com', NULL),
('loanr', 7, 'david', 'loan.david@uajp.cp', NULL),
('david', 8, 'johnson', 'r3wer@gmail.com', NULL),
('john', 9, 'william', 'testdf@gmail.com', NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `crud`
--
ALTER TABLE `crud`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `crud`
--
ALTER TABLE `crud`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENTLet’s create database connection file.
Create config.php file
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "test";
$conn = mysqli_connect($servername, $username, $password, $db);
// Check The Connection
if (mysqli_connect_errno() ){
echo "Database Connection Failed: " . mysqli_connect_error();
die;
}
mysqli_set_charset($conn,"utf8");
?>Next create index.php file and add it search form. We can call ajax_request.php file that get search input and check from database if value exists that display on dropdown.
Create index.php file
<?php include('config.php'); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="panel panel-card ">
<div class="panel-body">
<h2>Autocomplete search option</h2>
<div class="form-group search_container"><label>Search</label>
<input type="text" class="form-control" name="search" id="search">
</div>
</div>
</div><!-- End .panel -->
</div><!--end .col-->
</div><!--end .row-->
</div>
<script>
$( function() {
$( "#search" ).autocomplete({
source: 'ajax_request.php'
});
});
</script>
</body>
</html>Create ajax_request.php file
<?php
## Database configuration
include 'config.php';
$searchTerm = $_GET['term'];
$sql = "SELECT * FROM crud WHERE firstname LIKE '%".$searchTerm."%' OR lastname LIKE '%".$searchTerm."%' LIMIT 10";
$result = mysqli_query($conn,$sql);
$tutorialData = array();
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_array($result)) {
$tutorialData[]=$row['firstname']." ".$row['lastname'];
}
}
echo json_encode($tutorialData);