Sukrit Jaie Sukrit Jaie - 29 days ago 5
SQL Question

SQL query to display records in various scenarios

I have a form which displays data from my table. The form has a field for account id and start date and end date. I want to have multiple scenarios being displayed and have explained them below.

  1. If ONLY the account id is selected, then all the rows pertaining to that account id should be displayed.

  2. If ONLY the start and end dates are selected, then all the rows within that range should be displayed.

  3. If all 3 fields(account id, start and end date) are selected, then the display the particular account ids within the specified date range.

  4. If none of the fields are selected, then display the whole table.

I am able to figure out scenarios 1 and 2 but just can't get 3 & 4 to work. Below is my query I have used:

SELECT * FROM tbl_call_log_detail WHERE dealer_id = '$call_id' OR time_stamp <= '$endDate' AND time_stamp >= '$startDate' ORDER BY id ASC

I even tried putting parenthesis in the OR clause but that does nothing either.

Answer Source

I assume that if not selected, then its value is an empty string.

    SELECT * FROM tbl_call_log_detail 
     (dealer_id = '$call_id' AND '$endDate'='1970-01-01' AND '$startDate' ='1970-01-01')
  OR ( time_stamp <= '$endDate' AND time_stamp >= '$startDate'  
                    AND (dealer_id = '$call_id' OR'$call_id'='' ))    
  OR ('$endDate'='1970-01-01' AND '$startDate' ='1970-01-01'  AND '$call_id'='')
     ORDER BY id ASC