CODEWITHSUNDEEP
phpmysqlzend-frameworkzend-db
mrGott
mrGott

Reputation: 1076

mySQL statement in Zend Framework DB_TABLE

I'd like to know how to properly write mySQL statement using ZEND in this case:

SELECT * FROM `users` WHERE role_id = 2 AND status = 2 AND bddate BETWEEN '1988-05-07' AND '1991-01-01' ORDER BY lastname DESC

I'll have if statements, for ex. if in the filter user selected role_id I'll include it in WHERE and so on, the same with the rest of them.

So far I have 1 WHERE condition and I have done it like this (now I need more complicated one):

    $select = $this->select()
            ->setIntegrityCheck(false)
            ->from(array('u'=>'users'))
            ->join(array('r'=>'user_roles'), 'u.role_id = r.role_id', array('role'=>'title'));          
    if(count($filters) > 0) {
        foreach($filters as $field => $filter) {
            $select->where($field . ' = ?', $filter);
        }
    }

    if(null != $sortField) {

        $select->order($sortField);

    }

Upvotes: 0

Views: 374

Answers (1)

mpaepper
mpaepper

Reputation: 4022

Okay, so you are having a problem with the BETWEEN. There are two possible solutions for this:

a) You use two where clauses for to split up the BETWEEN, so you get

$select = $this->select()
//... other conditions
    ->where('bddate >= ?', '1988-05-07')
    ->where('bddate <= ?', '1991-01-01');

OR

b) 

$select = $this->select()
//... other conditions
    ->where ('bddate BETWEEN '1988-05-07' AND '1991-01-01');

I would recommend option a), because it properly escapes your values.

Upvotes: 3

Related Questions