Zend Framework 1 Db Multiple where statements

Question

Having following variable $select where all necessary configurations are made and works fine. There is task for filtering data on same table like following:

For example table values with fields:

id object_type object_value
1-----------8------------1
1-----------5------------300
2-----------8------------0
2-----------5------------500
3-----------8------------1
3-----------5------------400
4-----------8------------1
4-----------2------------10 ...

Values must be sorted like following:

When object_type = 8 chose only object_value = 1
AND
When object_type = 5 chose only object_value >= 400 For example.

Tried using Zend_Db_selectunion() but that doesn't seem to work. Any ideas will be very helpfull. Also with joins doesn't seem to be an option.Couldn't implement case.

Update Applying query should sort data and output :

id object_type object_value
3-----------8------------1
3-----------5------------400

Explanation For example id=1 has 2 rows

object_type=8
object_value=1

AND

object_type=5
object_value=300

Query checks if id with number when object_type = 8 the object_value must be = 1 and when object_type=5 then object_value must be >= 400 so in following case id with number 1 and it's values won't be displayed. Only id with number 3 matched searched query. Hope a little explanation made thinks clear if not i will add later experimental values on concrete objects.

It's not really clear what you're trying to achieve. You want to do a filtered select (showing object_type 8 and object_type 5)? Or you have some parameter to give into your query that let you choose if you want to see object_type = 8 or object_type = 5? — Gounemond
– Gounemond, Commented Oct 20, 2014 at 13:41
@Gounemond Updated How output should look. Hope thinks become more clear. — Igor
– Igor, Commented Oct 20, 2014 at 13:49
possible duplicate of Zend-framework DB: OR instead of AND operator — Niko
– Niko, Commented Oct 20, 2014 at 14:10
@Igor Could you then try to explain a bit better what you want to achieve? For example, why is there only one row with object_type 8 in your example result? — Niko
– Niko, Commented Oct 20, 2014 at 14:18

Gounemond · Accepted Answer · 2014-10-20 14:57:16Z

1

SQL speaking, what you want is something like this:

select a.*, b.*  
from MyTable a
join MyTable b on b.id = a.id
where a.object_type = 8 and a.object_value = 1
and b.object_type = 5 and b.object_value >= 400

If you want to achieve a result like this one, you just need to get a good where condition, plus a join condition.

//your program

$select = $this->_table->select()
               ->from(array('a' => 'MyTable'),
               ->joinLeft(array('b' => 'MyTable'), 'b.id = a.id', array())

$_where = array()
$_where[] = array('object_type= ' . "8", 'object_value = '. "1");
$_where[] = array('object_type= ' . "5", 'object_value >= '. "400");

// you can do a loop after the first element if 
// you have more conditions to put in OR
$select->where($_where[0]);
$select->orWhere($_where[1]);

//...

or, if you're doing an update

 _dbTable->update($_data, $_where);

(Put in $_data whatever you're going to update) It may be nice to add an

$select->order(...);

depending on what you want to do!

edited Oct 20, 2014 at 14:57

answered Oct 20, 2014 at 14:03

Gounemond

3531 silver badge9 bronze badges

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

5 Comments

Igor Over a year ago

I post in Update what output should be based on query.

Gounemond Over a year ago

So, instead of $_where and $_where2, just declare $_where = array(); $_where[] = //first condition; $_where[] = //second condition. Then ->update($_data, $_where); (on $_data you will put columns to modify and new values). Check updated answer!

Igor Over a year ago

check Update block part in my question.

Gounemond Over a year ago

I understood now, you want to output a couple with the same ID, that respect both conditions on type - value. Am I right?

Igor Over a year ago

Yes.Already found solution using joins by setting different alias. Checked your answer and is similar to what i have done.

Collectives™ on Stack Overflow

Zend Framework 1 Db Multiple where statements

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related