function Pager_Wrapper_DB(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB2_FETCHMODE_ASSOC, $dbparams = null) { $db->setFetchMode($fetchMode); if (!array_key_exists('totalItems', $pager_options)) { // be smart and try to guess the total number of records if ($countQuery = rewriteCountQuery($query)) { $totalItems = $db->getOne($countQuery, $dbparams); if (PEAR::isError($totalItems)) { return $totalItems; } } else { $res =& $db->query($query, $dbparams); if (PEAR::isError($res)) { return $res; } $totalItems = (int) $res->numRows(); $res->free(); } $pager_options['totalItems'] = $totalItems; } $pager_options['delta'] = 3; require_once 'Pager/Pager.php'; $pager =& Pager::factory($pager_options); $page = array(); $page['totalItems'] = $pager_options['totalItems']; $page['links'] = $pager->links; $page['page_numbers'] = array('current' => $pager->getCurrentPageID(), 'total' => $pager->numPages()); list($page['from'], $page['to']) = $pager->getOffsetByPageId(); if (!$disabled) { $db->setLimit($pager_options['perPage'], $page['from'] - 1); } $sth = $db->prepare($query); $res = $sth->execute($dbparams); if (PEAR::isError($sth)) { die($sth->getMessage()); } $page['data'] = array(); while ($row = $res->fetchRow()) { $page['data'][] = $row; } if ($disabled) { $page['links'] = ''; $page['page_numbers'] = array('current' => 1, 'total' => 1); } return $page; }
/** * @param object PEAR::MDB2 instance * @param string db query * @param array PEAR::Pager options * @param boolean Disable pagination (get all results) * @param integer fetch mode constant * @return array with links and paged data */ function Pager_Wrapper_MDB2(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB2_FETCHMODE_ASSOC) { if (!array_key_exists('totalItems', $pager_options)) { //be smart and try to guess the total number of records if ($countQuery = rewriteCountQuery($query)) { $totalItems = $db->queryOne($countQuery); if (PEAR::isError($totalItems)) { return $totalItems; } } else { //GROUP BY => fetch the whole resultset and count the rows returned $res = $db->queryCol($query); if (PEAR::isError($res)) { return $res; } $totalItems = count($res); } $pager_options['totalItems'] = $totalItems; } require_once 'Pager/Pager.php'; $pager = Pager::factory($pager_options); $page = array(); $page['links'] = $pager->links; $page['totalItems'] = $pager_options['totalItems']; $page['page_numbers'] = array('current' => $pager->getCurrentPageID(), 'total' => $pager->numPages()); list($page['from'], $page['to']) = $pager->getOffsetByPageId(); $page['limit'] = $page['to'] - $page['from'] + 1; if (!$disabled) { $db->setLimit($pager_options['perPage'], $page['from'] - 1); } $page['data'] = $db->queryAll($query, null, $fetchMode); if (PEAR::isError($page['data'])) { return $page['data']; } if ($disabled) { $page['links'] = ''; $page['page_numbers'] = array('current' => 1, 'total' => 1); } return $page; }
/** * Test rewriteCountQuery() with queries having keywords embedded in other words */ function testRewriteCountQuery_EmbeddedKeywords() { $query = 'SELECT afieldFROM, b, c, d FROM mytable WHERE a=1 AND c="g"'; $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"'; $this->assertEqual($expected, rewriteCountQuery($query)); $query = 'SELECT FROMafield, b, c, d FROM mytable WHERE a=1 AND c="g"'; $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"'; $this->assertEqual($expected, rewriteCountQuery($query)); $query = 'SELECT afieldFROMaaa, b, c, d FROM mytable WHERE a=1 AND c="gLIMIT"'; $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="gLIMIT"'; $this->assertEqual($expected, rewriteCountQuery($query)); $query = 'SELECT DISTINCTaaa, b, c, d FROM mytable WHERE a=1 AND c="g"'; $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"'; $this->assertEqual($expected, rewriteCountQuery($query)); //this one fails... the regexp should NOT match keywords within quotes. //we need a full blown stack-based parser to catch this... $query = 'SELECT afieldFROMaaa, b, c, d FROM mytable WHERE a=1 AND c="g LIMIT a"'; $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g LIMIT a"'; $this->assertEqual($expected, rewriteCountQuery($query)); }