mysql - 从 ZF2 中的 mysql 查询获取结果

Question

这是我的模型:

<?php

namespace Object\Model;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Sql;

class SiteVisitsTable {

protected $tableGateway;
protected $sql;

public function __construct(TableGateway $tableGateway) {
    $this->tableGateway = $tableGateway;
    $this->sql = new Sql($this->tableGateway->adapter);
}

public function getVisits($field = '', $value = '') {
    if ($field = '' || $value = ''):
        $rowset = $this->tableGateway->select(array($field => $value));
    else:
        $rowset = $this->tableGateway->select();
    endif;

    $row = $rowset->current();
    return $row;
}

public function getAllVisits() {
    $adapter = $this->tableGateway->adapter;

    $select = $this->sql->select();
    $select->from($this->tableGateway->getTable());
    $select->columns(array(
        new \Zend\Db\Sql\Expression('SUM(`unique`) as `unique`'),
        new \Zend\Db\Sql\Expression('SUM(`impressions`) as `impressions`'),
    ));

    $selectString = $this->sql->getSqlStringForSqlObject($select);
    $resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

    return $resultSet;
}

public function addVisit($date) {
    $currentVisits = $this->getVisits('date', $date);
    if ($currentVisits) {
        $data = array(
            'unique' => $currentVisits + 1,
        );

        $this->tableGateway->update($data, array('date' => $date));
    } else {
        $data = array(
            'unique' => 1,
            'impressions' => 1,
        );

        $this->tableGateway->insert($data);
    }
}
}

在 getAllVisits 公共(public)方法中，我有一个 SELECT CUM MySQL 查询。查询本身一切正常，但我无法打印查询中的值。这是 $resultSet 的转储:

Zend\Db\ResultSet\ResultSet Object
(
[allowedReturnTypes:protected] => Array
    (
        [0] => arrayobject
        [1] => array
    )

[arrayObjectPrototype:protected] => ArrayObject Object
    (
        [storage:ArrayObject:private] => Array
            (
            )

    )

[returnType:protected] => arrayobject
[buffer:protected] => 
[count:protected] => 1
[dataSource:protected] => Zend\Db\Adapter\Driver\Pdo\Result Object
    (
        [statementMode:protected] => forward
        [resource:protected] => PDOStatement Object
            (
                [queryString] => SELECT SUM(`unique`) as `unique`, SUM(`impressions`) as `impressions` FROM `site_visits`
            )

        [options:protected] => 
        [currentComplete:protected] => 
        [currentData:protected] => 
        [position:protected] => -1
        [generatedValue:protected] => 0
        [rowCount:protected] => 1
    )

[fieldCount:protected] => 2
[position:protected] => 
)

您可能会看到 queryString 键，当我在 phpmyadmin 中执行“原始”查询时，它会显示来自 MySQL 数据库的值，但是当尝试在 ZF2 中显示它们时，它似乎不起作用。

这是我的 Controller :

    return new ViewModel(array(
                'todayVisits' => $this->getSiteVisitsTable()->getVisits('date', date('Y-m-d')),
                'allVisits' => $this->getSiteVisitsTable()->getAllVisits(),
                'yesterdayVisits' => $this->getSiteVisitsTable()->getVisits('date', ''),
                'inactiveBlogs' => $this->getBlogsTable()->getInactiveBlogs(),
                'inactiveUsers' => $this->getUsersTable()->getUsers('is_active', '0'),
                'notes' => $this->getAdminNotesTable()->getAllNotes(),
                'form' => $form,
            ));

我的观点:

<article class="stats_overview">
<div class="overview_today">
    <p class="overview_day">Днес</p>
    <p class="overview_count"><?php echo $todayVisits->unique; ?></p>
    <p class="overview_type">посещения</p>
    <p class="overview_count"><?php echo $todayVisits->impressions; ?></p>
    <p class="overview_type">импресии</p>
</div>
<div class="overview_previous">
    <p class="overview_day">Общо</p>
    <p class="overview_count"><?php echo $allVisits->unique; ?></p>
    <p class="overview_type">посещения</p>
    <p class="overview_count"><?php echo $allVisits->impressions; ?></p>
    <p class="overview_type">импресии</p>
</div>
</article>

附言我对 JOIN 查询有同样的问题! :) 提前致谢。

Answer 1

你可以看到我的模型:

<?php

namespace Home\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;


class VisitTable extends AbstractTableGateway {

    protected $table = 'counter';


    public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Visit());

        $this->initialize();
    }



    public function sumVisit(Select $select = null) {
        if (null === $select)
            $select = new Select();
        $select->from($this->table)->columns(array(new \Zend\Db\Sql\Expression('SUM(`luongtruycap`) as `luongtruycap`')));
        $resultSet = $this->selectWith($select);
        $row = $resultSet->current();
        return $row;
    }


}

和 Controller :

$visit = $this->getVisitTable()->sumVisit();
return new ViewModel(array(
                'allVisits' => $visit->luongtruycap,

            ));

查看:

<p class="overview_count"><?php echo $this->allVisits; ?></p>

最好的问候，

吴屯