Search engine not working correct - Get ONLY published posts

[eluser]Lykos22[/eluser]
Hi , 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:

Code:

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

I have created also this method in my post_model:

Code:

public function search() {
  $this->db->select('posts.*')->select('categories.category_id')
  ->select('categories.category_name AS category, categories.slug AS cat_slug')
  ->join('categories', 'posts.category_id = categories.category_id', 'left');
  
  // 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 `posts`.*, `categories`.`category_id`, `categories`.`category_name`
FROM (`posts`)
LEFT JOIN `categories` ON `posts`.`category_id` = `categories`.`category_id`
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 ?

[eluser]Lykos22[/eluser]
The query seemed to have some issues on logic, so I did some small changes on search() function. Here's what I did and fixed it:

Code:

// build the query
     $where_str = '(';
  $like_str = '';
  foreach ($words as $word) {  
   // set condition for this word
   foreach ($search_fields as $field) {
    $like_str .= ' OR `' . $field . '` LIKE \'%' . $word . '%\' ';
   }
  }

  // remove first ' OR ' and close parenthesis
     $where_str .= substr($like_str, 4);
     $where_str .= ')';
    
     // add condition to query
     $this->db->where($where_str);

// Generates this query:
SELECT `posts`.*, `categories`.`category_id`, `categories`.`category_name`
FROM (`posts`)
LEFT JOIN `categories` ON `posts`.`category_id` = `categories`.`category_id`
WHERE `posts`.`date_published` <= '2014-06-06'
AND `posts`.`visible` =  1
AND  // this is where the query changes
( `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