By nadongtae on

I want to make a table which can be sorted if i click on headers . However,this sorting must be effect to all pages. For example, my database has 3 fields : id,name,date . I list it on many pages. When i click on header Id. It will show the sorted table base on Id even though i move to other pages.
How can i do it ?
Can anyone give me a simple example to do that ? Thanks for all your supporting.

Comments

josesanmartin’s picture

josesanmartin commented

Do you already have this table somewhere? If yes, perhaps the best you just create a PHP function...

Otherwise, that's the Drupal way:

Use CCK (http://drupal.org/project/cck) to build the "table". In other words: create a content type and insert the fields: "id" and "date". Make sure to have no body in the content type.

Then install Views (http://drupal.org/project/views) and create a view that shows only that very type using a "Table View".

Regards,

José San Martin

José San Martin
http://www.chuva-inc.com/

nadongtae’s picture

nadongtae commented

Thanks for your help but can you give me more details ? It will be better if you give me an obvious example.

nrambeck’s picture

nrambeck commented

Here is a function I created in a recent project. This function will output a table with sortable columns and paging navigation links. Sorting will be maintained across all pages.

function client_list() {
  $sql = 'SELECT cid, first_name, last_name, company, city FROM {clients}';
  $limit = 20;
  $header = array(
    array('data' => t('Name'), 'field' => 'last_name', 'sort' => 'asc'), 
    array('data' => t('Company'), 'field' => 'company'), 
    array('data' => t('City'), 'field' => 'city')
  );
  $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);
  }
  if (!$rows) {
    $rows[] = array(array('data' => t('No client accounts created yet.'), 'colspan' => 3));
  }
  $output .= theme('table', $header, $rows);
  $output .= theme('pager', NULL, $limit, 0);

  return $output;
}

--
Nathan Rambeck
Rambeck Group

--
Nathan Rambeck

goobermaster’s picture

goobermaster commented

I'd been trying to come up with the same result and this was exactly the kick-start I needed.

ndmaque’s picture

ndmaque commented

me too ... it was all over in minutes thanks to the wonderful nrambeck help

i can't believe i have been exploding rows the old way while this was under my nose with sort order and pager to boot!

thanks nrambeck

newbuntu’s picture

newbuntu commented

Just want to share a little hard learned experience here. In this example, the sql query doesn't have a "where" clause. If you have a "where" condition in your sql query, you may see undesired result.

In my case, I should have only one page, but pager_query() counted I have 6 pages, therefore it displayed many page markers below the table, (something like (<< first, 1, 2, 3, 4, 5, 6,... next, last >>), very annoying.

The problem is pager_query() may not count the number of page correctly if you have a where clause in your query. If you do, then you may need to provide a counter_query.

  // instead of calling this $result = pager_query($sql . $tablesort, $limit);
  // you may need to do the following:
  $count_query="select count(*) from {clients} where city='Phoenix'";
   $result = pager_query($sql . $tablesort, $limit, 0, $counter_query);

Check pager_query() API doc for more details. I scratched my head for a few hours to find out the problem.

kpbowler’s picture

kpbowler commented

Thank you very much! This code is exactly what I needed, and it was very easy to understand!

W32’s picture

W32 commented

How I can make sortable table with data from two request?
Example:

<?php
  
   // return string
   $output  = '';

   $limit = 20;
 
   // SQL query (selecting all node with articles type)
   $query = "SELECT {node}.changed, {node_revisions}.nid, {node_revisions}.title, ". 
                               "{node_revisions}.teaser, {node_type}.type ".
                   "AS typename FROM {node_revisions}, {node}, {node_type} " .
                   "WHERE ({node_type}.type = {node}.type) AND ({node}.type = 'article') ".
                        "AND ({node_revisions}.nid = {node}.nid) ".
                     "ORDER BY {node}.created DESC";
 
  // Theme table header and empty data
  $thead = array('Authors', 'Data', 'Articles');
  $tdata = array();

  // Request
  $result = pager_query( $query, $limit );
  while ($node = db_fetch_object($result))
  {
        // Finding article authors from CCK table of additional fields
        $ta = '';
        {
          // Subrequest for authors
          $subquery = "SELECT {content_field_authors}.field_authors_value ". 
                   "FROM {content_field_authors} " .
                   "WHERE ({content_field_authors}.nid = '" . $node->nid . "') ";
         
           $subresult = db_query( $subquery );
           while ($ath = db_fetch_object($subresult)) 
           {
              $ta .= '<span class="submitted">' . $ath->field_authors_value . ' </span>';
           }
         }

         // Article date
         $td = '<span class="submitted">'  . t(format_date($node->changed, 'custom', ' j.m.y ')) . '</span>';

         // Article node link
         $ts = l($node->title, 'node/'. $node->nid);

         // Add theme table row
         $tdata[] = array($ta, $td, $ts);
   }
   $output .= theme_table($thead, $tdata);

   // add pager
   $output .= theme('pager', NULL, $limit);
 
   print  $output;
?>
sercagil’s picture

sercagil commented

Did you finally get this, W32? I'm interested in sorting the table using subquery data...

Michael Phipps’s picture

Michael Phipps commented

I just thought I would mention that

'sort'=>'asc'

only appears to work when asc is in lower case, not UPPER case.

socialnicheguru’s picture

socialnicheguru commented

subscribing

http://SocialNicheGuru.com
Delivering inSITE(TM), we empower you to deliver the right product and the right message to the right NICHE at the right time across all product, marketing, and sales channels.

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented

From berdir:

TableSort now works as an Extender, so you need to convert your query
to a dynamic db_select() query.

You then need to extend it with "->extend('TableSort')" and add the
table header by calling "->orderByHeader($header)".

Some Links:

An example from core: http://api.drupal.org/api/function/poll_votes/7
Extender doc page: http://drupal.org/node/508796

--
Morris Animal Foundation