Zend Framework 2 Db \ Adapter \ Adapter查询结果集，如ZF1

Just need a hand understanding some simple database queries in ZF2. In ZF1 I have simple methods like this:

只需要了解ZF2中的一些简单数据库查询。在ZF1中我有这样简单的方法：

public function recordset()
{
// listing of all records 
$db = Zend_Registry::get('db');
$sql = "SELECT " . $this->_selectlist() .
    " from customer c";
$r = $db->fetchAll($sql);
return $r;
}

In ZF2, how would I do the same? I have tried the following, but this just returns what looks like a "Result" object, but all I want is an array like ZF1 did with fetchAll. If I have to iterate the result object only to provide the array later, which then must be iterated over again, it just seems like some duplication of effort.

在ZF2中，我将如何做同样的事情？我尝试了以下内容，但这只是返回看起来像“Result”对象的内容，但我想要的只是一个像ZF1那样的数组与fetchAll。如果我必须迭代结果对象只是为了稍后提供数组，然后必须重复迭代，它似乎只是一些重复工作。

Anyway, here's what I have in ZF2 so far:

无论如何，这是我到目前为止在ZF2中所拥有的：

//above the controller start I have: use Zend\Db\Adapter\Adapter as DbAdapter;

public function blaAction()
{
    $db = new DbAdapter(
        array(
            'driver'        => 'Pdo',
            'dsn'            => 'mysql:dbname=mydb;host=localhost',
            'username'       => 'root',
            'password'       => '',
            )
    );
    $sql = 'select * from customer';
    $stmt = $db->query($sql);
    $results = $stmt->execute();
    $this->view->data = $results;
    return $this->view;
}

In the output, I get this:

在输出中，我得到这个：

object(Zend\Db\Adapter\Driver\Pdo\Result)#197 (8) { 
     ["statementMode":protected]=> string(7) "forward" ["resource":protected]=> object(PDOStatement)#195 (1) { 
        ["queryString"]=> string(22) "select * from customer" 
  } ["options":protected]=> NULL ["currentComplete":protected]=> bool(false) ["currentData":protected]=> NULL ["position":protected]=> int(-1) ["generatedValue":protected]=> string(1) "0" ["rowCount":protected]=> NULL 
}

However, if I change $results to $results->count(); I do indeed see a record count. How do I get to the data though as an array? (a full recordset)

但是，如果我将$ results更改为$ results-> count（）;我确实看到了记录数。我如何作为数组获取数据？ （完整的记录集）

At one point I did see something like: $results->current() But that only returned a single record.

有一次，我确实看到了类似的结果：$ results-> current（）但是只返回了一条记录。

Just a side note. I do see all the table abstract classes I could use, but at this point in my learning, I don't want to do that. I just want some simple on-demand queries that return arrays like they did in ZF1. In ZF2, there's seems to be way too much "wiring up" of things in configs and stuff that just seem like overkill. But, as a framework, I like the flexibility and the main app I am working on in ZF1 could really benefit from the modularity of ZF2. (otherwise I'd probably go with other framework)

只是旁注。我确实看到了我可以使用的所有表格抽象类，但在我学习的这一点上，我不想那样做。我只想要一些简单的按需查询返回数组，就像在ZF1中一样。在ZF2中，在配置和东西中似乎有太多“连线”的东西，这看起来有点矫枉过正。但是，作为一个框架，我喜欢灵活性，我在ZF1中工作的主应用程序可以真正受益于ZF2的模块化。 （否则我可能会选择其他框架）

Please forgive my ignorance, and thanks very much for any help!

请原谅我的无知，非常感谢你的帮助！

5 个解决方案

#1

From http://framework.zend.com/manual/2.0/en/modules/zend.db.result-set.html:

来自http://framework.zend.com/manual/2.0/en/modules/zend.db.result-set.html：

Zend\Db\ResultSet is a sub-component of Zend\Db for abstracting the iteration of rowset producing queries.

Zend \ Db \ ResultSet是Zend \ Db的子组件，用于抽象行集生成查询的迭代。

So you can do the following:

所以你可以做到以下几点：

$statement = $db -> query($sql);

/** @var $results Zend\Db\ResultSet\ResultSet */
$results = $statement -> execute();

$returnArray = array();
// iterate through the rows
foreach ($results as $result) {
    $returnArray[] = $result;
}

Now you can send it to the view:

现在您可以将其发送到视图：

return new ViewModel(array('results' => $returnArray));

#2

Ok, I think I've got it. At least this will do the job for the time being. Basically, you have to add one extra step and feed the result object into a ResultSet object which has a toArray convenience method. I suppose this could be done a million other ways, but... this works.

好的，我想我已经明白了。至少这将暂时完成这项工作。基本上，您必须添加一个额外的步骤并将结果对象提供给具有toArray便捷方法的ResultSet对象。我想这可以通过其他一百万种方式来完成，但是......这很有效。

Keep in mind, I wouldn't do this in a controller, or even in this exact way, but its only a test at this point. There's times when I want this available, and this is how ZF2 can do it, if one desired. (never minding good/bad habits)

请记住，我不会在控制器中执行此操作，甚至不会以这种方式执行此操作，但此时它只是一个测试。有时候我想要这个，这就是ZF2可以做到的，如果需要的话。 （从不关注好/坏习惯）

In the top of the Controller add/use the ResultSet:

在Controller的顶部添加/使用ResultSet：

use Zend\Db\ResultSet\ResultSet;

Here's the working test action:

这是工作测试操作：

public function blaAction()
{
    $db = new DbAdapter(
        array(
            'driver'        => 'Pdo',
            'dsn'            => 'mysql:dbname=mydb;host=localhost',
            'username'       => 'root',
            'password'       => '',
            )
    );
    $sql = 'select * from customer 
        where cust_nbr > ? and cust_nbr < ?';
    $sql_result = $db->createStatement($sql, array(125000, 125200))->execute();
    if($sql_result->count() > 0){
        $results = new ResultSet();
        $this->view->data = $results->initialize($sql_result)->toArray();
    }
    return $this->view;
}

toArray is just doing a foreach loop for you, so, I guess its still adding extra array loops I wanted to avoid, but not having looked at ZF1 version of their code, maybe its doing the same anyway.

toArray正在为你做一个foreach循环，所以，我想它仍然会添加额外的数组循环，我想避免，但没有看过他们的代码的ZF1版本，也许它的做法无论如何。

What I will probably do is create a simple db wrapper class for Zend\Db that replaces my Zend_Registry statement from ZF1 and adds a fetchAll and fetchOne method, that way I can quickly port over a bunch of ZF1 code to ZF2 much easier.

我可能会做的是为Zend \ Db创建一个简单的db包装类，它从ZF1替换我的Zend_Registry语句并添加一个fetchAll和fetchOne方法，这样我就可以更快地将一堆ZF1代码快速移植到ZF2。

Thanks for your input in the comments, I appreciate it. :)

