Select AND / OR problem

[eluser]Cambo[/eluser]
[quote author="xwero" date="1213015266"]

Code:

$query = $this->db->select('productID')->from('product')->where('(`productName` LIKE ‘clear %’ OR `productName` LIKE ‘% clear %’ OR `productName` LIKE ‘% clear’) AND (`productName` LIKE ‘file %’ OR `productName` LIKE ‘% file %’ OR `productName` LIKE ‘% file’)')->get();

[/quote]

OK. After some experimentation, this is what I came up with:

Code:

$searchTerm = "";
    for ($counter=0; $counter < count($keywords_array) ;$counter++)
    {
        $value = $keywords_array[$counter];
                
        if(count($keywords_array) > 1)
        {
            $searchTerm .= ' (';
        }
        // force the item searched for to not be part of another word        
        $searchTerm .= "`".$this->search_columns."` LIKE '". $value." %'";  
        $searchTerm .= " OR `".$this->search_columns."` LIKE '% ". $value." %'";
        $searchTerm .= " OR `".$this->search_columns."` LIKE '% ". $value."'";
            
        if($counter < count($keywords_array)-1 )
        {
            $searchTerm .= ") AND";
        } elseif ($counter == count($keywords_array)-1 && $counter != 0 ) {
        $searchTerm .= ")";
    }
        
    }

$query=$this->CI->db->select($this->entry_identifier)->from($this->table)->where($searchTerm)->get();

Now, I dont think this is too elegant, but it works.

Here is the result of a "print $this->CI->db->last_query();"

Code:

SELECT `productID` FROM (`product`) WHERE (`productName` LIKE 'a4 %' OR `productName` LIKE '% a4 %' OR `productName` LIKE '% a4') AND (`productName` LIKE 'paper %' OR `productName` LIKE '% paper %' OR `productName` LIKE '% paper')

If anyone has any suggestions to improve it, please feel free to post.

Thanks for your help xwero.

Regards Cambo