php - 忽略 MYSQL 查询中的空值

Question

我有一个表单供用户输入一些信息。提交表单后，它应该使用用户输入的值查询数据库。

我的问题是，如果用户输入的某些值是空的，它应该从查询中删除。

这是我的代码:

if(isset($_POST['submit']))
{
include("../includes/header.php");
include ("../scripts/db/connect.php");

//Gets variables from $_POST
$negocio = $_POST['negocio'];
$imovel = $_POST['imovel'];
$distrito = $_POST['distrito'];
$concelho = $_POST['concelho'];
$freguesia = $_POST['freguesia'];

$query = "SELECT * FROM imoveis WHERE negocio = $negocio and imovel = $imovel and distrito = $distrito and concelho = $concelho and freguesia = $freguesia";
}

想象一下，如果 $negocio、$imovel、$concelho 和 $freguesia 等于 null，查询应该是:

$query = "SELECT * FROM imoveis WHERE distrito = $distrito;

我该怎么做？

Answer 1

Generate your query string dynamcilly depending on which value are set or not null, and than use that query

在一个单独的文件中运行这段代码，在删除或添加任何变量注释($name、$street、$address 或 $qualification )之后，您就会明白这一点

// you will see query will change depending on the set variable,
//I am using these name you can use any name for your variable

$name='my name';
//$address='some where on earth';
$street='this is my street';
//$qualification='i am very much qualified';

//now create the array only with the values which are not empty or not nul,
//I am using empty you can use null if you want with this example you can use any thing.
if(!empty($name)) $query_string_second_part[]=" AND name = '$name'";
if(!empty($address)) $query_string_second_part[]=" AND address = '$address'";
if(!empty($street)) $query_string_second_part[]=" AND street = '$street'";
if(!empty($qualification)) $query_string_second_part[]=" AND qualification = '$qualification'";

//hand type the first part for the query
$query_string_First_Part= "SELECT * FROM myTableName WHERE";
//Implode the array, if you want to see how it look like use echo,
$query_string_second_part= implode(" ", $query_string_second_part);
//as you can see we are adding AND with every value, so we need to remove the first AND
//with one space
//Make sure you give space in the second parameter. else it wont work means "" not correct but " " is correct
//Hint --> use one space in between the double qoutes
$query_string_second_part=  preg_replace("/AND/", " ", $query_string_second_part, 1);

//Join the first and second part together to create a full query
$query_string=$query_string_First_Part.$query_string_second_part;
echo ($query_string);//see how our query look like at the moment