由多个字段搜索。有时由一个字段，有时多个字段

Question

我有搜查表。在这里多个领域。有时我会用一个字段形成提交，有时用两个字段值提交，有时使用多个字段值。

     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 1

在查询变量中使用串联

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

NOTE:mysql_query()在PHP5.5中被废弃，在PHP7中被删除。请使用PDO的mysqli库进行更新。

Answer 2

您可以构建increntale查询。

<code>
 if (isset($_POST['search'])) {
    $projectName     = $_POST['pName'];
    $clientId      = $_POST['s_by_clientName'];
    $departmentId  = $_POST['s_by_department'];
    $statusName  = $_POST['s_by_status'];
    $my_sql = "select * from project_list "; 
    $my_where = "";
    if (!empty($projectName)) 
    {
        if ($my_where = ""){
            $my_sql .= "where ";
        } else {
            $my_sql .= "and ";
        }
        $my_sql .= "projectName='$projectName'";

    }
    if (!empty($clientId)) 
    {

        if ($my_where = ""){
            $my_sql .= "where ";
        } else {
            $my_sql .= "and ";
        }
        $my_sql .=  "client_id='$clientId'";

    }
    if (!empty($departmentId)) 
    {
        if ($my_where = ""){
            $my_sql .= "where ";
        } else {
            $my_sql .= "and ";
        }
        $my_sql .=  "department_id='$departmentId'";


    }
    if (!empty($statusName)) 
    {
        if ($my_where = ""){
            $my_sql .= "where ";
        } else {
            $my_sql .= "and ";
        }
        $my_sql .=  "status='$statusName'";

    }
}

​

Answer 3

在这里，我使用列id作为primary key & auto-increment。根据您的列名更改它。

$query = "SELECT * FROM project_list WHERE id is not null";

码

<?
if (isset($_POST['search'])) {
    $projectName     = $_POST['pName'];
    $clientId      = $_POST['s_by_clientName'];
    $departmentId  = $_POST['s_by_department'];
    $statusName  = $_POST['s_by_status'];

    // Here I used coloumn 'id' as primary key & auto-increment. Change it as per your column name.
    $query = "SELECT * FROM project_list WHERE id is not null"

    if (!empty($projectName)) 
    {
        $query. = " AND projectName='".$projectName."'";
    }
    if (!empty($clientId)) 
    {
        $query. = " AND client_id='".$clientId."'";
    }
    if (!empty($departmentId)) 
    {
        $query. = " AND department_id='".$departmentId."'";
    }
    if (!empty($statusName)) 
    {
        $query. = " AND project_list='".$statusName."'";
    }

     $searchSql = mysql_query($query);
}