如何在WHERE子句中为SQL SELECT查询使用变量 [英] How to use variables in WHERE clause for a SQL SELECT query

问题描述

我的SQL SELECT查询有3个由HTML表单设置的变量，但这些变量可以为null：

My SQL SELECT query has 3 variables set by a HTML form, however these variables can be null:

   $suburb = (isset($_POST['suburb'])) ? $_POST['suburb'] : '';
   $postcode = (isset($_POST['postcode'])) ? $_POST['postcode'] : '';
   $state = (isset($_POST['state'])) ? $_POST['state'] : '';

当所有3个变量都以表单输入时，SELECT查询处理得很好（由于WHERE子句）。但是，当一个或多个变量为空时，SELECT查询错误，因为它正在数据库中查找NULL值。

When all 3 variables are entered in the form the SELECT query processes fine (due to the AND's in the WHERE clause). But when one or more variables are blank, the SELECT query errors as it is looking for a NULL value in the databse.

我想要的是当变量是空，我不想为它的where子句

What I want it to do is when a variable is null, I don't want a where clause for it

查询：

$sql = <<<SQL
SELECT 
    cs.customerRefId,
    cc.firstName,
    cc.lastName,
    cc.email,
    cc.phone,
    cc.mobile,
    cs.seekingAddressSuburb, 
    cs.seekingAddressPostcode, 
    cs.seekingAddressState
FROM 
    customer_seeking cs
LEFT JOIN
    customer_contact cc ON cc.customerRefId = cs.customerRefId
WHERE
cs.seekingAddressSuburb = '$suburb' AND
cs.seekingAddressPostcode = '$postcode' AND
cs.seekingAddressState = '$state'
SQL;  


if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

For example if only the state variable is defined (cs.seekingAddressSuburb = '$suburb' AND cs.seekingAddressPostcode = '$postcode' AND are removed from the WHERE clause):

$sql = <<<SQL
SELECT 
    cs.customerRefId,
    cc.firstName,
    cc.lastName,
    cc.email,
    cc.phone,
    cc.mobile,
    cs.seekingAddressSuburb, 
    cs.seekingAddressPostcode, 
    cs.seekingAddressState
FROM 
    customer_seeking cs
LEFT JOIN
    customer_contact cc ON cc.customerRefId = cs.customerRefId
WHERE
cs.seekingAddressState = '$state'
SQL;  

推荐答案

在String中构造查询，然后逐个添加如果每个领域都有价值。例如：

construct your query in a String then add one by one if each field have value. example :

$query = "select * from table1 where 1=1";
if(!empty($suburb)) $query.=" and cs.seekingAddressSuburb = '$suburb'";
if(!empty($postcode)) $query.=" and cs.seekingAddressPostcode = '$postcode'";
if(!empty($state)) $query.=" and cs.seekingAddressState = '$state'";
//run your query then

这篇关于如何在WHERE子句中为SQL SELECT查询使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！