CodeIgniter Search engine not working correct - Get ONLY published posts

Question

I’d like some help please. I have a table iin my database named posts, where I’d like to create a search engine functionality. Here are the basic fields for this:

 table: Posts
post_id (pk)
title
keywords
visible // ****  this is the critical point ****  

I have created also this method in my post_model:

public function search() {
  $this->db->select('post_id, title, etc etc ...');

  // separate the words
  $words = preg_split('/[\s]+/', htmlentities((trim($this->input->post('search')))) ) ;

  // set fields that you wish to search
  $search_fields = array('title', 'keywords');

  // build the query
  foreach ($words as $word) {
   foreach ($search_fields as $field) {
    $this->db->or_like($field, $word);
   }
  }
  return parent::get();
 }

// Generates this query:
SELECT `post_id`, `title` etc etc ...
FROM (`posts`)
WHERE `posts`.`date_published` <= '2014-06-06'
AND `posts`.`visible` =  1
AND  `title`  LIKE '%Lorem,%'
OR  `keywords`  LIKE '%Lorem,%'
OR  `title`  LIKE '%Ipsum%'
OR  `keywords`  LIKE '%Ipsum%'
ORDER BY `date_published` DESC, `posts`.`category_id` ASC, `post_id` DESC

// This is the get() function inside MY_Model 
// Returns results like this $this->db->get($this->_table_name)->result();
public function get($id = NULL, $single = FALSE){
  if ($id != NULL) {
   $filter = $this->_primary_filter;
   $id = $filter($id);
   $this->db->where($this->_primary_key, $id);
   $method = 'row';
  }
  elseif($single == TRUE) {
   $method = 'row';
  }
  else {
   $method = 'result';
  }

  if (!count($this->db->ar_orderby)) {
   $this->db->order_by($this->_order_by);
  }
  return $this->db->get($this->_table_name)->$method();
 } 

The problem is that I also get and unpublished posts (visible = 0) with this, which is not expected, instead what I want to get is ONLY the published posts (visible = 1). How can I fix this ?

EDIT The correct query is this:

SELECT `post_id`, `title` ... . *
FROM (`posts`)
WHERE `date_published` <= '2014-06-06'
AND `visible` =1
AND (
`title` LIKE '%Lorem,%'
OR `keywords` LIKE '%Lorem,%'
OR `title` LIKE '%Ipsum%'
OR `keywords` LIKE '%Ipsum%'
)
ORDER BY `date_published` DESC , `posts`.`category_id` ASC , `post_id` DESC

How can I convert this to an active record ??

Answer 1

It looks like you may need to add some parentheses to your AND/OR logic.

Try this:

SELECT `post_id`, `title` etc etc ...
FROM (`posts`)
WHERE `posts`.`date_published` <= '2014-06-06'
AND `posts`.`visible` =  1
AND  
(`title`  LIKE '%Lorem,%'
OR  `keywords`  LIKE '%Lorem,%'
OR  `title`  LIKE '%Ipsum%'
OR  `keywords`  LIKE '%Ipsum%’)
ORDER BY `date_published` DESC, `posts`.`category_id` ASC, `post_id` DESC

To convert to active record in Codeigniter check out CodeIgniter Active Record multiple "where" and "or" statements OR this