select two record containing dates in it with two input dates

Question

Simply I have Two Input box

1. Start_date
2. End_date

This is my database table

| id | category | start_date | end_date   |
| 1  | gold     | 2016-05-24 | 2016-05-31 |
| 2  | silver   | 2016-05-16 | 2016-05-23 |

Now When I insert date like below:

start_date = 2016-05-25
end_date = 2016-05-27 

I want to find out category between imputed date in input box.

I use this select query:

SELECT category 
FROM tablename
WHERE '$start_date','$end_date' BETWEEN start_date AND end_date; 

But I don't get any result. I know that if you use single date after where I get result but I want to search with two dates

Answer 1

Simple answer:

If you want both date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
  AND 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the date columns to be between the input dates:

SELECT category 
FROM tablename 
WHERE 'start_date' BETWEEN start_date AND end_date
   OR 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the ranges that are "overlapping with the range of the input dates
(seems the most likely, based on the wanted result):

SELECT category 
FROM tablename 
WHERE 'start_date' <= end_date
  AND start_date <= 'end_date' ;

Answer 2

Most probably table row interval must be within interval defined by user input ('$start_date', '$end_date'). So

SELECT category 
FROM tablename t 
WHERE t.start_date BETWEEN '$start_date' AND '$end_date'  
    AND t.end_date BETWEEN '$start_date' AND '$end_date'; 

Answer 3

You have to use below query for your output.

SELECT category 
 FROM table_1 
   WHERE start_date BETWEEN '2016-05-16' AND '2016-05-31'
    OR end_date BETWEEN '2016-05-16' AND '2016-05-31'