Zend Framework 1.12 Db Select - nested WHEREs using multiple values for question marks in condition

Question

I'm using Zend Framework 1.12.3. I need to create a query that hasnested WHEREs, such as

SELECT * FROM table1
WHERE (id = 'foo' AND name = 'bar') OR (grade = 123)

Here's my attempt

$this->getDbTable()->select()
->from($this->getDbTable(), array('*')
->where('id = ? AND name = ?', $foo, $bar)
->orWhere('grade = ?', $grade);

However, the outcome is

SELECT * FROM table1
WHERE (id = 'foo' AND name = 'foo') OR (grade = 123)

instead of name = 'bar'

Basically, I cannot use multiple ?s assigning each ? a different value. Do you know any solution?

Thanks

LE: using a WHERE condition such as ->where("id = $foo and name = $bar") does work, however it doesn't prevent injection attacks like the ? does

score 1 · Answer 1 · answered Jul 16 '14 at 09:58

1

Looking at the code, I see no way to do it, the where clause only works with a condition, but I think that adding parentheses, you can manage the AND and OR with the right priorities.

Try this:

this->getDbTable()->select()
    ->from($this->getDbTable(), array('*')
    ->where('(id = ?', $foo) // add '(' before condition
    ->where('name = ?)', $bar) // add ')' after condition
    ->orWhere('grade = ?', $grade);

answered Jul 16 '14 at 09:58

doydoy44

5,720
4
29
45

This was only an example, I've got multiple nested WHEREs. I'll try it though. – Daniel Jul 16 '14 at 10:49

score 1 · Answer 2 · answered Jul 16 '14 at 10:51

I would use named params for binding, like so:

    $sql = $this->getDbTable()->select()
        ->from($this->_name, array('*'))
        ->where('id = :foo AND name = :bar')
        ->orWhere('grade = ?', 'grade');
    $this->getDbTable()->fetchAll($sql, array('foo' => 'foo', 'bar' => 'bar'));

Zend Framework 1.12 Db Select - nested WHEREs using multiple values for question marks in condition

2 Answers2