How to search despite missing input
I am working on a search bar with a button next to it that is database related. When the button is clicked, it will fetch the data in the search bar, search the database and display the result. It still works.
What I cannot figure out is to do a search even if the data is missing in the search bar. For example, the user puts "ma" in the search bar. It will still display results like "mark", "marge", "may" and everything with "ma". How to do it?
index.php (search string)
<SCRIPT>
function loadSearch()
{
var xmlhttp;
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("myDivs").innerHTML=xmlhttp.responseText;
}
}
var bar = document.getElementById('sbar').value;
xmlhttp.open("GET","search.php?search="+bar,true);
xmlhttp.send();
}
</SCRIPT>
<span class="searchbox">
<input type="text" class="searchbar" id = "sbar" autocomplete="off" placeholder="Search for site..." autocorrect="off" autocapitalize="off">
</input>
</span>
search.php
<?php
$getsearch = $_GET['search'];
$conn = mysqli_connect("localhost", "root", "", "test1");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$today = date("Y-m-d");
$sql = "SELECT name, COUNT(*) as Referrals,
SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
FROM table1
WHERE name > '' AND name = '".$getsearch."' AND month(date) = MONTH(NOW()) AND year(date) = YEAR(NOW())
GROUP BY name
ORDER BY Referrals DESC";
$result = mysqli_query($conn, $sql);
?>
source to share
In your sql, replace the statement =
with LIKE
with a partial match character %
Here I have placed %
on both sides so that it searches anywhere in the string.
Whether you only use left or right depends on the fact that your result also depends on the search criteria.
$sql = "SELECT name, COUNT(*) as Referrals,
SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
FROM table1
WHERE name > '' AND name LIKE '%".$getsearch."%' AND month(date) = MONTH(NOW()) AND year(date) = YEAR(NOW())
GROUP BY name
ORDER BY Referrals DESC";
source to share