By danmc on

I would like my end result look like the project issues listing pages on this very site where it is so neat and sortable - beautiful even...

drupal.org/project/issues/

what is involved in this. I don't need it to be user submitted, I can supply all the data - it is not a dynamic thing, want to make a table with my data that is sortable by column header. Do I sound like a total clueless newb... right! Point me to correct subject to search on, I'm not lazy, just in over my head sometimes.

Thanks! danmc

⋅ Categories: Drupal 5.x

Comments

add1sun’s picture

add1sun commented

It is created using the Drupal API. You can use http://api.drupal.org/api/function/tablesort_sql/5 in the code that builds the table (look at http://api.drupal.org/api/function/theme_table/5 for building the table itself.)

You can look at a core module, like poll or path, that creates it to get a sense of it in use. (I use them as examples just because they are pretty simple implementations in core that I could find quickly. You can look for tablesort_sql in any module to see how it implements.)

Drupalize.Me, The best Drupal training, available all the time, anywhere!

add1sun’s picture

add1sun commented

Oh, and of course you can look at how project_issue module does it to actually see the example you are seeking. :-)

Drupalize.Me, The best Drupal training, available all the time, anywhere!

danmc’s picture

danmc commented

Thanks for that - I have to figure out what to do with it though. Am i using a excel sheet or a table from dreamweaver, afraid I havn't got the dynamic stuff figured out yet or even a basic idea on how to use it. I have started the apress - prodrupal dev book - but the second chapter jumped way over my head...

add1sun’s picture

add1sun commented

Hm, yeah this is dynamic, not regular HTML tables. Basically the use is to make a query to the database and then use those results to dynamically build the rows for the table. I don't know of a way to make a table sortable without PHP and DB unless there is some sort of JavaScrit magic that can do it (and I have nooo idea if there is.) I know pretty much nothing about JS though so I'm no help to you.

Drupalize.Me, The best Drupal training, available all the time, anywhere!

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Here's a simple example that reads the accesslog table and produces sortable columns:

<?php
  $how_many = 50; 
  $rows = array();

  echo '<p>Click on a column title to sort by that column.</p>';
  $header = array(
    array('data' => 'Page', 'field' => 'title'),
    array('data' => 'Member', 'field' => 'name', 'sort' => 'asc'),
    array('data' => 'When', 'field' => 'timestamp', 'sort' => 'asc'),
    );

  $sql = 'SELECT l.title, l.path, u.name , l.timer, l.timestamp FROM {accesslog} l JOIN {users} u USING(uid) WHERE uid<>1';
  $sql .= tablesort_sql($header);
  $result = pager_query($sql, $how_many);

  while ($log = db_fetch_array($result)) {
    $name = empty($log['name']) ? 'Visitor' : $log['name'];
    $page = empty($log['title']) ? 'Home' : $log['title'];
    $rows[] = array(
      $page,
      $name,
      date('Y/m/d H:i a', $log['timestamp']),
     );
  }
  echo theme('table', $header, $rows);
  echo theme('pager', $how_many);
?>

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Since I wrote this piece of code, I found this http://drupal.org/node/45361. I bet the two ideas can be merged to make a very useful snippet.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

danmc’s picture

danmc commented

looks like I will need to understand some php and sql to make this work - and how to load the data into the database...

For the simple use example above, I've tried creating a node with the code in it but haven't got it to do anything so I'm guessing I may need to tell it some info about my database or/and it just isn't that easy and may have to put the code somewhere else. or - I'm using phpmyadmin to look at my existing tables and I don't see one named access_logs so maybe thats my first error?

themphill’s picture

themphill commented

That code works for me without any modification; I simply put it into a page whose input format was set to PHP. The table it pulls from is named "accesslog," which is created by the core - optional module "statistics."

danmc’s picture

danmc commented

I get these errors when trying to make the sample code work - I don't want to go further if I have something messed up this early on. This is entered as php, with rich text editor turned off. I'm lost