感谢您在评论中的意见，我很感激。 :)

Oh, I also wanted to mention. I ran into this bridge class someone created, which might also be helpful: https://github.com/fballiano/zfbridge

哦，我也想提一下。我遇到了有人创建的这个桥类，这可能也很有帮助：https：//github.com/fballiano/zfbridge

EDIT: So the adapter results returned are iterable it turns out. I am not sure what steps I took that led to my confusion, but the results in $db->query are returned as a Pdo\Result object and that can be looped in foreach easy enough. What messed me up was the fact that if you var_dump it,it doesn't show the array data, just the object. That led me down a totally confusing path.

编辑：所以返回的适配器结果是可迭代的，事实证明。我不知道我采取了哪些步骤导致了我的困惑，但是$ db-> query中的结果作为Pdo \ Result对象返回，并且可以在foreach中循环播放。搞砸了我的事实是，如果你var_dump它，它不会显示数组数据，只显示对象。这让我陷入了一条完全令人困惑的道路。

Now, even though the above works, this is better IMO, because we can take that object, send it where we want for iteration later. (rather than loop over the whole thing to create an array first, only to iterate another loop, waste of time that way)

现在，即使上面的工作，这是更好的IMO，因为我们可以采取该对象，将其发送到我们想要稍后迭代的地方。 （而不是循环遍历整个事物来创建一个数组，只是迭代另一个循环，浪费时间）

Here's a working example I like better. you just loop the object, and there's your data! duh! Not sure how I miss the simple things sometimes. :)

这是一个我更喜欢的工作示例。你只需循环对象，就有你的数据！咄！不知道我有时会想念简单的事情。 :)

public function blaAction()
{
    $db = new DbAdapter(
        array(
            'driver'        => 'Pdo',
            'dsn'            => 'mysql:dbname=gwdb;host=localhost',
            'username'       => 'root',
            'password'       => '',
            )
    );
    $sql = 'select * from customer 
        where cust_nbr > ? and cust_nbr < ?';

    $rs = $db->query($sql)->execute(array(125000, 125200));
    // Source of confusion: this doesn't dump the array!!!
    // It dumps the object properties for Pdo\Result
    Debug::dump($rs);
    // but it is still able to iterate records directly
    // without toArray
    foreach ($rs as $row){
        Debug::dump($row);
    }

    return $this->view;
} 

#3

After long search I handel my SQL Query in ZF2 that way

经过长时间的搜索，我以这种方式在ZF2中查找我的SQL查询

 $sql = new \Zend\Db\Sql\Sql($this->tableGateway->getAdapter());
    $select = $sql->select();
    $select->from('table'); 
    $select->columns(array('*'));
    $select->join("join table", "table.id = join table.id", array("*"), "left");
    $statement = $sql->prepareStatementForSqlObject($select);
    $results = $statement->execute();
    return iterator_to_array($results));

The trick is the PHP function iterator_to_array

诀窍是PHP函数iterator_to_array

#4

You can avoid the foreach loop by doing the following:

您可以通过执行以下操作来避免foreach循环：

$statement = $db->query($sql);

/** @var $results Zend\Db\ResultSet\ResultSet */
$results = $statement->execute();

$data = $result->getResource()->fetchAll();
// Now data is an array

#5

My English is very rotten
I also encountered this problem,$returnType Defined in Zend\Db\ResultSet\ResultSet
we can give third argument for Zend\Db\Adapter\Adapter,like this

我的英文很烂我也遇到过这个问题，$ returnType在Zend \ Db \ ResultSet \ ResultSet中定义我们可以为Zend \ Db \ Adapter \ Adapter提供第三个参数，就像这样

$adapter = new Zend\Db\Adapter\Adapter($db_config,null,new Zend\Db\ResultSet\ResultSet('array'));
$re = $adapter->query('select * from mooncake', $adapter::QUERY_MODE_EXECUTE);
$s = $re->current();
var_dump($s);

now,$s is array

现在，$ s是数组