Select ignores where clause using Zend_Db_Select

$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 t开发者_C百科able, 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 ?

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

@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

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;