In this tutorial we are going to develop an Auto completed search. when user type on textbox we give them suggestion to choose from dropdown. it really useful when millions of data inside table and quick result as well.
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");
?>Create index.php file were you can put jQuery and HTML code. We will use jquery-ui js library for search result and you can refer more about above link.
autocomplete method provide by UI library so, we can use it directly and call PHP file from server.
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>initialize autocomplete by source
$( ".selector" ).autocomplete({ source: [ "c++", "java", "php", "coldfusion", "javascript", "asp", "ruby" ]});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);