Search Page:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search</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;
}
.result{
position: absolute;
z-index: 999;
top: 100%;
left: 0;
}
.search-box input[type="text"], .result{
width: 100%;
box-sizing: border-box;
}
/* Formatting result items */
.result p{
margin: 0;
padding: 7px 10px;
border: 1px solid #CCCCCC;
border-top: none;
cursor: pointer;
}
.result 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(){
/* 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="search-box">
<input type="text" autocomplete="off" id="id" name="id" placeholder="Search Products or codes..." />
<div class="result"></div>
</div>
</body>
</html>
Backend search page:
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "username", " ", "root");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
if(isset($_POST['term'])){
// Prepare a select statement
$sql = "SELECT * FROM ProductCodes WHERE item_name LIKE ?";
//$sql = "SELECT * FROM ProductCodes WHERE ('item_name' LIKE ?) OR ('id' LIKE ?)";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_term);
// Set parameters
$param_term = $_REQUEST['term'] . '%';
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
// Check number of rows in the result set
if(mysqli_num_rows($result) > 0){
// Fetch result rows as an associative array
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "<p>BA" . $row["id"] . " " . $row["item_name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}
//var_dump(mysqli_error($db_conx));
// Close statement
mysqli_stmt_close($stmt);
}
// close connection
mysqli_close($link);
?>
Please go easy - newbie alert! I have set up the above with a table (ProductCodes) which contains two colums - item_name and id. I need to get the search to check both of the columns but I've tried replacing
$sql = "SELECT * FROM ProductCodes WHERE item_name LIKE ?";
with
"$sql = "SELECT * FROM ProductCodes WHERE ('item_name' LIKE ?) OR ('id' LIKE ?)";
The other problem I have is that I need the value for the form to be the id field only but I can't see how to assign it. Any help is appreciated, I've tried all sorts of combinations but I'm just going round in circles.
You can use two params in query as follow: