有没有一种方法可以根据数据库中的数据只执行WHERE语句的一部分

Question

有没有一种方法可以只执行WHERE语句的一部分，也许可以使用基于数据库中数据的某种IF-THEN语句？

以下代码在大多数情况下正确显示特定日期的计划任务，但存在异常。如果有人创建了一个任务，然后将开始日期设置在创建日期之前，则即使该任务已安排在当天，该任务也不会显示。

在本例中，如果未设置开始日期(即TaskStartDate = '' OR TaskStartDate is NULL'(，我们只希望使用创建日期(即，TaskCreated <= '" . $DisplayDateTime . "'"(。如果设置了TaskStartDate，则我们希望使用该日期而不是日期范围的创建日期。

// Displays tasks that are scheduled on a particular date or marked as "focus"
$sql = "SELECT * FROM Reminders";
$sql = $sql . " " . "WHERE TaskUserID = " . $loggedinuser;
$sql = $sql . " " . "AND ( TaskFocus = 1";
$sql = $sql . " " . "OR ( TaskCreated <= '" . $DisplayDateTime . "'";
$sql = $sql . " " . "AND ( TaskStartDate <= '" . $DisplayDate . "' OR TaskStartDate = '' OR TaskStartDate is NULL )";
$sql = $sql . " " . "AND ( TaskPauseDate <= '" . $DisplayDate . "' OR TaskPauseDate = '' OR TaskPauseDate is NULL )";
$sql = $sql . " " . "AND ( TaskSchedDate >= '" . $DisplayDate . "' AND TaskSchedDate is NOT NULL )" ;
$sql = $sql . " " . "AND ( TaskDueDate <> '" . $DisplayDate . "'  OR TaskDueDate = '' OR TaskDueDate is NULL )";                            
$sql = $sql . " " . ") )";
$sql = $sql . " " . "AND ( TaskPauseDate <= '" . $DisplayDate . "' OR TaskPauseDate = '' OR TaskPauseDate is NULL )";
$sql = $sql . " " . "ORDER BY TaskFocus DESC, TaskPriority ASC, TaskSchedDate ASC, TaskDueDate ASC";

SQL:

SELECT * FROM Reminders 
WHERE TaskUserID = 1 
AND ( ( TaskCreated <= '2020-12-03 23:59:59' 
AND ( TaskStartDate <= '2020-12-03' OR TaskStartDate = '' OR TaskStartDate is NULL ) 
AND ( TaskPauseDate <= '2020-12-03' OR TaskPauseDate = '' OR TaskPauseDate is NULL ) 
AND ( TaskSchedDate >= '2020-12-03' AND TaskSchedDate is NOT NULL ) 
AND ( TaskDueDate >= '2020-12-03' OR TaskDueDate = '' OR TaskDueDate is NULL ) ) ) 
AND ( TaskPauseDate <= '2020-12-03' OR TaskPauseDate = '' OR TaskPauseDate is NULL ) 
ORDER BY TaskFocus DESC, TaskPriority ASC, TaskSchedDate ASC, TaskDueDate ASC

修复这种异常的最佳方法是什么？

Answer 1

您可以尝试更换TaskCreated <= '" . $DisplayDateTime . "'"

带有

((TaskCreated <= '" . $DisplayDateTime . "') OR (TaskStartDate = '' OR TaskStartDate is NULL'))"

Answer 2

PHP HEREDOC字符串中的sql。(更易于阅读(

$sql = <<<WXC
SELECT * FROM Reminders 
WHERE    TaskUserID = $loggedinuser
AND (   (( TaskStartDate <= '{$DisplayDate}' and (TaskStartDate <> '' AND TaskStartDate is NOT NULL ))
OR  (TaskCreated <= '{$DisplayDateTime}' AND ( TaskStartDate is NULL OR TaskStartDate = '' )  ))
AND ( TaskPauseDate <= '{$DisplayDate}' OR TaskPauseDate = '' OR TaskPauseDate is NULL )
AND ( TaskSchedDate >= '{$DisplayDate}' AND TaskSchedDate is NOT NULL )
AND ( TaskDueDate <> '{$DisplayDate}'  OR TaskDueDate = '' OR TaskDueDate is NULL )  
AND ( TaskPauseDate <= '{$DisplayDate}' OR TaskPauseDate = '' OR TaskPauseDate is NULL ))
ORDER BY TaskFocus DESC, TaskPriority ASC, TaskSchedDate ASC, TaskDueDate ASC
WXC;
//

Answer 3

查看SQL 中的CASE语句

Answer 4

为了使其按预期工作，您需要更改：

$sql = $sql . " " . "AND ( TaskCreated <= '" . $DisplayDateTime . "'";

到此：

$sql = $sql . " " . "AND ( ( TaskCreated <= '" . $DisplayDateTime . "'";    
$sql = $sql . " " . "OR ( TaskStartDate <= '" . $DisplayDate . "' OR TaskStartDate = '' OR TaskStartDate is NULL ) )";

这将显示在特定日期创建或在特定日期开始的任务，并显示没有开始日期的任务。

SQL:

SELECT * FROM chReminders 
WHERE TaskUserID = 1 
AND ( ( ( TaskCreated <= '2020-12-03 23:59:59' 
OR ( TaskStartDate <= '2020-12-03' OR TaskStartDate = '' OR TaskStartDate is NULL ) ) 
AND ( TaskStartDate <= '2020-12-03' OR TaskStartDate = '' OR TaskStartDate is NULL ) 
AND ( TaskPauseDate <= '2020-12-03' OR TaskPauseDate = '' OR TaskPauseDate is NULL ) 
AND ( TaskSchedDate >= '2020-12-03' AND TaskSchedDate is NOT NULL ) 
AND ( TaskDueDate >= '2020-12-03' OR TaskDueDate = '' OR TaskDueDate is NULL ) ) ) 
AND ( TaskPauseDate <= '2020-12-03' OR TaskPauseDate = '' OR TaskPauseDate is NULL ) 
ORDER BY TaskFocus DESC, TaskPriority ASC, TaskSchedDate ASC, TaskDueDate ASC