| New file |
|---|
| 0,0 → 1,339 |
|---|
| <?php |
| // CVS: $Id$ |
| // |
| // Pager_Wrapper |
| // ------------- |
| // |
| // Ready-to-use wrappers for paging the result of a query, |
| // when fetching the whole resultset is NOT an option. |
| // This is a performance- and memory-savvy method |
| // to use PEAR::Pager with a database. |
| // With this approach, the network load can be |
| // consistently smaller than with PEAR::DB_Pager. |
| // |
| // The following wrappers are provided: one for each PEAR |
| // db abstraction layer (DB, MDB and MDB2), one for |
| // PEAR::DB_DataObject, and one for the PHP Eclipse library |
| // |
| // |
| // SAMPLE USAGE |
| // ------------ |
| // |
| // $query = 'SELECT this, that FROM mytable'; |
| // require_once 'Pager_Wrapper.php'; //this file |
| // $pagerOptions = array( |
| // 'mode' => 'Sliding', |
| // 'delta' => 2, |
| // 'perPage' => 15, |
| // ); |
| // $paged_data = Pager_Wrapper_MDB2($db, $query, $pagerOptions); |
| // //$paged_data['data']; //paged data |
| // //$paged_data['links']; //xhtml links for page navigation |
| // //$paged_data['page_numbers']; //array('current', 'total'); |
| // |
| |
| /** |
| * Helper method - Rewrite the query into a "SELECT COUNT(*)" query. |
| * @param string $sql query |
| * @return string rewritten query OR false if the query can't be rewritten |
| * @access private |
| */ |
| function rewriteCountQuery($sql) |
| { |
| if (preg_match('/^\s*SELECT\s+\bDISTINCT\b/is', $sql) || preg_match('/\s+GROUP\s+BY\s+/is', $sql)) { |
| return false; |
| } |
| $open_parenthesis = '(?:\()'; |
| $close_parenthesis = '(?:\))'; |
| $subquery_in_select = $open_parenthesis.'.*\bFROM\b.*'.$close_parenthesis; |
| $pattern = '/(?:.*'.$subquery_in_select.'.*)\bFROM\b\s+/Uims'; |
| if (preg_match($pattern, $sql)) { |
| return false; |
| } |
| $subquery_with_limit_order = $open_parenthesis.'.*\b(LIMIT|ORDER)\b.*'.$close_parenthesis; |
| $pattern = '/.*\bFROM\b.*(?:.*'.$subquery_with_limit_order.'.*).*/Uims'; |
| if (preg_match($pattern, $sql)) { |
| return false; |
| } |
| $queryCount = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT COUNT(*) FROM ', $sql, 1); |
| list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount); |
| list($queryCount, ) = preg_split('/\bLIMIT\b/is', $queryCount); |
| return trim($queryCount); |
| } |
| |
| /** |
| * @param object PEAR::DB instance |
| * @param string db query |
| * @param array PEAR::Pager options |
| * @param boolean Disable pagination (get all results) |
| * @param integer fetch mode constant |
| * @param mixed parameters for query placeholders |
| * If you use placeholders for table names or column names, please |
| * count the # of items returned by the query and pass it as an option: |
| * $pager_options['totalItems'] = count_records('some query'); |
| * @return array with links and paged data |
| */ |
| function Pager_Wrapper_DB(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = DB_FETCHMODE_ASSOC, $dbparams = null) |
| { |
| 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; |
| } |
| 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(); |
| |
| $res = ($disabled) |
| ? $db->limitQuery($query, 0, $totalItems, $dbparams) |
| : $db->limitQuery($query, $page['from']-1, $pager_options['perPage'], $dbparams); |
| |
| if (PEAR::isError($res)) { |
| return $res; |
| } |
| $page['data'] = array(); |
| while ($res->fetchInto($row, $fetchMode)) { |
| $page['data'][] = $row; |
| } |
| if ($disabled) { |
| $page['links'] = ''; |
| $page['page_numbers'] = array( |
| 'current' => 1, |
| 'total' => 1 |
| ); |
| } |
| return $page; |
| } |
| |
| /** |
| * @param object PEAR::MDB 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_MDB(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB_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 { |
| $res = $db->query($query); |
| if (PEAR::isError($res)) { |
| return $res; |
| } |
| $totalItems = (int)$db->numRows($res); |
| $db->freeResult($res); |
| } |
| $pager_options['totalItems'] = $totalItems; |
| } |
| 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(); |
| |
| $res = ($disabled) |
| ? $db->limitQuery($query, null, 0, $totalItems) |
| : $db->limitQuery($query, null, $page['from']-1, $pager_options['perPage']); |
| |
| if (PEAR::isError($res)) { |
| return $res; |
| } |
| $page['data'] = array(); |
| while ($row = $db->fetchInto($res, $fetchMode)) { |
| $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; |
| } |
| |
| /** |
| * @param object PEAR::DataObject instance |
| * @param array PEAR::Pager options |
| * @param boolean Disable pagination (get all results) |
| * @return array with links and paged data |
| * @author Massimiliano Arione <garak@studenti.it> |
| */ |
| function Pager_Wrapper_DBDO(&$db, $pager_options = array(), $disabled = false) |
| { |
| if (!array_key_exists('totalItems', $pager_options)) { |
| $totalItems = $db->count(); |
| $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->limit($page['from'] - 1, $pager_options['perPage']); |
| } |
| $db->find(); |
| while ($db->fetch()) { |
| $db->getLinks(); |
| $page['data'][] = $db->toArray('%s', true); |
| } |
| return $page; |
| } |
| |
| /** |
| * @param object PHP Eclipse instance |
| * @param string db query |
| * @param array PEAR::Pager options |
| * @param boolean Disable pagination (get all results) |
| * @return array with links and paged data |
| * @author Matte Edens <matte@arubanetworks.com> |
| * @see http://sourceforge.net/projects/eclipselib/ |
| */ |
| function Pager_Wrapper_Eclipse(&$db, $query, $pager_options = array(), $disabled = false) |
| { |
| if (!$disabled) { |
| require_once(ECLIPSE_ROOT . 'PagedQuery.php'); |
| $query =& new PagedQuery($db->query($query), $pager_options['perPage']); |
| $totalrows = $query->getRowCount(); |
| $numpages = $query->getPageCount(); |
| $whichpage = isset($_GET[$pager_options['urlVar']]) ? (int)$_GET[$pager_options['urlVar']] - 1 : 0; |
| if ($whichpage >= $numpages) { |
| $whichpage = $numpages - 1; |
| } |
| $result = $query->getPage($whichpage); |
| } else { |
| $result = $db->query($query); |
| $totalrows = $result->getRowCount(); |
| $numpages = 1; |
| } |
| if (!$result->isSuccess()) { |
| return PEAR::raiseError($result->getErrorMessage()); |
| } |
| if (!array_key_exists('totalItems', $pager_options)) { |
| $pager_options['totalItems'] = $totalrows; |
| } |
| |
| $page = array(); |
| require_once(ECLIPSE_ROOT . 'QueryIterator.php'); |
| for ($it =& new QueryIterator($result); $it->isValid(); $it->next()) { |
| $page['data'][] =& $it->getCurrent(); |
| } |
| require_once 'Pager/Pager.php'; |
| $pager = Pager::factory($pager_options); |
| |
| $page['links'] = $pager->links; |
| $page['totalItems'] = $pager_options['totalItems']; |
| $page['page_numbers'] = array( |
| 'current' => $pager->getCurrentPageID(), |
| 'total' => $numpages |
| ); |
| $page['perPageSelectBox'] = $pager->getperpageselectbox(); |
| list($page['from'], $page['to']) = $pager->getOffsetByPageId(); |
| $page['limit'] = $page['to'] - $page['from'] +1; |
| if ($disabled) { |
| $page['links'] = ''; |
| $page['page_numbers'] = array( |
| 'current' => 1, |
| 'total' => 1 |
| ); |
| } |
| return $page; |
| } |
| ?> |