0

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);

    }
mrGott
  • 1,066
  • 3
  • 18
  • 53
  • I do not really understand your problem. It seems like you are already able to build a query. So what is more complicated in your first select statement / where exactly are you having problems? – mpaepper Jan 16 '13 at 10:02
  • I wrote the query, but how to do it for ZEND_DB? in my code I have only 1 WHERE, and in the new statement that I want to implement I have several in addition with BETWEEN – mrGott Jan 16 '13 at 10:15

1 Answers1

3

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.

mpaepper
  • 3,952
  • 3
  • 21
  • 28
  • so, I can do as many ->where's as I want? (judging according you example) – mrGott Jan 16 '13 at 19:38
  • @Mikey Yes, you can. They all get AND-ed together. You actually already do this in your code... "foreach($filters as $field => $filter) { $select->where($field . ' = ?', $filter); }" – mpaepper Jan 17 '13 at 01:02