Creating a where query with AND & OR

Question

I have been asked to add a new query to a CC5 model but I am struggling the requirement is to look check to see if a record is published which is

job_view.status => '1'

I then need to check-in 3 columns to see if it matches the user id, the user id will appear in one of the columns or none.

The current code which works and allows me to check the first column is below, I can't get it to look in the other 2 columns.

<?php
$user = JFactory::getUser();
return array( 'job_view.status' => '1', 'job_view.r1_name' => $user->id );
?>

This is my attempt crafting the statement, but it doesn't work, hope there is a simple answer.

<?php
$user = JFactory::getUser();
return array(":job_view.status => '1', job_view.r1_name => $user->id OR job_view.r2_name => $user->id OR job_view.r3_name => $user->id");
?>

Regards,
Dave

Twincarb · Accepted Answer

Right, I have solved it🙂

the final code that I needed was:

<?php
$user = JFactory::getUser();
return array(":job_view.status = '1' AND (job_view.r1_name = '$user->id' OR job_view.r2_name = '$user->id' OR job_view.r3_name = '$user->id') ");
?>

Having enabled debug I could see the final WHERE function, popped into PHPMyAdmin and ran the query when it returned the wrong result I took another look at my logic and realised the OR statement needed to be contained in it's own brackets.

When I ran the query in PHPMyAdmin with the brackets it returned the correct result I was expecting, I proved this by modifying the status of a couple of records allong with the userid I was looking for to prove it.

I hope this may help assist someone else who hasn't moved to CC6 yet.

Dave