20

For Drupal 6 you could do something like this:

$header = array(
  array('data' => t('Order id'), 'field' => 'order_id'),
  ...
  array('data' => t('Transaction time'), 'field' => 'payment_time', 'sort' => 'desc'),
);
$sql = "...";
$sql .= tablesort_sql($header);
$limit = 25;
$result = pager_query($sql, $limit);
...

I took a look and for drupal 7 and both pager_query and tablesort_sql is now gone. It seems that instead the PagerDefault class can be used to create a pager query using DBTNG. I wasn't able to find any clues on a simple API for attaching a sortable table to the query like it is done in Drupal 6.

So how do you create a sortable table with a pager pulling data from a custom table?

8

You use the so called extenders. In your case, the code would be similar to the following one.

$header = array(
  array('data' => t('Order id'), 'field' => 'order_id'),
  // ...
  array('data' => t('Transaction time'), 'field' => 'payment_time', 'sort' => 'desc'),
);

// Initialize $query with db_select().
$query = db_select('your table name');

// Add the fields you need to query.
// ... 

// Add the table sort extender.
$query = $query->extend('TableSort')->orderByHeader($header);

// Add the pager.
$query = $query->extend('PagerDefault')->limit(25);
$result = $query->execute();

See HowTo: Convert a module to DBTNG, Dynamic queries: Table sorting and Extenders.

  • Don't forget to add the actual pager in your output: // Build table. $output = theme('table', array('header' => $header, 'rows' => $rows, 'empty' => t('No strings available.'))); // Add the pager. $output .= theme('pager'); – kbrinner Oct 14 '15 at 17:49
6

Use the TableSort and PagerDefault extenders.

$query = db_select('node', 'n');
$query->fields('n', array('nid', 'title', 'status'));

$table_sort = $query->extend('TableSort') // Add table sort extender.
  ->orderByHeader($header); // Add order by headers.

$pager = $table_sort->extend('PagerDefault')
  ->limit(5); // 5 rows per page.

$result = $pager->execute();
| improve this answer | |
2

Use the DataTables module.

The DataTables module integrates the jQuery plugin DataTables into Drupal as a views style and a callable theme function. DataTables lets you add dynamic features to tables, including:

  • Variable length pagination
  • On-the-fly filtering
  • Sorting with data type detection
  • Smart handling of column widths
  • Themeable by CSS
  • And more to come...
| improve this answer | |
  • I'd advise against adding a module to do something that's part of the DB API and can be done with about 50 or fewer lines of custom code. – Agi Hammerthief Mar 28 '14 at 14:36
0

You can just include the same Drupal 6 tablesort_sql in your code and it works fine.

For pager:

$count = <Total No. of Table rows>

$sql = "...";
$sql .= tablesort_sql($header);
$limit = 25; //Pager limit

$results = db_query( $sql );
$rows = array();
//Loop through the result.
while ( $row = $results->fetchAssoc() ) {
$rows = <Get your array values for Table row>
}
$current_page = pager_default_initialize($count, $limit);
$chunks = array_chunk($rows,$limit, TRUE);
$output = theme( 'table', array( 'header' => $headers, 'rows' => $chunks[$current_page] ) );
$output .= theme( 'pager', array('quantity',$count ) );
print $output;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.