Salman Quader Salman Quader - 1 year ago 43
SQL Question

search by multiple field. sometimes by one field and sometimes more than one field

I have search form. in here multiple field. sometimes I will form submit with one field, sometimes form submit with two and sometimes multiple field value.

if (isset($_POST['search'])) {
$projectName = $_POST['pName'];
$clientId = $_POST['s_by_clientName'];
$departmentId = $_POST['s_by_department'];
$statusName = $_POST['s_by_status'];
if (!empty($projectName))
{
$searchSql = mysql_query("select * from project_list where projectName='$projectName'");
}

if (!empty($clientId))
{
$searchSql = mysql_query("select * from project_list where client_id='$clientId'");
}

if (!empty($departmentId))
{
$searchSql = mysql_query("select * from project_list where department_id='$departmentId'");
}

if (!empty($statusName))
{
$searchSql = mysql_query("select * from project_list where status='$statusName'");
}

}



these query only for search by single field.
how to make query that performs searching by one or multiple field value
is it possible??

Answer Source

Use Concatenation in query Variable

 $searchSql ="select * from project_list where 1=1 ";
 if (isset($_POST['search'])) {
    $projectName     = $_POST['pName'];
    $clientId      = $_POST['s_by_clientName'];
    $departmentId  = $_POST['s_by_department'];
    $statusName  = $_POST['s_by_status'];
    if (!empty($projectName)) 
    {
        $searchSql. = " AND projectName='$projectName'");
    }
    if (!empty($clientId)) 
    {
        $searchSql. = " AND client_id='$clientId'");
    }
    if (!empty($departmentId)) 
    {
        $searchSql. = " AND department_id='$departmentId'");
    }
    if (!empty($statusName)) 
    {
        $searchSql. = " AND status='$statusName'");
    }
}
$result=mysql_query($searchSql);