0

I just created a data table based on a query and displayed it successfully using theme_table().

Now, I'd like to add some filters to the table but have no idea how to proceed.

Is there a built-in feature that allow me to do this easily, or should I manually add a form and update the query/redisplay the results each time the user selects something?

Thanks for your help!

Frank Parent
  • 2,136
  • 19
  • 33

1 Answers1

2

I think you want to use pager_query and tablesort_sql: it's especially made for creating tables of data with pagination and sorting capabilities (and themes usually theme such tables nicely out of the box).

Example:

<?php
    // The regular query without sorting or pagination parameters
    $sql = 'SELECT cid, first_name, last_name, company, city FROM {clients}';

    // Number of rows per page
    $limit = 20;

    // List of table columns ("field" is the matching database column from the sql query)
    $header = array(
        array('data' => t('Name'), 'field' => 'last_name', 'sort' => 'asc'),
        array('data' => t('Company'), 'field' => 'company'),
        array('data' => t('City'), 'field' => 'city')
    );

    // Calculates how to modify the SQL query according to the current pagination and sorting settings
    // Then performs the database query
    $tablesort = tablesort_sql($header);
    $result = pager_query($sql . $tablesort, $limit);
    $rows = array();
    while ($client = db_fetch_object($result)) {
        $rows[] = array(l($client->last_name.', '.$client->first_name, 'client/'.$client->cid), $client->company, $client->city);
    }

    // A message in case no results were found
    if (!$rows) {
        $rows[] = array(array('data' => t('No client accounts created yet.'), 'colspan' => 3));
    }

    // Then you can pass the data to the theme functions
    $output .= theme('table', $header, $rows);
    $output .= theme('pager', NULL, $limit, 0);

    // And return the HTML output
    print $output;
?>

(I added comments, but the original version of the example comes from this page)

Alternatively, maybe you don't need to make a module at all if you're just trying to make a page that displays a list of data, you may prefer using the Views module.

wildpeaks
  • 7,273
  • 2
  • 30
  • 35
  • Thanks a lot for the feedback, great article. Right now, I'm really struggling with the filtering part though. I'd like to reproduce something like the exposed filters of the Views, but with some code instead. The reason is that I'm a beginner with a tight deadline and still haven't figured out how to use hook_views_data =( – Frank Parent Nov 05 '10 at 04:16
  • Quick note by the way: make sure not to have a WHERE clause in the sql query you're using for the pager_query or you may get undesirable results – wildpeaks Nov 05 '10 at 05:13
  • Also, if you query is becoming quite large, you could create a view in the database (I mean a view such as http://dev.mysql.com/doc/refman/5.0/en/create-view.html or http://www.postgresql.org/docs/8.3/interactive/sql-createview.html), that way it may be easier to create subsequent filtering queries on that view given it acts like a regular table. – wildpeaks Nov 05 '10 at 05:47