function _db_fetch_paginated($sql, $args, $cluster, $shard)
{
#
# Setup some defaults
#
$page = isset($args['page']) ? max(1, $args['page']) : 1;
$per_page = isset($args['per_page']) ? max(1, $args['per_page']) : $GLOBALS['cfg']['pagination_per_page'];
$spill = isset($args['spill']) ? max(0, $args['spill']) : $GLOBALS['cfg']['pagination_spill'];
if ($spill >= $per_page) {
$spill = $per_page - 1;
}
#
# If we're using the 2-query method, get the count first
#
$calc_found_rows = !!$args['calc_found_rows'];
if (!$calc_found_rows) {
$count_sql = _db_count_sql($sql, $args);
$ret = _db_fetch($count_sql, $cluster, $shard);
if (!$ret['ok']) {
return $ret;
}
$total_count = intval(array_pop($ret['rows'][0]));
$page_count = ceil($total_count / $per_page);
}
#
# generate limit values
#
$start = ($page - 1) * $per_page;
$limit = $per_page;
if ($calc_found_rows) {
$limit += $spill;
} else {
$last_page_count = $total_count - ($page_count - 1) * $per_page;
if ($last_page_count <= $spill && $page_count > 1) {
$page_count--;
}
if ($page == $page_count) {
$limit += $spill;
}
if ($page > $page_count) {
# we do this to ensure we fetch no rows if we're asking for the
# page after the last one, else we might end up with some spill
# being returned.
$start = $total_count + 1;
}
}
#
# build sql
#
$sql .= " LIMIT {$start}, {$limit}";
if ($calc_found_rows) {
$sql = preg_replace('/^\\s*SELECT\\s+/', 'SELECT SQL_CALC_FOUND_ROWS ', $sql);
}
$ret = _db_fetch($sql, $cluster, $shard);
#
# figure out paging if we're using CALC_FOUND_ROWS
#
if ($calc_found_rows) {
$ret2 = _db_fetch("SELECT FOUND_ROWS()", $cluster, $shard);
$total_count = intval(array_pop($ret2['rows'][0]));
$page_count = ceil($total_count / $per_page);
$last_page_count = $total_count - ($page_count - 1) * $per_page;
if ($last_page_count <= $spill && $page_count > 1) {
$page_count--;
}
if ($page > $page_count) {
$ret['rows'] = array();
}
if ($page < $page_count) {
$ret['rows'] = array_slice($ret['rows'], 0, $per_page);
}
}
#
# add pagination info to result
#
$ret['pagination'] = array('total_count' => $total_count, 'page' => $page, 'per_page' => $per_page, 'page_count' => $page_count, 'first' => $start + 1, 'last' => $start + count($ret['rows']));
if (!count($ret['rows'])) {
$ret['pagination']['first'] = 0;
$ret['pagination']['last'] = 0;
}
if ($GLOBALS['cfg']['pagination_assign_smarty_variable']) {
$GLOBALS['smarty']->assign('pagination', $ret['pagination']);
}
return $ret;
}
function _db_fetch_paginated($sql, $args, $cluster, $k=null){
#
# Setup some defaults
#
$page = isset($args['page']) ? max(1, $args['page']) : 1;
$per_page = isset($args['per_page']) ? max(1, $args['per_page']) : $GLOBALS['cfg']['pagination_per_page'];
$spill = isset($args['spill']) ? max(0, $args['spill']) : $GLOBALS['cfg']['pagination_spill'];
if ($spill >= $per_page) $spill = $per_page - 1;
#
# figure out what we're dealing with
# (yes, this is a horrible hack)
#
$ret = _db_fetch(preg_replace(array('/^SELECT .* FROM/i', '/ ORDER BY .*$/'), array('SELECT COUNT(*) FROM', ''), $sql), $cluster, $k);
if (!$ret['ok']) return $ret;
$total_count = intval(array_pop($ret['rows'][0]));
$page_count = ceil($total_count / $per_page);
#
# generate limit values
#
$start = ($page - 1) * $per_page;
$limit = $per_page;
$last_page_count = $total_count - (($page_count - 1) * $per_page);
if ($last_page_count <= $spill && $page_count > 1){
$page_count--;
}
if ($page == $page_count){
$limit += $spill;
}
#
# build sql
#
$sql .= " LIMIT $start, $limit";
$ret = _db_fetch($sql, $cluster, $k);
$ret['pagination'] = array(
'total_count' => $total_count,
'page' => $page,
'per_page' => $per_page,
'page_count' => $page_count,
);
if ($GLOBALS['cfg']['pagination_assign_smarty_variable']){
$GLOBALS['smarty']->assign('pagination', $ret['pagination']);
$GLOBALS['smarty']->register_function('pagination', 'smarty_function_pagination');
}
return $ret;
}