Skip to main content
2 of 3
added debug option
Rene Korss
  • 3.2k
  • 1
  • 14
  • 29

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( $query->__toString() );

and it will print full query what gets executed. This way you can see what's wrong with it. Just for simple debugging.

Rene Korss
  • 3.2k
  • 1
  • 14
  • 29