CODEWITHSUNDEEP
phpmysqlcakephp
numerical25
numerical25

Reputation: 10790

CAKEPHP CONDITIONS OR Statement

I am trying to filter a search result based on the LIKE wildcard. I also want to add several OR statements if the end user inputs multiple keywords. But My query returns back all results instead of filter results, below is the code

        function search()
        {
            $queries = explode(" ", $this->data['Project']['query']);
            $nq = '';
            foreach($queries as $q)
            {
                $nq[] = array('Project.project_title LIKE'=>"%$q%");
            }

            $final = array("OR"=>$nq);

            debug($this->Project->find('list',$final));
        }



1   SHOW FULL COLUMNS FROM `projects`       28  28  16
2   SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME= 'latin1_swedish_ci';     1   1   1
3   SHOW FULL COLUMNS FROM `users`      2   2   8
4   SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME= 'utf8_general_ci';       1   1   1
5   SHOW FULL COLUMNS FROM `categories`     6   6   11
6   SHOW FULL COLUMNS FROM `operating_systems`      2   2   7
7   SHOW FULL COLUMNS FROM `project_operating_systems`      3   3   10
8   SHOW FULL COLUMNS FROM `projects_categories`        3   3   12
9   SELECT `Project`.`id`, `Project`.`project_title` FROM `projects` AS `Project` WHERE 1 = 1       4   4   1
10  SELECT `Category`.`id`, `Category`.`name`, `Category`.`parent_id`, `Category`.`url`, `Category`.`lft`, `Category`.`rght`, `ParentCategory`.`id`, `ParentCategory`.`name`, `ParentCategory`.`parent_id`, `ParentCategory`.`url`, `ParentCategory`.`lft`, `ParentCategory`.`rght` FROM `categories` AS `Category` LEFT JOIN `categories` AS `ParentCategory` ON (`Category`.`parent_id` = `ParentCategory`.`id`) WHERE 1 = 1

Upvotes: 0

Views: 665

Answers (2)

Digital Chris
Digital Chris

Reputation: 6202

When building a query witn a series of ors like this, always be careful to enclose your OR conditions in parentheses, or you will negate your AND conditions.

Example:

SELECT * 
FROM users
WHERE type='teacher'

Only gives you teachers. 

SELECT * 
FROM users
WHERE type='teacher'
AND name like '%Joe%'

This gives only teachers with "Joe" somewhere in their name. HOWEVER:

SELECT * 
FROM users
WHERE type='teacher'
AND name like '%Joe%'
OR name like '%Mary%'

You might expect this to give teachers whose name is either "Joe" or "Mary"... but it does not. It will give you ANY user whose name is "Mary". To correct this, you use parentheses:

SELECT * 
FROM users
WHERE type='teacher'
AND 
(name like '%Joe%'
OR name like '%Mary%')

Upvotes: 0

Anh Pham
Anh Pham

Reputation: 5481

debug($this->Project->find('list',array('conditions'=>$final)));

Upvotes: 1

Related Questions