Exemplo n.º 1
0
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;
}
Exemplo n.º 2
0
	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;
	}