Zend Framework: How to include an OR statement in an SQL fetchAll()

Question

I am trying to build the following SQL statement:

SELECT `users_table`.*, `users_data`.`first_name`, `users_data`.`last_name`
FROM `users_table`
INNER JOIN `users_data` ON users_table.id = user_id
WHERE (users_table.username LIKE '%sc%')
OR (users_data.first_name LIKE '%sc%')
OR (users_data.last_name LIKE '%sc%')

I have the following code at the moment:

public function findAllUsersLike($like) {
    $select = $this - > select(Zend_Db_Table::SELECT_WITH_FROM_PART) - > setIntegrityCheck(false);
    $select - > where('users_table.username LIKE ?', '%'.$like.'%');
    $select - > where('users_data.first_name LIKE ?', '%'.$like.'%');
    $select - > where('users_data.last_name LIKE ?', '%'.$like.'%');
    $select - > join('users_data', 'users_table.id = user_id', array('first_name', 'last_name'));
    return $this - > fetchAll($select);
}

This is close, but not right as it uses AND to add the extra WHERE statements, instead of OR.

Is there any way to do this as one select? Or should I perform 3 selects and combine the results (alot more overhead?)?

P.S. The parameter $like that is past is sanitized so don't need to wory about user input in the code above!

score 4 · Accepted Answer · answered Jan 18 '10 at 15:14

4

public function findAllUsersLike($like) {
    $select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)->setIntegrityCheck(false);
    $select->orWhere('users_table.username LIKE ?', '%'.$like.'%');
    $select->orWhere('users_data.first_name LIKE ?', '%'.$like.'%');
    $select->orWhere('users_data.last_name LIKE ?', '%'.$like.'%');
    $select->join('users_data', 'users_table.id = user_id', array('first_name', 'last_name'));
    return $this->fetchAll($select);
}

answered Jan 18 '10 at 15:14

St.Woland

5,357
30
30

1

I wasn't sure if the first where could be a `orWhere` so I tested it, it doesn't matter. – Robin Jan 18 '10 at 15:21
I thought it would be where rather than orwhere - but like you say it doesn't seem to matter! Thanks! – Scoobler Jan 18 '10 at 15:43

score 2 · Answer 2 · answered Jan 18 '10 at 15:14

2

Zend_Db_Select has a function orWhere. You should be able to solve your problem with it.

answered Jan 18 '10 at 15:14

Robin

303
5
17

score -1 · Answer 3 · answered Mar 25 '10 at 12:08

You can also nest OR statements using double quotes ("):

$select->where('
users_table.username LIKE ' "%'.$like1.'%" '
OR users_data.first_name LIKE '"%'.$like2.'%" '
OR users_data.last_name LIKE '"%'.$like3.'%"'
);

Which works better when you create sql including also AND like:

select X from Y where A = AAA and (B = BBB or C = CCC or D = DDD)

Zend Framework: How to include an OR statement in an SQL fetchAll()

3 Answers3