Select ignores where clause using Zend_Db_Select

Question

$table = new Zend_Db_Table(array('name'=>'rules'));

    $select = $table->select();
    $select->setTable($table); 
    $select->setIntegrityCheck(false);

    $select = $select
    ->from(array('ru'=>'rules'),array('ru.*'))
    ->join(array('ro'=>'roles'),'ro.id=ru.role_id',array('role_id'=>'ro.id'))
    ->join(array('g'=>'groups'),'ro.group_id=g.id',array('group_id'=>'g.id'))
    ->join(array('ug'=>'user_groups'),"ug.group_id=g.id",array('user_group_id'=>'ug.id'))
    ->where("ug.user_id={$userId}")
    ->where("ru.resource='{$resource}'")
    ->where("ru.privilege='{$privilege}'");
    echo "select: ".$select->__toString();

$row = $table->fetchAll();

I have the preceding code,but when I try fetchAll() it returns all rows in the table, ignoring the where clause, when I use fetchRow() it returns the first row it finds, ignoring the where clause, I printed the SQL statement and run it separately and it executes correctly any clue ?

DarkLeafyGreen · Accepted Answer · 2011-04-28 13:19:20Z

2

This is how you would create a db select object correctly

$db = Zend_Db::factory( ...options... );
$select = new Zend_Db_Select($db);

Or you use the database adapter's select() method

$db = Zend_Db::factory( ...options... );
$select = $db->select();

And you can add clauses

  // Build this query:
  //   SELECT *
  //   FROM "table1"
  //   JOIN "table2"
  //   ON "table1".column1 = "table2".column1
  //   WHERE column2 = 'foo'

  $select = $db->select()
               ->from('table1')
               ->joinUsing('table2', 'column1')
               ->where('column2 = ?', 'foo');

Have a look at the Zend_Db Reference Guide for more information

answered Apr 28, 2011 at 13:19

DarkLeafyGreen

70.6k136 gold badges394 silver badges618 bronze badges

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

Comments

regilero · Accepted Answer · 2011-05-02 10:28:45Z

0

@ArtWorkAD is right in a certain way. But in your case you're not just using a Zend_Db_Select. You tried to extend a Zend_Db_Select obtained from a Zend_Db_Table (well, you should try to handle a Singleton pattern with Zend_Db_Table but this is another problem). Your current problem (if we except the fact you are certainly reading documentation too fast) is that this line was correct:

 $select->setIntegrityCheck(false);

It make your 'select-from-a-zend-db-table' not anymore restricted to the Active Record Mode, and available for extra joins.

But just after that you make a:

$select = new Zend_Db_Select($table);

This is the complete creation of a new object, that you put into your variable. Nothing is kept from previous variable value. You could add a $select=null; just before it would be the same. So this is just canceling the 3 previous lines.

In quite the same confusion mode this line:

$select->setTable($table);

Is not necessary as you're already taking the select from a Zend_Db_Table so the table is already there.

EDIT And your last and bigger error is:

$table->fetchAll()

You do not use your built $select but your $table, so effectively everything done in your $select is ignored :-) . Fecthing from the $select shoudl give you better results

edited May 2, 2011 at 10:28

answered Apr 30, 2011 at 22:22

regilero

30.7k6 gold badges65 silver badges101 bronze badges

4 Comments

omars Over a year ago

The $select = new Zend_Db_Select($table); was added when I was just trying the select object. I had removed it.

omars Over a year ago

your are right also about the setTable method. I had also removed the it, there is nothing changed but it's still not fixed.

regilero Over a year ago

I've edited the answer with your last error, using $table and not $select

omars Over a year ago

Thanks alot, its solved now. I passed the $select to the fetchAll() method

ProfCrazynuts · Accepted Answer · 2011-04-29 13:01:01Z

0

This should work. Just tested it.

    $table = new Zend_Db_Table('rules');
    $select = $table->getAdapter()->select();

    $select->from(array('ru' => 'rules'), array('ru.*'))
           ->join(array('ro'=>'roles'), 'ro.id = ru.role_id', array('role_id'=>'ro.id'))
           ->join(array('g'=>'groups'), 'ro.group_id = g.id', array('group_id'=>'g.id'))
           ->join(array('ug'=>'user_groups'),"ug.group_id=g.id",array('user_group_id'=>'ug.id'))
           ->where('ug.user_id = ?', $userId)
           ->where('ru.resource = ?', $resource)
           ->where("ru.privilege = ?", $privilege);

    echo (string)$select;

answered Apr 29, 2011 at 13:01

ProfCrazynuts

961 silver badge3 bronze badges

1 Comment

omars Over a year ago

The sql produced from my code is the same as I wanted it, and it runs on phpMyAdmin perfectly, but the problem is that when it executes here in the code, it doesn't care about where clauses, it just select everything in the table

Collectives™ on Stack Overflow

Select ignores where clause using Zend_Db_Select

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related