Salman Quader Salman Quader - 7 months ago 10
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

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);