public function show() { $vars = array("name" => $this->name); //get data from DB $this->parseURLParameters(); $totalPages = $totalRows = 0; $pagingEnabled = $this->pageSize > 0; $countSQLInMainSelect = false; if ($pagingEnabled) { //use direct count if there's no distinct inside of SELECT statement //that's 10 times faster than count loaded results $countSQLType = $this->countSQLType; if (!$countSQLType) { if (in_array(get_class($this->db), array('Zend_Db_Adapter_Mysqli', 'Zend_Db_Adapter_Pdo_Mysql'))) { $countSQLType = "SQL_CALC_FOUND_ROWS"; } elseif (preg_match("/\\s((distinct)|(group by))\\s/i", $this->selectSQL)) { $countSQLType = "subselect"; } else { $countSQLType = "simple"; } } switch ($countSQLType) { case 'SQL_CALC_FOUND_ROWS': $countSQL = preg_replace("/(^\\s*)SELECT/smi", '$1SELECT SQL_CALC_FOUND_ROWS ', $this->selectSQL); $countSQLInMainSelect = true; break; case 'simple': //SELECT.+\\sFROM is greedy to replace cases with multi selects $countSQL = "SELECT COUNT(*) FROM " . preg_replace("/^\\s*SELECT.+\\sFROM\\s/smi", "", $this->selectSQL); break; case 'subselect': default: $countSQL = "select count(*) from (" . $this->selectSQL . ") a"; } if (!$countSQLInMainSelect) { $totalRows = $this->db->fetchOne($countSQL); $totalPages = ceil($totalRows / $this->pageSize); } else { $totalRows = $this->page * $this->pageSize + 1; $totalPages = $this->page + 1; } } $sql = $countSQLInMainSelect ? $countSQL : $this->selectSQL; if ($this->sortOrder && array_key_exists($this->sortOrder, $this->sortOrders)) { if (is_array($this->sortOrders[$this->sortOrder])) { $sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder][$this->sortDirection == "ASC" ? 0 : 1]; } else { $sql .= " ORDER BY " . $this->sortOrders[$this->sortOrder] . ($this->sortDirection == "ASC" ? "" : " DESC"); } } elseif ($this->defaultSortOrder) { $sql .= " ORDER BY " . $this->defaultSortOrder; } if ($totalPages == 0) { $this->page = 1; } elseif ($this->page > $totalPages) { $this->page = $totalPages; } if ($pagingEnabled) { $sql .= " LIMIT " . ($this->page - 1) * $this->pageSize . ", " . $this->pageSize; } $vars["rows"] = $this->db->fetchAll($sql); if (!$pagingEnabled) { $totalRows = count($vars["rows"]); $totalPages = ceil($totalRows / $this->pageSize); } elseif ($countSQLInMainSelect) { $totalRows = $this->db->fetchOne("SELECT FOUND_ROWS()"); $totalPages = ceil($totalRows / $this->pageSize); } $vars["totalRows"] = $totalRows; $vars["totalPages"] = $totalPages; //set URI for subcomponents(navigator, sorter) $params = $this->request->getParams(); //remove empty variables foreach ($params as $name => $value) { if (empty($value) && $value !== "0") { unset($params[$name]); } } $params['controller'] = $this->request->getControllerName(); $params['action'] = $this->request->getActionName(); if (array_key_exists($this->name . 'Page', $params)) { unset($params[$this->name . 'Page']); } $vars["pagerURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/'; //remove sorter parameters if (array_key_exists($this->name . 'Order', $params)) { unset($params[$this->name . 'Order']); } if (array_key_exists($this->name . 'Dir', $params)) { unset($params[$this->name . 'Dir']); } $vars["sorterURI"] = $this->actionController->getHelper('Url')->url($params, null, true) . '/'; $vars["page"] = $this->page; $vars["pageSize"] = $this->pageSize; $vars["sortOrder"] = $this->sortOrder; $vars["sortDirection"] = $this->sortDirection; $this->view->{$this->name} = $vars; }