Your OR
condition is breaking it. Right now it is.
if condition1 = TRUE AND condition2 = TRUE AND condition3 = TRUE OR condition4 = TRUE
So you don't have ()
around conditions 3 and 4. This will also give results where only condition4
is true
.
Try
->select ($db->quoteName(array('c.event','b.date','b.type','a.mob','a.joined','a.left')))
->from ($db->quoteName('app_mob_animal', 'a'))
->join('INNER', $db->quoteName('app_event_animal', 'b') . ' ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('b.mob').')')
->join('INNER', $db->quoteName('app_events', 'c') . ' ON (' .$db->quoteName('b.event'). '=' .$db->quoteName('c.id').')')
->join('INNER', $db->quoteName('app_mob', 'd') . ' ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('d.id').')')
->where ($db->quoteName('a.animal') . ' LIKE ' . $db->quote($anid), 'AND')
->where ($db->quoteName('a.joined') . '<=' . $db->quoteName('b.date'), 'AND')
->where ('('.$db->quoteName('a.left') . '>=' . $db->quoteName('b.date') .' OR '. $db->quoteName('a.left') .' LIKE '. $db->quote($emptyDate).')');
You can print out query to see how it is built.
For example, if your query variable is $query
, then do
print_r(echo $query->__toString>dump() ); // Thanks to @DmitryRekun
and it will print full query what gets executed. This way you can see what's wrong with it. Just for simple debugging.