在 mySQL 中构建动态 WHERE 子句 [英] Build dynamic WHERE clause in mySQL

问题描述

如果我只想按办公室名称进行搜索，我有这个代码并且效果很好.但是，我需要能够通过办公室和/或名字和/或姓氏"(三者的任意组合)进行搜索.

I have this code and it works great, if I just want to search by office name. However I need to be able to search by "Office and/or First Name and/or Last Name", any combination of the three.

$firstName      = $_POST["firstName"];
$lastName       = $_POST["lastName"];
$officeName     = $_POST ["officeName"];

$query = "SELECT 
e.*,
e.id emp_id,
o.*
";
$query .= "FROM 
employee_data e,
office o,
employee_office_pivot p
"; 
$query .= "WHERE 
1=1
AND e.id=p.employee_id
AND p.office_id=o.id
AND o.office_name= '".$officeName."'
";

如何构建 WHERE 子句，使其接受三列中的任何一列，如果它们为空，则不接受.

How can I build the WHERE clause, so that it will accept any of the three columns, or none if they are null.

谢谢，理查德

推荐答案

类似的事情?

$query .= "WHERE 
    1=1
    AND e.id=p.employee_id
    AND p.office_id=o.id
    AND (o.office_name= '".mysqli_real_escape_string($officeName)."'
        OR o.office_name= '".mysqli_real_escape_string($firstName)."'
        OR o.office_name= '".mysqli_real_escape_string($lastName)."')
    ";

我在这里以 mysqli_real_escape_string() 为例，您应该使用正确且必要的预防措施来避免系统中的 SQL 注入.

I used mysqli_real_escape_string() here as an example, you should use the correct and necessary precautions to avoid SQL injection in your system.

这篇关于在 mySQL 中构建动态 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！