Today, We want to share with you search in php mysql with examples.In this post we will show you how to fetch data from database in php using search button, hear for how to search and filter data in html table using php and mysql database [ with source code ] we will give you demo and example for implement.In this post, we will learn about PHP AJAX Live Search Box Autocomplete with an example.
Ajax Live Database Search
Creating the Database Table
CREATE TABLE brands (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
Creating the Search Form
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search - www.pakainfo.com</title>
<style type="text/css">
body{
font-family: Arail, sans-serif;
}
/* Formatting search box */
.search-box{
width: 300px;
position: relative;
display: inline-block;
font-size: 14px;
}
.search-box input[type="text"]{
height: 32px;
padding: 5px 10px;
border: 1px solid #CCCCCC;
font-size: 14px;
}
.response{
position: absolute;
z-index: 999;
top: 100%;
left: 0;
}
.search-box input[type="text"], .response{
width: 100%;
box-sizing: border-box;
}
/* Formatting response items */
.response p{
margin: 0;
padding: 7px 10px;
border: 1px solid #CCCCCC;
border-top: none;
cursor: pointer;
}
.response p:hover{
background: #f2f2f2;
}
</style>
<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(){
var inputVal = $(this).val();
var responseDropdown = $(this).siblings(".response");
if(inputVal.length){
$.get("backend-search.php", {term: inputVal}).done(function(data){
responseDropdown.html(data);
});
} else{
responseDropdown.empty();
}
});
// Set search input value on click of response item
$(document).on("click", ".response p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".response").empty();
});
});
</script>
</head>
<body>
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search country..." />
<div class="response"></div>
</div>
</body>
</html>
Processing Search Query in Backend
backend-search.php
<?php
$link = mysqli_connect("localhost", "root", "F9898Jayshd", "ap_v1_v23_v1");
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
if(isset($_REQUEST["term"])){
$sql = "SELECT * FROM brands WHERE name LIKE ?";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "s", $param_term);
$param_term = $_REQUEST["term"] . '%';
if(mysqli_stmt_execute($stmt)){
$response = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($response) > 0){
while($row = mysqli_fetch_array($response, MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}
mysqli_stmt_close($stmt);
}
mysqli_close($link);
?>
php mysql search database and display results
Example
<?php
$con= new mysqli("localhost","root","","Meembers_lisat");
$name = $_post['search'];
//$query = "SELECT * FROM members
// WHERE nic_nm LIKE '%{$name}%' OR profile_nm LIKE '%{$name}%'";
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$response = mysqli_query($con, "SELECT * FROM members
WHERE nic_nm LIKE '%{$name}%' OR profile_nm LIKE '%{$name}%'");
while ($row = mysqli_fetch_array($response))
{
echo $row['nic_nm'] . " " . $row['profile_nm'];
echo "<br>";
}
mysqli_close($con);
?>
I hope you get an idea about Advanced Search using PHP.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.