如何在WHERE子句中使用CASE WHEN？

Here is a part of my query:

这是我的查询的一部分：

WHERE CASE $range WHEN 'ALL' THEN TRUE
      ELSE $this->table_alias.date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 $range))
      END

Noted that $range is a php variable which contains a word. It throws this error message:

注意$ range是一个包含单词的php变量。它抛出此错误消息：

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALL WHEN 'ALL' THEN TRUE ELSE re.date_time > unix_timestam' at line 13 in C:\xampp\htdocs\myweb\others\users.php:120 Stack trace: #0 C:\xampp\htdocs\myweb\others\users.php(120): PDO->prepare('SELECT u.id use...') #1 C:\xampp\htdocs\myweb\others\questions.php(359): users->index(' AND categories...', ' INNER JOIN qan...', 'tagged') #2 C:\xampp\htdocs\myweb\others\users.php(26): questions->tagged('index') #3 C:\xampp\htdocs\myweb\application\other.php(24): users->index() #4 C:\xampp\htdocs\myweb\index.php(161): require_once('C:\xampp\htdocs...') #5 {main} thrown in C:\xampp\htdocs\myweb\others\users.php on line 120

致命错误：未捕获PDOException：SQLSTATE [42000]：语法错误或访问冲突：1064 SQL语法中有错误;查看与您的MariaDB服务器版本对应的手册，以便在C：\ xampp \ htdocs \ myweb \ others \ users.php中第13行的'ALL WHEN'ALL'那么正确're.date_time> unix_timestam'附近使用正确的语法：120堆栈跟踪：＃0 C：\ xampp \ htdocs \ myweb \ others \ users.php（120）：PDO-> prepare（'SELECT u.id use ...'）＃1 C：\ xampp \ htdocs \ myweb \ others \ questions.php（359）：users-> index（'AND categories ...'，'INNER JOIN qan ...'，'tagged'）＃2 C：\ xampp \ htdocs \ myweb \ others \ users.php（26）：questions-> tagged（'index'）＃3 C：\ xampp \ htdocs \ myweb \ application \ other.php（24）：users-> index（）＃4 C：\ xampp \ htdocs \ myweb \ index.php（161）：require_once（'C：\ xampp \ htdocs ...'）＃5 {main}在第120行的C：\ xampp \ htdocs \ myweb \ others \ users.php中抛出

Does anybody what's wrong?

有人怎么了？

1 个解决方案

#1

I think you used wrong syntax

我认为你使用了错误的语法

$sql = "WHERE CASE 
    WHEN '{$range}' = 'ALL' THEN 1=1
    ELSE {$this->table_alias}.date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 $range))
  END";