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));
}
