PHP Zend_Db_Adapter_Abstract::fetchAssoc方法代码示例

 /**
  * Check DB connection
  *
  * @return bool
  */
 public function hasConnection()
 {
     if (!$this->_read) {
         return false;
     }
     $tables = $this->_read->fetchAssoc('show tables');
     if (!isset($tables[$this->_sessionTable])) {
         return false;
     }
     return true;
 }

 public function loadGroupByRelationValues($fieldname, $condition, $countValues = false)
 {
     if ($condition) {
         $condition = "WHERE " . $condition;
     }
     if ($countValues) {
         if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) {
             $query = "SELECT dest as `value`, count(DISTINCT src_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         } else {
             $query = "SELECT dest as `value`, count(*) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         }
         $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
         $query .= " AND src IN (" . $subquery . ") GROUP BY dest";
         OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
         $result = $this->db->fetchAssoc($query);
         OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
         return $result;
     } else {
         $query = "SELECT dest FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
         $subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
         $query .= " AND src IN (" . $subquery . ") GROUP BY dest";
         OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
         $result = $this->db->fetchCol($query);
         OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
         return $result;
     }
 }

    /**
     * @param $sqlRelevanceField
     * @param $sqlPriceField
     * @param $sqlNameField
     * @param $sqlDescriptionField
     * @param $sqlFrom
     * @param $sqlCategoryFilter
     * @param $sqlTranslation
     * @param $sqlWhere
     * @param $sqlHaving
     * @return array
     * @throws Enlight_Exception
     */
    public function getSearchExecuteQuery($sqlRelevanceField, $sqlPriceField, $sqlNameField, $sqlDescriptionField, $sqlFrom, $sqlCategoryFilter, $sqlTranslation, $sqlWhere, $sqlHaving)
    {
        $sql = '
            SELECT
                a.id as `key`, a.id as articleID, ' . $sqlRelevanceField . ' as relevance, ' . $sqlPriceField . ' as price, a.supplierID,
                a.datum, d.sales as sales, ' . $sqlNameField . ' as name, ' . $sqlDescriptionField . ' as description, ai.img as image, ai.extension,
                IFNULL((SELECT CONCAT(ROUND(AVG(points), 2), \'|\', COUNT(*)) as votes FROM s_articles_vote WHERE active=1 AND articleID=a.id), \'0.00|0\') as vote

            ' . $sqlFrom . '

            JOIN s_articles_details d
            ON a.id=d.articleID
            AND d.kind=1

            JOIN s_categories c
            ON c.id=' . $sqlCategoryFilter . '

            JOIN s_categories c2
            ON c2.active=1
	        AND c2.left >= c.left
	        AND c2.right <= c.right

            JOIN s_articles_categories ac
            ON ac.articleID=a.id
	        AND ac.categoryID=c2.id

            JOIN s_core_tax t
            ON a.taxID = t.id

            LEFT JOIN s_articles_img AS ai
            ON ai.articleID=a.id
            AND ai.article_detail_id IS NULL
            AND ai.main = 1

            ' . $sqlTranslation . '

            WHERE a.active=1

            AND (
                SELECT articleID
                FROM s_articles_avoid_customergroups
                WHERE articleID = a.id AND customergroupID = ' . $this->requestShopCustomerGroupId . '
            ) IS NULL

            ' . $sqlWhere . '

            ' . $sqlHaving . '

            ORDER BY (' . $sqlRelevanceField . ') DESC, a.id
        ';

        try {
            $result = $this->database->fetchAssoc($sql);
        } catch (PDOException $e) {
            throw new Enlight_Exception($e->getMessage());
        }
        return $result;
    }

 /**
  * Test the Adapter's fetchAssoc() method.
  */
 public function testAdapterFetchAssoc()
 {
     $id = $this->getResultSetKey('id');
     $table = $this->getIdentifier(self::TABLE_NAME);
     $result = $this->_db->fetchAssoc('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id DESC', array('2006-01-01'));
     foreach ($result as $idKey => $row) {
         $this->assertEquals($idKey, $row[$id]);
     }
 }

 public function updateOneshot($minutes = 60, $interval = 300)
 {
     $last_update = $this->_read->fetchOne("SELECT UNIX_TIMESTAMP(MAX(add_date)) FROM {$this->_summaryTable} WHERE type_id IS NULL");
     $next_update = $last_update + $interval;
     if (time() >= $next_update) {
         $stats = $this->_read->fetchAssoc("SELECT\n                                            u.visit_time,\n                                            v.visitor_id,\n                                            c.customer_id,\n                                            ROUND( (UNIX_TIMESTAMP(u.visit_time) - UNIX_TIMESTAMP(" . now() . " - INTERVAL {$minutes} MINUTE )) / {$interval} )  as _diff,\n                                            COUNT(DISTINCT(v.visitor_id)) as visitor_count,\n                                            COUNT(DISTINCT(c.customer_id)) as customer_count\n                                        FROM\n                                            {$this->_urlTable} u\n                                        LEFT JOIN {$this->_visitorTable} v ON(v.visitor_id = u.visitor_id)\n                                        LEFT JOIN {$this->_customerTable} c on(c.visitor_id = v.visitor_id)\n                                        WHERE\n                                            UNIX_TIMESTAMP(u.visit_time) > {$next_update}\n                                        group by _diff");
         foreach ($stats as $stat) {
             $data = array('type_id' => new Zend_Db_Expr('NULL'), 'visitor_count' => $stat['visitor_count'], 'customer_count' => $stat['customer_count'], 'add_date' => $stat['visit_time']);
             $this->_write->insert($this->_summaryTable, $data);
         }
     }
 }

 /**
  * Load the actions into memory from the database (and cache them)
  */
 private function _getActionsDb()
 {
     try {
         $rows = $this->_db->fetchAssoc("SELECT * FROM gems__log_setup ORDER BY gls_name");
     } catch (\Exception $exc) {
         $rows = array();
         $this->_warn();
     }
     $output = array();
     foreach ((array) $rows as $row) {
         $output[$row['gls_name']] = $row;
     }
     // \MUtil_Echo::track($output);
     $this->_cache->save($output, $this->_cacheId, array('accesslog_actions'));
     return $output;
 }