Making a PHP MySql search

Question

I am making a search feature in my app, i have username ,designation and district, so could anyone tell me the best approach of achieving it. It should be like whatever the user enters in search the script should search the database and get the required details. //my code here

<?php
include_once("db.php");
if(isset($_POST['district'])){
    $district=$_POST['district'];
}
if(isset($_POST['designation'])){
    $design=$_POST['designation'];
}
if(isset($_POST['name'])){
    $name=$_POST['name'];
}
if(isset($_POST['department'])){
    $dept=$_POST['department'];
}
$result=array();
$response=array();
if((isset($design)) &&(!isset($district)) && (!isset($name)))
{
    $a=$design;
    $stmt=$db->prepare("SELECT * FROM search WHERE designation LIKE ?");
    $stmt->bind_param("s",$a);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;
}
}
else if((isset($district)) &&(!isset($design)) && (!isset($name)))
{
    $a=$district;
    $stmt=$db->prepare("SELECT * FROM search WHERE district LIKE ?");
    $stmt->bind_param("s",$a);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;

    }
}
else if((isset($name)) &&(!isset($district)) && (!isset($district))){
    $a=$name;
    $stmt=$db->prepare("SELECT * FROM search WHERE name LIKE ?");
    $stmt->bind_param("s",$a);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;

    }
}
if((isset($name))&& (isset($design))){
    $stmt=$db->prepare("SELECT * FROM search WHERE name LIKE ? AND designation LIKE ?");
    $stmt->bind_param("ss",$name,$design);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;
    }
}
if(isset($dept)){
    $stmt=$db->prepare("SELECT * FROM search WHERE department LIKE ?");
    $stmt->bind_param("s",$dept);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;

    }
}
$response['result']=$result;
echo json_encode($response)
?>

above is the full code .

Answer 1

First of all, you need to change !empty and empty instead of isset and !isset because you are setting the variables on the top so it will be always set and will be in each and every condition so I have changed it to empty as shown in code below.

after that, you can make an array with parameters which fulfills the condition and finally I have implemented that in SQL and bind parameter using join, I hope it will work for you.

<?php
include_once("db.php");



$result=array();
$response=array();

if(isset($_POST['keyword']) && !empty($_POST['keyword']))
{
    $keyword = $_POST['keyword'];
    $sql = "SELECT * FROM search WHERE CONCAT(designation,' ', district, ' ', department, ' ',name) LIKE ?";

    $stmt=$db->prepare($sql);
    $key = "%".$keyword."%";
    $stmt->bind_param("s",$key);
    $stmt->execute();
    $d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;
    }
    $response['result']=$result;
    echo json_encode($response);
} else {
    echo json_encode(array("message"=>"Keyword is not defined"));
}

?>

Answer 2

try this.. I dint execute it.

`$query='SELECT * FROM search WHERE ';
$stmt;
if(isset($_POST['district'])){
    $query+='district like @district '
    $stmt=$db->prepare($query);
    $stmt->bind_param(@district,$_POST['district']);

}
if(isset($_POST['designation'])){
    if(strlen($query>27))// district is set
    {
        $query+=' AND ';
    }
   $query+='district like @designation'
    $stmt=$db->prepare($query);
    $stmt->bind_param(@designation,$_POST['designation']);
}
if(isset($_POST['name'])){
    if(strlen($query>27))// district/design  is set
    {
        $query+=' AND ';
    }
    $query+='district like @name'
    $stmt=$db->prepare($query);
    $stmt->bind_param(@name,$_POST['name']);
}
if(isset($_POST['department'])){
  if(strlen($query>27))// district/design/dept  is set
    {
        $query+=' AND ';
    }
    $query+='district like @department'
    $stmt=$db->prepare($query);
    $stmt->bind_param(@department,$_POST['department']);
}
$result=array();
$d=$stmt->get_result();
    while($b=$d->fetch_array(MYSQLI_ASSOC)){
        $result[]=$b;
    }`

Answer 3

make use of ajax requests. and your MySql query should be :

select * from user where uname like %username% or designation like %designation% or district like %district%

dont forget to use parameterized queries.

hope that helps.