Issue

ZF-5189: Zend_Paginator_Adapter_DbSelect - no way to bind data to placeholders

Issue Type: Improvement Created: 2008-12-08T20:11:09.000+0000 Last Updated: 2010-06-07T12:34:41.000+0000 Status: Resolved Fix version(s): Reporter: Phil Brown (philbrown) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator

Related issues: Attachments:

Description

Given a query with named parameter placeholders, eg

<pre class="highlight">
SELECT * FROM TABLE WHERE STATUS = :status

there's currently no way to pass the array of bind variables to the pagination adapter for use in the getItems() or count() / setRowCount() methods.

Given that this is the recommended method of parameter passing (at least for non-string values), shouldn't it be supported by the adapter?

Comments

Posted by Jurrien Stutterheim (norm2782) on 2008-12-23T11:02:19.000+0000

A Zend_Db_Statement object isn't flexible enough to be used in a similar way as a Zend_Db(_Table)_Select object. The current Zend_Db_Select adapter works by modifying the original query and converting it into a COUNT query to determine the amount of items in the result set. Zend_Db_Statement uses plain strings for the unbound query. At the moment I don't see a way to create decent support for Zend_Db_Statement. Suggestions are welcome, but I'll postpone this issue until a nice way to support this is offered. Please use Zend_Db(_Table)_Select for paginating Db results in the mean while.

Posted by Phil Brown (philbrown) on 2009-01-04T16:35:31.000+0000

I've solved this by extending Zend_Paginator_Adapter_DbSelect with the following alterations.

<pre class="highlight">
protected $_bind = array();

public function __construct(Zend_Db_Select $select, array $bind = array())
{
    parent::__construct($select);
    $this->setBindValues($bind);
}

public function setBindValues(array $bind)
{
    $this->_bind = $bind;
    return $this;
}

in setRowCount()

<pre class="highlight">
$stmt = $rowCount->getAdapter()->query($rowCount, $this->_bind);
$stmt->setFetchMode(Zend_Db::FETCH_ASSOC);
$result = $stmt->fetch();

and similarly in getItems()

<pre class="highlight">
$stmt = $this->_select->getAdapter()->query($this->_select, $this->_bind);
$stmt->setFetchMode($this->_select->getAdapter()->getFetchMode());
return $stmt->fetchAll();

Of course, when using the Zend_Paginator::factory() method, the bind data needs to be added after the paginator is created. Otherwise, the array can be set using the adapter's constructor.

Posted by Jurrien Stutterheim (norm2782) on 2009-03-30T18:05:50.000+0000

Has been resolved with ZF-2017 and ZF-3220

Posted by Aaron S. Hawley (ashawley) on 2010-06-07T12:34:37.000+0000

Phil Brown's suggestion in the comment above is a good one.

It should be accepted into ZF in my opinion.

Here's a patch that works for me.

<pre class="literal">
2010-06-03  Aaron S. Hawley   vtinfo.com>

    * Zend/Paginator/Adapter/DbSelect.php: Pass bind data values
          to Zend_Db_Select.

--- Zend/Paginator/Adapter/DbSelect.php 2010-04-29 12:37:49.475322300 -0400
+++ Zend/Paginator/Adapter/DbSelect.php 2010-06-03 16:57:27.698390900 -0400
@@ -64,6 +64,13 @@
     protected $_select = null;
 
     /**
+     * Bind values
+     *
+     * @var array
+     */
+    protected $_bind = array();
+
+    /**
      * Total item count
      *
      * @var integer
@@ -118,7 +125,8 @@
                 throw new Zend_Paginator_Exception('Row count column not found');
             }
 
-            $result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch();
+            $result = $rowCount->query(Zend_Db::FETCH_ASSOC, $this->_bind)
+                               ->fetch();
 
             $this->_rowCount = count($result) > 0 ? $result[$rowCountColumn] : 0;
         } else if (is_integer($rowCount)) {
@@ -146,7 +154,7 @@
     {
         $this->_select->limit($itemCountPerPage, $offset);
 
-        return $this->_select->query()->fetchAll();
+        return $this->_select->bind($this->_bind)->query()->fetchAll();
     }
 
     /**
@@ -259,4 +267,15 @@
 
         return $rowCount;
     }
+
+    /**
+     * Set the data to bind to placeholders.
+     *
+     * @return Zend_Db_Select
+     */
+    public function setBindValues(array $bind)
+    {
+        $this->_bind = $bind;
+        return $this;
+    }
 }

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2017 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts