PHP MySQL getting overlapping dates compared to input date

Question

I need to get the overlapping date row from DB where my entered start and end dates overlaps with any of the rows in the DB.

Query:

SELECT * 
  FROM dates
 WHERE date_from >= $dateFrom 
   AND date_from >= $dateTill
    OR date_till >= $dateFrom 
   AND date_till <= $dateTill
    OR date_from <= $dateFrom 
   AND date_till >= $dateTill

Currently I have a row with start date: 2019-03-11 and end date 2019-03-17

And query skips it at the moment with these dates:

Beginning: 2019-03-07
End: 2019-03-16

Any ideas how can I improve the query?

Lots of things wrong. I'll start with this one 2019 minus 3 minus 7 equals 2009. — Strawberry, Jan 25 '19 at 08:08
Your `$dateFrom` and `$dateTill` variables should be enclosed in single quotes... — Nick, Jan 25 '19 at 08:10

score 0 · Accepted Answer · answered Jan 25 '19 at 08:26

0

Try this:

SELECT *
FROM dates
WHERE (date_from BETWEEN $dateFrom AND $dateTill)
   OR (date_till BETWEEN $dateFrom AND $dateTill)
   OR ($dateFrom BETWEEN date_from AND date_end)

You have to use parentheses when doing some complicated logical operations.

Also look how logical operators work: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Logical_Operators

answered Jan 25 '19 at 08:26

Waska Chaduneli

352
3
8

The logic for overlaps is very simple. Event A can be said to overlap Event B is Event A starts before Event B ends, and ends after Event B starts. Also, this kind of solution is wide open to sql injection, and without inverted commas, you're just comparing a date to an integer. So while not strictly wrong, there's really no sense in which the answer above is 'correct'. – Strawberry Jan 25 '19 at 10:29
@Strawberry Actually yes, you're correct. But I think that the question is about logic of comparing of dates not about security. – Waska Chaduneli Jan 27 '19 at 07:47

score 0 · Answer 2 · answered Jan 25 '19 at 09:17

0

select * FROM table_name WHERE (date_from BETWEEN $dateFrom AND $dateTill)  OR (date_till BETWEEN $dateFrom AND $dateTill) OR ($dateFrom BETWEEN date_from AND date_end)

I think its helpful for you.

answered Jan 25 '19 at 09:17

user1161960

90
1
9

PHP MySQL getting overlapping dates compared to input date

2 Answers2