[jonathanc]

So happy to be using the marvellous GroceryCRUD. Thank you.

 

When using the search function with or_where clauses, I believe the SQL query generated is not as expected because of the way the OR and AND clauses are combined.

 

Here is some SQL generated by DB_active_rec...

 

SELECT COUNT(*) AS `numrows`
FROM (`mytable`)
WHERE `myvalue` =  'x'
OR `myvalue` =  'xx'
OR `myvalue` =  'xy'
OR `myvalue` =  'yz'
AND  `mysearch`  LIKE 'searchvalue'

 

But for the filter to work properly we need to bracket the OR clauses together...

 

SELECT COUNT(*) AS `numrows`
FROM (`mytable`)
WHERE (`myvalue` =  'x'
OR `myvalue` =  'xx'
OR `myvalue` =  'xy'
OR `myvalue` =  'yz')
AND  `mysearch`  LIKE 'searchvalue'

 

To achieve this we can change

if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
		{
			$sql .= "\nWHERE ";
		}

		$sql .= implode("\n", $this->ar_where);

To..

if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
        {
            $sql .= "\nWHERE (";

            $sql .= implode("\n", $this->ar_where);

            $sql .= ")";
        } else{

        $sql .= implode("\n", $this->ar_where);

        }

I suspect other clauses may be similarly affected but haven't had time to check yet.

 

Thank you

[semsik]

I know this is from 2014 but I had the exact same problem with the bootstrap theme, using a or_where clause breaks the search.

I worked around the problem by using the custom query where from codeigniters (3.x) manual like this

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

Now search is working again and no need for adjusting the core of GC.

 

Semsik

Codeigniter 3.x, Grocery Crud 1.5.6 Bootstrap v3 Theme