Use this function instead of pager_query() when selecting a subset of records from a Chado table.

@returns A database query result resource or FALSE if the query was not executed correctly

Parameters

$query: The SQL statement to execute, this is followed by a variable number of args used as substitution values in the SQL statement.

$args: The array of arguments for the query. They keys are the placeholders

$limit: The number of query results to display per page.

$element: An numeric identifier used to distinguish between multiple pagers on one page.

$count_query: An SQL query used to count matching records.

Related topics

File

tripal_core/api/tripal_core.chado_query.api.inc, line 1622

Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Code

function chado_pager_query($query, $args, $limit, $element, $count_query = '') {
  // get the page and offset for the pager
  $page_arg = isset($_GET['page']) ? $_GET['page'] : '0';
  $pages = explode(',', $page_arg);
  $page = 0;
  if (count($pages) >= $element) {
    $page = key_exists($element, $pages) ? $pages[$element] : 0;
  }
  $offset = $limit * $page;
  $q = $_GET['q'];

  // Construct a count query if none was given.
  if (!isset($count_query)) {
    $count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), 
    array('SELECT COUNT(*) FROM ', ''), $query);
  }

  // We calculate the total of pages as ceil(items / limit).
  $results = chado_query($count_query, $args);
  if (!$results) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    "chado_pager_query(): Query failed: %cq", array('%cq' => $count_query));
    return;
  }
  $total_records = $results->fetchField();

  // set a session variable for storing the total number of records
  $GLOBALS['chado_pager'][$q][$element]['total_records'] = $total_records;

  pager_default_initialize($total_records, $limit, $element);

  $query .= ' LIMIT ' . (int) $limit . ' OFFSET ' . (int) $offset;
  $results = chado_query($query, $args);
  return $results;
}