Skip to main content
added 14 characters in body
Source Link
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(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.

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.

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

echo $query->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.

added debug option
Source Link
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.

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.

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).')');

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.

Source Link
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.

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).')');