How to use CASE WHEN in the WHERE clause?

Question

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:

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

Does anybody what's wrong?

Perhaps pursue this on the already open question? stackoverflow.com/questions/45468750/… — ficuscr
– ficuscr, Commented Aug 2, 2017 at 19:43
$range is not quoted and is comparing to 'ALL'. There is most likely no column named ALL in the table. — T Gray
– T Gray, Commented Aug 2, 2017 at 19:45
What is $range? that supposed to be PHP variable? Doesn't seem to be a MySQL variable. T Gray, he is trying to do a string to string comparison. — ficuscr
– ficuscr, Commented Aug 2, 2017 at 19:45
That case statement doesn't make sense.. What's wrong with the answer by @ficuscr on your other question? — Aaron Dietz
– Aaron Dietz, Commented Aug 2, 2017 at 19:47
@ficuscr As I've mentioned in the question $range is a PHP variable. So the final query looks like WHERE CASE ALL WHEN ALL THEN .... — stack
– stack, Commented Aug 2, 2017 at 19:51

ficuscr · Accepted Answer · 2017-08-02 19:57:57Z

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";

edited Aug 2, 2017 at 19:57

ficuscr

7,0932 gold badges35 silver badges54 bronze badges

answered Aug 2, 2017 at 19:49

vjy tiwari

8611 gold badge5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ficuscr Over a year ago

What is $range?... $sql = "WHERE CASE WHEN {$range}... maybe... note PHP tags and see previous question in this series.

vjy tiwari Over a year ago

i think , it is php variable

ficuscr Over a year ago

Agreed. Not sure that is understood though.

Paul Spiegel Over a year ago

Thsis syntax has the same problem.

ficuscr Over a year ago

Cleaned it up some.

|

Collectives™ on Stack Overflow

How to use CASE WHEN in the WHERE clause?

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related