Issues

ZF-3220: Zend_Db_Table bind value problem?

Description

I don't know how this error appears. I have many controllers that has the same logic but only in some this error appear. Example:

I'm deleting a photo from gallery in which goes order by field 'position'. If I'm deleting the last, there is no problem. When I'm deleting other I must decrement position of all photos after. So if the photo delete link is clicked, there is invoked this action:

[CODE] public function deleteAction() { $id = $this->getRequest()->getParam('id'); $photos = new Photos();

    $where = $photos->getAdapter()->quoteInto('id = ?', $id);
    $photo = $photos->fetchRow($where);

    $position = $photo->position;

    $count = $photos->fetchAll()->count();

    if($position == $count)
    {
        $where = $photos->getAdapter()->quoteInto('id = ?', $id);
        $photos->delete($where);
    }
    else
    {
        $where = $photos->getAdapter()->quoteInto('id = ?', $id);
        $photos->delete($where);

        for($i = $position + 1; $i <= $count; $i++)

        {

            $where = $photos->getAdapter()->quoteInto('position = '.$i);

            $data = array();

            $data['position'] = $i - 1;

            $photos->update($data, $where);

        }
    }

    $this->_redirect('admin/prodgallery');
}

[/CODE]

And it is producting this error:

[CODE] Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: no parameters were bound' in /home/httpd/html/shop/library/Zend/Db/Statement/Pdo.php:238 Stack trace: #0 /home/httpd/html/shop/library/Zend/Db/Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array) #1 /home/httpd/html/shop/library/Zend/Db/Adapter/Abstract.php(406): Zend_Db_Statement->execute(Array) #2 /home/httpd/html/shop/library/Zend/Db/Adapter/Pdo/Abstract.php(206): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array) #3 /home/httpd/html/shop/library/Zend/Db/Table/Abstract.php(1185): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select)) #4 /home/httpd/html/shop/library/Zend/Db/Table/Abstract.php(1040): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select)) #5 /home/httpd/html/shop/application/controllers/AdminController.php(423): Zend_Db_Table_Abstract->fetchAll(Object(Zend_Db_Table_Select)) #6 /home/httpd/html/shop/library/Zend/Controller/Action.php(502): AdminCon in /home/httpd/html/shop/library/Zend/Db/Statement/Pdo.php on line 238 [/CODE]

I was searching a lot to solve this. I've found many simmilar errors but in cases where a strange stirngs were binded. In my case we talk about simple numbers...

Comments

I have found the same issue, which seems to be the problem in a file ZendFramework/library/Zend/Db/Table/Abstract.php

The function _fetch should be declared as the following, notice the additional $binds parameter. protected function _fetch(Zend_Db_Table_Select $select,$binds) { $stmt = $this->_db->query($select,$binds); $data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC); return $data; }

And two other functions need amendment as following,

line 1039 of the same file change to: $rows = $this->_fetch($select,(($where instanceof Zend_Db_Table_Select)?$order:array()));

and line 1081 , same file:

$rows = $this->_fetch($select,(($where instanceof Zend_Db_Table_Select)?$order:array()));

That is a quick and dirty fix of the Db_Table select with binds to align it to the documentation specifications.

I have forgot to mention that this issues still remains in the 1.5.2.

Rather than update _fetch() to accept a $bind parameter, we could make the Zend_Db_Table_Select hold the bind variables, see [http://framework.zend.com/issues/browse/ZF-2017].

Then _fetch can be:

protected function _fetch(Zend_Db_Table_Select $select)
    {
        $stmt = $this->_db->query($select, $select->getBind());
        $data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
        return $data;
    }

Line 1039, which is in fetchAll() wouldn't need updating, provided that when you want to use bind variables you call fetchAll with a Zend_Db_Select object rather than a sting where clause. The same is true of line 1081, which is in fetchRow().

The updates to fetchAll() and fetchRow() would not be required if Zend_Db_Select allows bind variables to be stored in the object, and we require users to pass a Zend_Db_Select or Zend_Db_Table_Select object to these methods instead of a string where clause whenever they want to use bind variables.

No action on this issue for too long. I'm reassigning to Ralph for re-evaluation and categorization.

Resolved in r. 14528