CodeIgniter - SQL sentence IF then WHERE?-CodePudding

Currently I am capable of doing WHERE sentences in CodeIgniter as follows:

$builder = $db->table('mytable');
$builder->select(*);
$builder->where('age >='.$params['some-param']);

What I need to do, is a WHERE statement that only applies if an IF statement is true. Something like:

(IF gender = 'Male', apply these rows the following where) $builder->where('age >='.$params['some-param']);
(IF gender = 'Female', apply these other rows, this where and not the first one) $builder->where('age >='.$params['some-other-param']);

Also note that ['some-param'] and ['some-other-param'] won't always exist. I get that it can be achieved with OR sentence but if ['some-other-param'] is not setted, then the query would be something like:

$builder->where("age >=".$params['some-param']." AND gender = 'Male'");

And that would not include any row with gender = 'Female', which I also need.

I have no clue of how to achieve this. Any orientation will be appreciated.

CodePudding user response：

Wouldn't something like this work?

-- Both age filters are set
select *
from my_table
where (
    (gender = 'Male' and age >= 20)
    or 
    (gender = 'Female' and age >= 30)
)

-- Only Male filter is set
select *
from my_table
where (
    (gender = 'Male' and age >= 20)
    or 
    gender = 'Female'
)

-- Only Female filter is set
select *
from my_table
where (
    (gender = 'Female' and age >= 20)
    or 
    gender = 'Male'
)