CODEWITHSUNDEEP
phpcakephpsearch
Scrappy Cocco
Scrappy Cocco

Reputation: 1204

Cakephp - Search with AND and OR conditions

I am trying to implement a search in which all fields are optional, so i have to filter my results based on user input, but am stuck on how to achieve this...

The search field is as follows

1) serach by email :

2) serach by name :

3) serach by registration date : (i.e fromDate and toDate)

options 1) and 3) am able to achieve as only an AND condition is required.

problem is with option 2) name should match either first_name or last_name, so i must have an OR condition , which i cant figure out a right way to do it..

Note: All fields are optional...

heres my code snippet....

$conditions = array();

if(!empty($this->request->data['searchbyemail'])) {

   $conditions[] = array('User.username' => $this->request->data['searchbyemail']);
}

if(!empty($this->request->data['regfromdate']) &&
   !empty($this->request->data['regtodate'])) {

    $conditions[] = array('User.created BETWEEN ? AND ?' => 
    array($this->request->data['regfromdate'], $this->request->data['regtodate']));
}

if(!empty($this->request->data['searchbyname'])) {

    $conditions[] = array(
'OR' => array(
        'User.first_name' => $this->request->data['searchbyname'],
        'User.last_name' => $this->request->data['searchbyname']

                ));
}


$this->paginate = array('conditions' => $conditions);
$this->set('users', $this->paginate());

The query am trying to achieve should look like this

SELECT * FROM  `users` AS `User`
WHERE `User`.`username` = 'a'
AND `User`.`created`
BETWEEN '2013-02-01'
AND '2013-02-05'
AND ((`User`.`first_name` LIKE '%b%')) OR ((`User`.`last_name` LIKE '%b%'))

Am able to generate this query in cakephp but its static , am looking for a dynamic way to generate this query since the search fields are optional and the query should change according to the user input.....

Upvotes: 0

Views: 2897

Answers (1)

penguin egg
penguin egg

Reputation: 1194

To use the LIKE operator with the % wildcards, you would:

        'OR' => array(
            'User.first_name LIKE' => "%" . $this->request->data['searchbyname'] . "%",
            'User.last_name LIKE' => "%" . $this->request->data['searchbyname'] . "%"
        ),

Upvotes: 3

Related Questions