Thread

Hi there

I’ve been struggling with getting “OR” searches to work on a site I’m developing. I’ve tried SuperSearch, Low Search and of course the channel:entries tag. In the end I’ve realised that it’s an SQL query that’s gonna solve my problems.

What I have is *nearly* working, but not quite.

In plain english, what I’m trying to achieve is this:

- The user is searching a job vacancy site using the ‘subject’ and ‘region’ fields.
- Each job vacancy can have 2 ‘subjects’ (eg, ‘Business Studies’ and ‘Economics’ could be 1 teaching job). These subjects are 2 custom fields.
- They also search by ‘region’. For this I’m using categories, so a possible value could be ‘80’.
- Also in the mix is the status of the vacancy. It must be either ‘Published’ or ‘School View’

Here’s the sql I have that isn’t really working:

SELECT ct.entry_id, ct.title, cp.cat_id
FROM exp_channel_titles ct 
LEFT JOIN exp_category_posts cp ON ct.entry_id = cp.entry_id
LEFT JOIN exp_channel_data cd ON cp.entry_id = cd.entry_id
WHERE cp.cat_id = '$region'
AND cd.field_id_182 = '$subject'
OR cd.field_id_220 = '$subject'
AND ct.channel_id = 4 
AND ct.status = 'Published' 
OR ct.status = 'School View'
ORDER BY cd.field_id_134 ASC";

$region and $subject are php variables.

Can anyone see anything obviously wrong with this? I know that some of the ‘AND’s are being ignore but no idea why.

Cheers

Jim

Try it like:

WHERE cp.cat_id = '$region'
AND (cd.field_id_182 = '$subject' OR cd.field_id_220 = '$subject')
AND ct.channel_id = 4 
AND (ct.status = 'Published' OR ct.status = 'School View')

Any closer?  And make sure the query’s actually looking like you think it is- turn the output profiler on and just double check nothing odd worked it’s way in - like cp.cat_id = ‘’ or something.

Thanks Robin - I’ll give that a go. I was worried that the LEFT JOIN bits may have been completely wrong, but your reply suggested otherwise, which is nice!

LOL- they could be wrong, but I went with the easy suggestion first.  Heh.  If that isn’t it- then yea, time to start looking at the joins- which I’d have to test to figure out.

When I paste the sql query into phpmyadmin, it seems to bring back the correct results. However, I’m outputting the entry_ids into a php array and for some reason when there are no results, I’m getting random output from my channel entries tag.

My code is as follows:

$query = $this->EE->db->query($sql);

// initiate ids array
$ids = array();

// loop through results, populate array
if ($query->num_rows() > 0)
{
 foreach($query->result_array() as $row)
 {
  $ids[] = $row['entry_id'];
 }
}

Then my entries loop is:

{exp:channel:entries channel="vacancies_channel" limit="10" entry_id="<?=implode('|', $ids)?>" dynamic="no"}
{title}
{/exp:channel:entries}

Can you see why this would give me inconsistent results?

Hrm- php is set to parse on input- yes?  If not- entry_id will be all kinds of wrong.  And this is another good case for debugging.  Turn template debugging on and you can see exactly how the channel entries tag is being interpreted- i.e., make sure that entry_id parameter has what you think it has in it.

That’s my first bet if the custom query seems to be pulling back correctly.