Hi All
so I am having some problems with events and locations
within a script I am trying to get a list of events for a location which intersects with a period I specify
like so
$events = $location->get_linked_beans(
'fp_event_locations_fp_events_1',
'FP_events',
0,
-1,
0,
"fp_events.date_start >= CONVERT('{$period['dateStart']}', DATETIME) AND fp_events.date_end =< CONVERT('{$period['dateEnd']}', DATETIME)"
);lets say I set dateStart and dateEnd to ‘2019-04-23 16:00:00’
and there is an event with these values
start date: 2018-09-20 09:00:00
end date: 2018-09-20 16:00:00
the above php will return this event record.
If I was to run the following query however
SELECT
l.name AS 'location',
e.name AS 'event',
e.date_start AS 'start date',
e.date_end AS 'end date',
e.date_start >= CONVERT('2019-04-23 16:00:00', DATETIME) AS'bool 1',
e.date_end <= CONVERT('2019-04-23 16:00:00', DATETIME) AS 'bool 2',
e.date_start >= CONVERT('2019-04-23 16:00:00', DATETIME) && e.date_end <=CONVERT('2019-04-23 16:00:00', DATETIME) AS 'bool 3'
FROM `fp_event_locations` AS l
LEFT JOIN fp_event_locations_fp_events_1_c AS le ONle.fp_event_locations_fp_events_1fp_event_locations_ida = l.id
LEFT JOIN fp_events AS e ON le.fp_event_locations_fp_events_1fp_events_idb = e.id
WHERE e.deleted = 0
AND l.deleted = 0
AND le.deleted = 0
AND e.id = 'record id'this returns
[table]
[tr]
[td]location[/td]
[td]event[/td]
[td]start date[/td]
[td]end date[/td]
[td]bool 1[/td]
[td]bool 2[/td]
[td]bool 3[/td]
[/tr]
[tr]
[td]First Floor - F8 - Classroom[/td]
[td]Project Management[/td]
[td]2018-09-20 09:00:00[/td]
[td]2018-09-20 16:00:00[/td]
[td]0[/td]
[td]1[/td]
[td]0[/td]
[/tr]
[/table]
looking at bool 3, you can see that this record should not have been returned from the get_linked_beans function used above.
am I doing something wrong?