如何在php预备查询中where子句后面的sql case子句中使用=，，>=运算符？ [已关闭]

Question

我不能在WHERE子句后面使用CASE子句。 代码:已编辑

$filterQuery = "SELECT * FROM mediainfo
WHERE (CASE
        WHEN year <> 1 AND imdb = 1 AND genre = 1 THEN year >= ?                              /* withoutGenreImdb */
        WHEN year = 1 AND imdb <> 1 AND genre = 1 THEN imdb >= ?                              /* withoutGenreYear */
        WHEN year <> 1 AND imdb <> 1 AND genre = 1 THEN year >= ? AND imdb >= ?               /* withoutGenre */
        WHEN year <> 1 AND imdb <> 1 AND genre <> 1 THEN year >= ? imdb >= ? AND genre LIKE ? /* withGenre */
        WHEN year = 1 AND imdb <> 1 AND genre <> 1 THEN imdb >= ? AND genre LIKE ?            /* withoutYear */
        WHEN year <> 1 AND imdb = 1 AND genre <> 1 THEN year >= ? AND genre LIKE ?            /* withoutImdb */
      END)
LIMIT 84";

    if ($stmt = $conn->prepare($filterQuery)) {
        $stmt->bind_param("sssssssssss",$year,$rating,$year,$rating,$year,$rating,$genre,$rating,$genre,$year,$genre);
        $stmt->execute();
    }else { echo "false"; }

总是显示为false。

Answer 1

当THEN或ELSE关键字后面有条件语句，而不是某个布尔表达式时，不可能在case.。中使用比较。

因此，最好通过正确使用括号来评估带有和/或连词的语句。 在这种情况下，将THEN关键字替换为AND关键字，并将那些带括号的术语与OR关键字组合将非常适合，例如

SELECT *
  FROM mediainfo
 WHERE ( year <> 1 AND imdb <> 1 AND genre =  1 AND year >= ? AND imdb >= ? )
    OR ( year <> 1 AND imdb <> 1 AND genre <> 1 AND year >= ? imdb >= ? AND genre LIKE ? )                  
    OR ( year <> 1 AND imdb =  1 AND genre =  1 AND year >= ? )        
    OR ( year <> 1 AND imdb =  1 AND genre <> 1 AND year >= ? AND genre LIKE ? )
    OR ( year =  1 AND imdb <> 1 AND genre =  1 AND imdb >= ? )           
    OR ( year =  1 AND imdb <> 1 AND genre <> 1 AND imdb >= ? AND genre LIKE ? ) 
 LIMIT 84