Multiple Keyword Search

[eluser]Lykos22[/eluser]
[quote author="JoostV" date="1402261555"]@Lykos22 : The error is here:

Code:

When you do $where_str .= substr($where_str, 4) . ')'; you are removing the first  parenthesis as well. Try this:
[code]
$where_str = '';
foreach ($search_fields as $field) {
    $where_str .= ' OR ' . $field . ' LIKE \'%' . $word . '%\'';
}
// Wrap in parentheses and remove first OR
$where_str = '(' . ltrim($where_str, ' OR ') . ')';

[/quote]

@JoostV: I tried the code you posted, but still getting error

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'OR `title` LIKE '%codeigniter%' OR `meta_keywords`
LIKE '%codeigniter%' OR `titl' at line 6

SELECT `articles`.*, `categories`.`category_id`, `categories`.`category_name` AS category, `categories`.`slug` AS cat_slug
FROM (`articles`) LEFT JOIN `categories`
ON `articles`.`category_id` = `categories`.`category_id`
WHERE `articles`.`pubdate` <= '2014-06-09'
AND `articles`.`visible` = 1
AND
(( OR `title` LIKE '%codeigniter%'
OR `meta_keywords` LIKE '%codeigniter%'
OR `title` LIKE '%tips%'
OR `meta_keywords` LIKE '%tips%' )
ORDER BY `pubdate` DESC, `articles`.`category_id` ASC, `article_id` DESC

However I found out a way to do this, which fetches the correct results:

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 .= ')';

// which generates the expected query:
SELECT `articles`.*, `categories`.`category_id`, `categories`.`category_name`
FROM (`articles`)
LEFT JOIN `categories` ON `articles`.`category_id` = `categories`.`category_id`
WHERE `articles`.`pubdate` <= '2014-06-06'
AND `posts`.`visible` =  1

// this is where the query changes
AND  
( `title`  LIKE '%Lorem,%'
OR  `keywords`  LIKE '%Lorem,%'
OR  `title`  LIKE '%Ipsum%'
OR  `keywords`  LIKE '%Ipsum%' )
ORDER BY `pubdate` DESC, `articles`.`category_id` ASC, `article_id` DESC

I have one question though. How can I paginate the results?

To be more specific when I click the link to get me to the othe pages, the query changes and return all data from database (not those with matching keywords).

Well I thought to put the keywords as parameters on the url and get them for the other pages, but the url will be dynamic and can't define the $config['uri_segment'] in pagination, based on its length.