Hi in this article we are going to discuss about How To Create Search Form in PHP MySQL using Ajax. when type something in search box then related text appear down as auto suggest. auto suggest textbox is very user friendly for web projects in this post you can learn how to implement Google like search textbox in PHP MySQL using Ajax we can easily display auto suggest from the database under Textbox. Let’s start the search with auto suggest in PHP MySQL training.
How To Create Search Form in PHP MySQL using Ajax
1-Creating Database
- Open Phpmyadmin in your Browser
- Click on Database Tab Display on Top side
- Give the Database name “demo2”.
- After Creating Database Open it.
- Click on SQL Tab on Top area
- Copy the Below Source Code and paste it.
- Then Click on Go.
-- phpMyAdmin SQL Dump -- version 4.7.0 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Jan 25, 2018 at 07:44 PM -- Server version: 10.1.26-MariaDB -- PHP Version: 7.1.8 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `demo2` -- -- -------------------------------------------------------- -- -- Table structure for table `countries` -- CREATE TABLE `countries` ( `id` int(10) NOT NULL, `name` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `countries` -- INSERT INTO `countries` (`id`, `name`) VALUES (1, 'India'), (2, 'America'), (3, 'Australia'), (4, 'Bangladesh'), (5, 'Afghanistan'), (6, 'Albania'), (7, 'Algeria'), (8, 'Azerbaijan'), (9, 'Bahamas'), (10, 'Belarus'), (11, 'France'), (12, 'Gambia'), (13, 'Pakistan'); -- -- Indexes for dumped tables -- -- -- Indexes for table `countries` -- ALTER TABLE `countries` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `countries` -- ALTER TABLE `countries` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
OR Import DB File
After Downloading the source code extract it in your root folder.
- Open Phpmyadmin in your Browser
- Click on Database Tab Display on Top side
- Give the Database name “demo2”.
- After Creating Database Open it.
- Click on Import Tab on Top area
- You can Find Db file in Downloaded source code Select it.
- Then Click on Go.
2 – Creating Search Form and add Ajax Code
This step we are creating search form in html and get data like suggestion using ajax.
12456<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>How To Create Search Form in PHP MySQL using Ajax</title>
<meta name="description" content="">
<meta name="author" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://fonts.googleapis.com/css?family=Raleway:400,300,600" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="css/normalize.css">
<link rel="stylesheet" href="css/skeleton.css">
<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('.search-box input[type="text"]').on("keyup input", function(){
/* Get input value on change */
var inputVal = $(this).val();
var resultDropdown = $(this).siblings(".result");
if(inputVal.length){
$.get("backend-search.php", {term: inputVal}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});
// Set search input value on click of result item
$(document).on("click", ".result p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".result").empty();
});
});
</script>
</head>
<body>
<div class="container">
<div class="search-box">
<br><br>
<h3><b>Search Countries</b></h3>
<input class="u-full-width" type="text" autocomplete="off" placeholder="Type..." />
<div class="result"></div>
</div>
</body>
</html>
3 – Get Data From MySQL using PHP in Search Form
This step we are fetching data in search form from MySQL Database in PHP.
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo2", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt search query execution
try{
if(isset($_REQUEST['term'])){
// create prepared statement
$sql = "SELECT * FROM countries WHERE name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST['term'] . '%';
// bind parameters to statement
$stmt->bindParam(':term', $term);
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
echo "<p>" . $row['name'] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
If you facing any type of problem with this source code then you can Download the Complete source code in zip Formate by clicking the below button Download Now otherwise you can send Comment.