* user warning: Column 'uid' in where clause is ambiguous query: SELECT COUNT(*) FROM accesslog l JOIN users u USING(uid) WHERE uid<>1 in /home/clubtaco/public_html/upvbc/includes/database.mysql.inc on line 172.
* user warning: Column 'uid' in where clause is ambiguous query: SELECT l.title, l.path, u.name , l.timer, l.timestamp FROM accesslog l JOIN users u USING(uid) WHERE uid<>1 ORDER BY timestamp ASC LIMIT 0, 50 in /home/clubtaco/public_html/upvbc/includes/database.mysql.inc on line 172.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I keep forgetting that MySql 4.x is not as smart as 5.x. Change the line:

  $sql = 'SELECT l.title, l.path, u.name , l.timer, l.timestamp FROM {accesslog} l JOIN {users} u USING(uid) WHERE uid<>1';

to:

  $sql = 'SELECT l.title, l.path, u.name , l.timer, l.timestamp FROM {accesslog} l JOIN {users} u USING(uid) WHERE u.uid<>1';

BTW, later today you can have the ability to show the contents of any database table in a sortable table, without having to do any coding or snippet pasting. It is summarized at http://drupal.org/node/205347.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

danmc’s picture

danmc commented

I'm getting it! Thanks so much. I may never html a table again... kidding.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Theme_table is so easy to use I rarely build my own anymore. It even gives you alternating colors for the rows if you set the CSS up to do so.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

david@thrale.com’s picture

david@thrale.com commented

I tried this but got a big problem. Using the example at http://drupal.org/node/205347, firstly I had to change the table name from "blocks" to "drupal_blocks". Despite finding the table and showing data, this - above the correctly display sortable table, gave an error message "You must supply a valid database table name". However, the error message displayed was displayed (sorry for the bad description) like a my website within a iframe in my website.

When I submit (save) this page, then have the problem that all the drupal edit button's disappear, so the only way of getting rid of this page is directly in phpmyadmin and deleting the node there. Any ideas?

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I tested it on D5.5 with MySql 5.0 and had no trouble with it. It is a simple PHP-formatted page and shouldn't cause any problems.

Yes, you would have to change the table name to one that matches your site; in this case you appear to have a table prefix which would have to be included. The error message is a standard "drupal_set_message" which will be shown as a box at the top of the page. This is normal.

I have had problems with Edit buttons disappearing when using the Meta-Tags (nodewords) module. There is an unresolved issue on it.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

themphill’s picture

themphill commented

Likewise, I'm very excited with the possible applications of this technique. My municipal government site has lots of information which is best presented in tabular form, and it's a great advantage to allow the user to sort it different ways. In a production environment, what front-end tool might be best to create the database tables to pull from? Typically, my data would come from tab-separated text.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I believe PhpMyAdmin will import a tab-separated table. It definitely should import an Excel table.

If you want to write some code, here's an example from the Spam_Tokens module:

function spam_tokens_import_submit($form_id, $form_values) {
  // First, turn new lines into commas.
  $input = str_replace("\n", ',', $form_values['token_list']);
  $input = str_replace("\r", ',', $input);

  // Now, turn spaces into commas.
  $input = str_replace(' ', ',', $input);

  // Now, remove extra commas.
  $input = str_replace(',,', ',', $input);
  $input = str_replace(',,', ',', $input);
  $input = str_replace(',,', ',', $input);
  
  // Finally, split it apart.
  $token_list = explode(',', $input);
  $now = time();
  
  foreach ($token_list as $token) {
    $token = strtolower($token);
    $result = db_query("INSERT INTO {spam_tokens} (token, last) VALUES('%s', %d) ON DUPLICATE KEY UPDATE last=%d", $token, $now, $now);
    if ($result != FALSE) { drupal_set_message(t('Added') .' '. $token); }
  }
  return 'admin/settings/spam/tokens';
}

The Quotes module also imports tab-separated data for another example.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru