How to use if else in mysql where clause

Question

I want to query data in mysql like:

$cid=$_POST["cid"];
$room_id=$_POST["room_id"];
$time=$_POST["time"];

$reuslt=$this->db-query("SELECT * FROM mytable WHERE if($cid is not null){then check cid=$cid} else{don't check} AND if($room_id is not null){then check room_id=$room_id} else{don't check} AND if($time is not null){then check time=$time} else{don't check}");

How could I writ the correct sql to query data?

Construct the SQL string using the conditions, and then use the constructed query: `$this->db-query(constructed-SQL-string) `. — Web User, Dec 15 '15 at 04:16
Did a Google search for "mysql if query" really not turn up anything? — miken32, Dec 15 '15 at 04:18
Possible duplicate of [Using an IF Statement in a MySQL SELECT query](http://stackoverflow.com/questions/8444570/using-an-if-statement-in-a-mysql-select-query) — miken32, Dec 15 '15 at 04:19
@miken32 That's a different problem, though it is related to CASE .. WHEN — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 15 '15 at 04:29
@miken32 Looks like this problem is not even related to CASE .. WHEN. See Daniel Brose's answer. — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 15 '15 at 04:35
@TareqMahmood I'm sure "how to use an IF in a query" is a duplicate, but you're correct I picked the wrong one. — miken32, Dec 15 '15 at 04:39
@miken32 If you read and understand Daniel's answer properly, you will know, it's not even related to using IF in a MySQL query. — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 15 '15 at 04:41
@TareqMahmood If you read and understand my answer properly, it can be done perfectly well with an SQL query, which was the original question! — miken32, Dec 15 '15 at 04:43
@miken32 Finally I got one same http://stackoverflow.com/questions/15162404/if-else-on-where-clause — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 15 '15 at 05:07

score 1 · Accepted Answer · edited May 23 '17 at 11:44

So if you wanted to use an SQL if|else statement, look at something like Using an IF Statement in a MySQL SELECT query, or If else on WHERE clause.

Some other sql answers here look interesting too. However i would caution against overcomplicating.

For this OP its easier to prepare statement you send to DB, rather than overthink it and risk performance drop if massive dataset and poor indexes.

So, if this is more relevant as a php if|else, and you want to keep it as a single line (as opposed to concatenating a string over multiple statements), i would recommend ternary syntax

https://davidwalsh.name/php-shorthand-if-else-ternary-operators

For example, something like this should work:

$reuslt= $this->db-query("SELECT * FROM mytable WHERE TRUE " 
   . ( ($cid is not null) ? " AND cid='".$cid."'" : "" ) 
   . ( ($room_id != null) ? " AND room_id='".$room_id."'" : "" ) 
   . ( ($time != null) ? " AND  time='" . $time . "'" : "" );

The WHERE TRUE is just to make easier to print, though it really would be easier to just create a $sql string variable and prepare the statement over seperate if() statements.

Though i would also stress the need for escaping these values, as you should with all user input.

http://php.net/manual/en/mysqli.real-escape-string.php

btw - i kept mine simple php, even included his typos, but its obvious codeigniter and as i mention seperate lines also easier, so something like @yogendrayaduvanshi might be more perfered - mine is really just a starting point to show you how ternary can be used with just a little trick (where true). Some of the other sql 'case; and 'if' answers are also interesting, but for the use case of this OP just prepare statement before sending to DB, no need for extra complications or possible performance drop if massive dataset and poor indexes. — Daniel Brose, Dec 17 '15 at 00:10

miken32 · Answer 2 · 2016-07-04T21:24:22.507

1

You can use 1=1 as a placeholder in an IF function in a WHERE clause:

$query = "SELECT * FROM mytable
    WHERE IF('$cid' != '', cid='$cid', 1=1)
    AND IF('$room_id' != '', room_id='$room_id', 1=1)
    AND IF('$time' != '', time='$time', 1=1)"
$result=$this->db->query($query);

I don't know how your framework handles it, but this code is seriously vulnerable to SQL injection and you should be using prepared queries with parameterization.

edited Jul 04 '16 at 21:24

answered Dec 15 '15 at 04:38

miken32

42,008
16
111
154

I guess you wanted to write something like the answer here http://stackoverflow.com/questions/15162404/if-else-on-where-clause. (and you are true that it's a duplicate). – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 15 '15 at 05:08
Pls correct your first argument inside `IF` function like `'$cid' != ''`. And I am going to give you +1 for teaching us the alternative (if corrected). – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 15 '15 at 05:44
@TᴀʀᴇǫMᴀʜᴍᴏᴏᴅ you're correct, I mistyped that. Maybe I was thinking of PHP loose typing! – miken32 Jul 04 '16 at 21:24

Andriy Ivaneyko · Answer 3 · 2015-12-15T06:13:39.503

0

SQL Statement below would perform action you are going to make:

SELECT * FROM mytable WHERE (('$cid' is not null) AND (cid='$cid')) 
                      AND (('$room_id' is not null)AND(room_id='$room_id'))
                      AND (('$time' is not null) AND (time='$time'))

edited Dec 15 '15 at 06:13

answered Dec 15 '15 at 04:25

Andriy Ivaneyko

20,639
6
60
82

1

1) You need single quotes around `'$cid'` and such others, otherwise it will be considered as a fieldname 2) Probably the value coming from the inputs should not be `null` it can be `''` instead, and in that case, your condition will fail. – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 15 '15 at 05:04
would it perform that action??? Im reading it as all parameters have to not be null, else a condition would fail and no results returned. Compare to mine, or others like @Praveen, where the conditions check for null before checking equality, and wont break if ANY is null. The OP states that for all values, 'only' if they are not null should they be checked, where yours would fail if any were null. – Daniel Brose Dec 17 '15 at 00:06

score 0 · Answer 4 · answered Dec 15 '15 at 04:30

You can do this by case statement:

SELECT * 
FROM mytable 
WHERE 
    (cid = case when $cid is not null then $cid else cid end) AND 
    (room_id = case when $room_id is not null then $room_id else room_id end) AND 
    (time = case when $time is not null then $time else time end)

score 0 · Answer 5 · answered Dec 15 '15 at 04:32

This is how i do in my case and its working ..() In model

public function get($table,$column='*',$where='',$start='',$limit='',$seach_field='', $seach_keyword='', $seach_type='',$orderby='',$order_type='DESC')
    {
        $this->db->select($column); 
        $this->db->from($table);
        if($where !='')
        {
            $this->db->where($where);   
        }
        if($seach_field !='' && $seach_keyword !='' && $seach_type!='')
        {
            $this -> db -> like($seach_field, $seach_keyword, $seach_type);
        }
        if($orderby!='')
        {
            $this->db->order_by($orderby,$order_type);
        }
        if($limit!='')
        {
            $this->db->limit($limit, $start);
        }
        $que = $this->db->get();

        return $que->result_array();        
    }

How to use if else in mysql where clause

5 Answers5