IF ELSE / CASE in where clause?

Databases
1

hi guys,
I need some advice here.
I have a page that allow user to filter result by status and by a range of date as below.

and here is the query,

select * from applications
where  dt_apply >= $_POST[start]
and  dt_apply <= $_POST[end]
and status = $_POST[status]
order by id

problem with my query is, if i didn’t choose the status the result won’t come out, i think is because of I’m using AND there.
So I want that if user did not select the status, data will display based on the start & end date only

meaning

if status is not null
then
select * from applications where dt_apply >= $_POST[start] and dt_apply <= $_POST[end] and status = $_POST[status]
else
select * from applications where dt_apply >= $_POST[start] and dt_apply <= $_POST[end]

I tried to use case / if else but failed, i’m not sure whether it can be use in where clause…?

so how i want to remove ‘and status = $_POST[status]’ when status is null?

Thank You.
I’m not so good in English, so if you don’t understand my grammar feel free to ask if you need more explanation.

2

this would be done with php, not sql

<?php
 $sql = "select * from applications where dt_apply >= $_POST[start] and dt_apply <= $_POST[end]"
 if ( $_POST[status] ) {
    $sql = $sql . " and status = $_POST[status]"
    }
 exec ( $sql )
 ?>

please excuse me if this doesn’t work, because i don’t know php, but i hope you get the idea

3

If status is a checkbox or radio, I think your suggestion will work.

If status is a select or text/textarea, then I recommend the following:

[COLOR=#000000] [COLOR=#0000BB]<?php
 $sql [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"select * from applications where dt_apply >= [/COLOR][COLOR=#0000BB]$_POST[/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]start[/COLOR][COLOR=#007700]][/COLOR][COLOR=#DD0000] and dt_apply <= [/COLOR][COLOR=#0000BB]$_POST[/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]end[/COLOR][COLOR=#007700]][/COLOR][COLOR=#DD0000]"
 [/COLOR][COLOR=#007700]if ( trim([/COLOR][COLOR=#0000BB]$_POST[/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]status[/COLOR][COLOR=#007700]]) <> "" ) {
    [/COLOR][COLOR=#0000BB]$sql [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]$sql [/COLOR][COLOR=#007700]. [/COLOR][COLOR=#DD0000]" and status = [/COLOR][COLOR=#0000BB]$_POST[/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]status[/COLOR][COLOR=#007700]][/COLOR][COLOR=#DD0000]"
    [/COLOR][COLOR=#007700]}
 [/COLOR][COLOR=#0000BB]exec [/COLOR][COLOR=#007700]( [/COLOR][COLOR=#0000BB]$sql [/COLOR][COLOR=#007700])
 [/COLOR][COLOR=#0000BB]?>[/COLOR] [/COLOR]

Trim the value (so the user can’t just enter spaces to bypass) and check for blank (or even length) in the conditional.

HTH,

:slight_smile:

4

thanks guys for the replies…

but i really need it to done by using sql :frowning:
if only i can solve it using code…

is it can not be done using sql? even using advance query?
i really appreciate it if someone with powerful knowledge in sql confirm this.
if it’s still can’t, it’s look like i need to figure out something else.

thank you.

5

okay, please test this (both ways, with a status selected and without) and let me know what happens –

SELECT * 
  FROM applications  
 WHERE dt_apply >= $_POST[start] 
   AND dt_apply <= $_POST[end] 
   AND status = 
       CASE WHEN '$_POST[status]' = ''
            THEN status
            ELSE $_POST[status] END 
ORDER 
    BY id
6

You should be using Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle. The golden rule is to never let any user submitted data anywhere near the database without sanitizing it and escaping (or using prepared statements - which is the preferable way).

7

@SpacePhoenix
thank you for the advice :slight_smile:

@ r937
i test it already, but when status not selected, it displays an error below.

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 ‘)
a limit 1000’ at line 8select * from (SELECT * FROM applications WHERE dt_apply>= '09-06-2014’and dt_apply <='16-06-2014’AND status = CASE WHEN ‘’ = ‘’ THEN status ELSE END )

8

okay i tried to change a little bit

select * from (
   select case '$_POST[status]'
	 when  ''
	 then
		 (SELECT * FROM ebantuan.`cidb_tra_bantuan` as a
		 WHERE dt_apply &gt;= '$_POST[start]'
		 and dt_apply &lt;='$_POST[end]')
	 else
		 (SELECT * FROM ebantuan.`cidb_tra_bantuan` as a
		 WHERE dt_apply &gt;= '$_POST[start]'
		 and dt_apply &lt;='$_POST[end]'
		 and status = '$_POST[status]' )
	 end
    )as apply

and it gave me this error

#1241 - Operand should contain 1 column(s)

how can I select more than one column in subquery?

9

you cannot select more than one column for the value to be used in a CASE expression

you really should re-consider doing this with php as i originally suggested

also, your dates are not going to work because mysql recognizes only the yyyy-mm-dd format – another reason to get into your php, to reformat the values coming from the web form

10

Thanks r937 for your help…

You right… this thing is so much easier if i use php.
actually, I’m using my company’s own development tools, and it has it’s own limitation.
so I’m gonna issue this to the development team.
hope they can fix this flaw.

11