1

I am trying to use multiple search options where user can select multiple options to filter records.

When user don't select filter option(s), by default all records should be returned.

Here's my SQL query :

$query = 
    sprintf("SELECT * FROM users_leave_request 
        where leave_from >= '$leave_from' 
        AND leave_to <= '$leave_to' 
        AND leave_status = '$leave_stat' 
        AND user_id = '$emp_id'");

    $result=$this->db->exec($query);

What I intend to do is that:

  1. Suppose $leave_stat parameter is empty, then records for all leave_stat values should be returned.
  2. Similarly if $emp_id is empty, records for all users should be returned.

It's somewhat like disabling that *extra AND* where condition when parameter is empty.

Can I do this with a single query or do I have to use separate queries for that? Any help is very much appreciated. Thanks.

TarangP
  • 2,711
  • 5
  • 20
  • 41
Azima
  • 3,835
  • 15
  • 49
  • 95
  • 1
    Build the query dynamically. Just add any condition if you got a value. You should also look into using parameterized [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of concatenating your queries. – M. Eriksson Mar 07 '18 at 06:13

2 Answers2

4

You can check the filter condition before the query, like this

$whrcondn="";

$whrcondn.=($leave_from)?" and leave_from > = '$leave_from'":"";

$whrcondn.=($leave_to)?" and leave_to < = '$leave_to'":"";

$whrcondn.=($leave_status)?" and leave_status  = '$leave_stat'":"";

$whrcondn.=($emp_id)?" and user_id ='$emp_id'":"";

$query =  sprintf("select * from users_leave_request where 1=1 $whrcondn");
Vijaya Vignesh Kumar
  • 444
  • 3
  • 14
1

look at this simple way add this condition

if(!empty($leave_stat))
 {$x='leave_status';}
else
 {$x='leave_status!';}

if(!empty($leave_stat))
 {$y='user_id';}
else
 {$y='user_id!';}

then change leave_status and user_id by $x and $y like this :

 $query = sprintf("SELECT * FROM users_leave_request 
    where leave_from >= '$leave_from' 
    AND leave_to <= '$leave_to' 
    AND '$x' = '$leave_stat' 
    AND '$y' = '$emp_id'");

$result=$this->db->exec($query);

and good luck

Lechani Houssam
  • 1
  • 2