Select AND / OR problem

[eluser]Cambo[/eluser]
Hi Resident Gurus

I am generating this query for a search using Active Record

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'

However, it is not formatting correctly. What I want is:

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')

Notice the brackets around the LIKE clauses?

How do I do that?

TIA
Cambo

[eluser]xwero[/eluser]
It isn't possible with the current AR library, but you can add your vote to add it to the staked wheres topic in the suggestion section.

[eluser]Cambo[/eluser]
Thanks xwero
Ok I had a look at that thread and that's very interesting.
I am using PHP5 so is there a way to put it together with method chaining or should I just go with some native php to get it working?
Regards
Cambo

[eluser]xwero[/eluser]
It's not fully in the AR idea of creating database independent queries but you can add a string as a where part of the sql statement. In your case it would be something like

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();

I like to use the from method to be as close as possible to the actual sql statement but you can add the table as the first argument of the get method to save you some typing.

[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

[eluser]Khoa[/eluser]
I also found that the missing of brackets cause many confusion and also makes us not feel confident about the sql statement that CI generates. I used to do a lot of sql statement writing on my own, and some of them are very very complicated. Manually writing is tedious but it helps me ensure that I put AND, OR and brackets in exactly where I want them to be.

I think there should be a function like: $this->db->openBracket() and $this->db->closeBracket() to allow user to insert them wherever they want.

xwero, your method is a way to work around this, but by doing so, I think we cannot make use of the escaping feature that CI provides (well, to be honest, I dont really know what CI does, but feel more confident whey it says it does :-D). Am i right?

[eluser]xwero[/eluser]
[quote author="Khoa" date="1220008127"]xwero, your method is a way to work around this, but by doing so, I think we cannot make use of the escaping feature that CI provides (well, to be honest, I dont really know what CI does, but feel more confident whey it says it does :-D). Am i right?[/quote]
You can escape the values but you have to do it yourself

Code:

$escaped_product1 = $this->db->escape($product1);
$escaped_product2 = $this->db->escape($product2);
$query = $this->db->select('productID')->from('product')->where('(`productName` LIKE ‘'.$escaped_product1.' %’ OR `productName` LIKE ‘% '.$escaped_product1.' %’ OR `productName` LIKE ‘% '.$escaped_product1.'’)
AND (`productName` LIKE ‘'.$escaped_product2.' %’ OR `productName` LIKE ‘% '.$escaped_product2.' %’ OR `productName` LIKE ‘% '.$escaped_product2.'’)')->get();

[eluser]Khoa[/eluser]
I dont know much about sqj injection, string escaping and stuff like that, so just wondering will that one statement ($this->db->escape()) does all the escapes we need to make it a safe query? And is it also what CI does behind the scene?

[eluser]Khoa[/eluser]
BTW, what I'm trying to do is to write a simple search function in Code igniter. It works well with "legal strings" but I'm struggling in covering all the possible scenario such as: when user enters simple JS statement like [removed]alert('blah blah blah');[removed]. What should we do in that case? Do you usually try to escape it and search for the whole string in a database? Or better just to strip out all <html> tags? Can you share some sample validation? Thanks.