codeigniter select records from date range without repeating a record

Question

Im working on a project that needs the following query but i couldn't get it done.

DATABASE: evenstdb

--------------------------------------
| Event_id | startDate |    endDate  |
--------------------------------------
|82637     |01-02-2020 |  02-03-2020 |
--------------------------------------
|66363     |10-03-2020 |  31-11-2020 |
--------------------------------------

i have a page that i should list the event according to the selected date range. everything works fine but the problem is that sometimes i get the same event listed twice because I'm listing the events according to the startDate and the endDate at the same time. sometimes the start and the end date are in the selected date range. How can i display an event only once?

my code:

// $date1 and $date2 are date inputs from the form 

$query = $this->db->where('startDate >=', $date1);
$query = $this->db->where('startDate <=', $date2);
$query = $this->db->get('evenstdb');
$results1 =  $query->result();

$query = $this->db->where('endDate >=', $date1);
$query = $this->db->where('endDate <=', $date2);
$query = $this->db->get('evenstdb');
$results2 =  $query->result();

$data["eventsList"] = array_merge($results1,$results2);

i'm not sure about the syntax cause im very new to codeigniter can you show me how pleae? — Mumin Gazi, Jul 21 '20 at 12:21

score 0 · Accepted Answer · answered Jul 21 '20 at 14:22

If each record should match two date ranges simultaneously, you can try to get needed records via one query with all 4 conditions:

$this->db->where('startDate >=', $date1)
         ->where('startDate <=', $date2)
         ->where('endDate >=', $date1)
         ->where('endDate <=', $date2);

$result = $this->db->get('evenstdb')->result();

If each record should match at least one date range, you can also try to get needed records via one query but group conditions into two parts connected with OR (record will be returned if any of these two groups returns true):

$this->db->group_start()
             ->where('startDate >=', $date1)
             ->where('startDate <=', $date2)
                 ->or_group_start()
                     ->where('endDate >=', $date1)
                     ->where('endDate <=', $date2)
                 ->group_end()
         ->group_end();

$result = $this->db->get('evenstdb')->result();

score 0 · Answer 2 · answered Jul 21 '20 at 15:19

I recently needed a query to find overlapping date ranges in MySQL. This solution performs much better than the date range solution above, and works perfectly.

https://stackoverflow.com/a/62761904/11822079

You can do similarly:

AND DATEDIFF(LEAST($date2, endDate),GREATEST($date1, startDate)) > 0;

Example query for the link:

SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)>0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) > 0;

codeigniter select records from date range without repeating a record

2 Answers2