mySQL statement in Zend Framework DB_TABLE

Question

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

    }

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
– mpaepper, Commented Jan 16, 2013 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
– mrGott, Commented Jan 16, 2013 at 10:15

mpaepper · Accepted Answer · 2013-01-16 10:22:25Z

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.

answered Jan 16, 2013 at 10:22

mpaepper

4,0324 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrGott Over a year ago

so, I can do as many ->where's as I want? (judging according you example)

mpaepper Over a year ago

@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); }"

Collectives™ on Stack Overflow

mySQL statement in Zend Framework DB_TABLE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related