按多个字段搜索.有时是一个字段，有时是多个字段 - search by multiple field. sometimes by one field and sometimes more than one field

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

本文关键字：字段一个搜索 | 更新日期: 2023-09-27

我有搜索表单。在这里有多个字段。有时我会使用一个字段来表单提交，有时使用两个字段值，有时使用多个字段值来表单提交。

     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'");
        }
    }

这些查询仅用于按单个字段进行搜索。如何生成按一个或多个字段值执行搜索的查询有可能吗？？

在查询变量中使用连接

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

注意：mysql_query（）在PHP 5.5中已被弃用，在PHP 7中已被删除。请更新以使用PDO的mysqli库。

您可以构建增量查询

<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'";
    }
}

这里我使用列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);
}