Creating a where query with AND & OR

Forums

Please write a review for the extension you are using on the Joomla extensions directory before posting a new question as we are being spammed by many negative reviews.

Thank you for your support!

Creating a where query with AND & OR

Twincarb 18 Aug, 2017

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 18 Aug, 2017

Answer

1 Likes

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

This topic is locked and no more replies can be posted.