Login

Making Our Report Sortable and Paged - Drupal 7 Multi-Step Form (FAPI)

In this post we are going to replace the stub code detailed in the my previous (Drupal 7 Multi-Step Form (FAPI) with Table Report). We are going to store our data in a new database table. We'll update our report so that users can sort it and scroll through pages.

We'll add a new file listing for our database table install (and unistall cleanup). I'm using the name "proto" for our example module. In Drupal 7,  you don't need to explicitly install your modules schema. Here is our proto.install listing:

<?php
 
function proto_schema() {
    $schema['folk'] = array(
      'description' => 'a simple person table.',
      'fields' => array(
          'id' => array(
              'description' => 'The primary identifier.',
              'type' => 'serial',
              'unsigned' => TRUE,
              'not null' => TRUE),
 
          'first_name' => array(
              'description' => 'first name',
              'type' => 'varchar',
              'length' => 10,
              'not null' => TRUE,
              'default' => ''),
 
          'last_name' => array(
              'description' => 'first name',
              'type' => 'varchar',
              'length' => 10,
              'not null' => TRUE,
              'default' => ''),
 
          'color' => array(
              'description' => 'color',
              'type' => 'varchar',
              'length' => 10,
              'not null' => TRUE,
              'default' => ''),
      ),
 
      'primary key' => array('id'),
   );
 
    return $schema;
}
 
/**
 * Implements hook_uninstall().
 */
function proto_uninstall() {
  drupal_uninstall_schema('proto');
 
}

Next, we'll a couple utility classes to help us name fields as our project grows:

class proto_definitions {
 
    const FIRST_NAME = 'first_name';
    const LAST_NAME = 'last_name';
    const COLOR = 'color';
    const ID = 'id';
    const FOLK_TABLE_NAME = 'folk';
}
 
class db_folk {
 
    function add($first_name, $last_name, $color) {
       $fields = array(proto_definitions::FIRST_NAME, proto_definitions::LAST_NAME,
           proto_definitions::COLOR);
 
        $insert = db_insert(proto_definitions::FOLK_TABLE_NAME)->fields($fields);
        $insert->values(array($first_name, $last_name,$color));
        $insert->execute();
    }
 }

Note! For sake of brevity I only implemented an add method for our database access class. Again, a simple pattern that allows us to isolate and name the database access routines. For a production system we would also add a lot more code that checks input and captures exceptions.

Now for our sortable table. In our previous post we added a html "markup" element to our Multi-Step input form. Therefore all we need to do to make our report reflect changes to the database is make one change in our form definition method (multi_step_form()).

// This is where we render our report
 // $folks = getCachedFolks();
  $form['report'] = array(
    '#type' => 'markup',
   // '#markup' => $folks->render_table(),
    '#markup' => drupal_render(folks_db_report()),
    '#weight' => 10,
  );

I commented out the old code, just so you can see the difference. We are replacing the temporary cache routine and adding out new report method.

Here is the source for our new report. It creates a sortable table along with a pager. Note at the end of the method how we employ a new Drupal 7 render array.

 function folks_db_report() {
 
  $header = array(
      'id' => array('data' => t('Id'), 'field' => 'u.id'),
      'first_name' => array('data' => t('First Name'), 'field' => 'u.first_name'),
      'last_name' => array('data' => t('Last Name'), 'field' => 'u.last_name'),
      'color' => array('data' => t('Favorite Color'), 'field' => 'u.color'),
  );
 
  $query = db_select('folk', 'u');
 
  $count_query = clone $query;
  $count_query->addExpression('COUNT(u.id)');
 
  $query = $query->extend('PagerDefault')->extend('TableSort');
 
  $query
          ->fields('u', array('id', 'first_name', 'last_name', 'color'))
          ->limit(10)
          ->orderByHeader($header)
          ->setCountQuery($count_query);
 
  $result = $query->execute();
 
  $rows = array();
  foreach ($result as $folk) {
    $rows[$folk->id] = array(
        'id' => toHtml('span', $folk->id),
        'first_name' => toHtml('span', $folk->first_name),
        'last_name' => toHtml('span', $folk->last_name),
        'color' => toHtml('span', $folk->color),
    );
  }
 
  $vars['header'] = $header;
  $vars['empty'] = 'Empty List - No Entries';
  $vars['rows'] = $rows;
 
  $render_array['table'] = array(
     '#theme' => 'table',
     '#rows' => $rows,    
      '#empty' => 'Empty List - No Entries',
      '#header' => $header,
  );
 
  $render_array['pager'] = array(
      '#theme' => 'pager',
  );
 
  return $render_array; 
}

Our report now looks like this:

We also changed our submit handler. We use our new db_folk->add method to add a new record to the database (and thus our report). Again, I will comment out the old code so that you can see what we changed:

case 'Submit - Complete Form' :
       /*
         $folks = getCachedFolks();
         $folks->add(folk::create()
                   ->setFirst_name($form_state['storage']['first_name'])
                    ->setLast_name($form_state['storage']['last_name'])
                    ->setColor($form_state['values']['color']));
         cache_set('FOLKS_CACHE_ID', serialize($folks), 'cache');*/
 
         $db = new db_folk();
         $db->add($form_state['storage']['first_name'],
                        $form_state['storage']['last_name'],$form_state['values']['color'] );
         drupal_goto('test/multi_step_form', array('query' =>array('sort' => 'desc','order' => 'Id')));
 
 break;    

The call to drupal_goto at the end forces our table report to display the newly added record in the first row of our html table. We are simply simulating what an end user would do by clicking on the table id column and sorting it in descending order.

Finally a small utility I added to help us prototype our project. It's a simple decorator around a basic drupal theme html element. Again, another stub we use during the development phase:

function toHtml($tag = 'p', $value = NULL, $attributes = array()) {
  $vars['element'] =
    array('#tag' => $tag,
          '#attributes' => $attributes,
          '#value' => $value,
  );
  return theme('html_tag', $vars);
}

 

Comments

Table not created

Unpublished

Hi,

I followed your example, but i keep getting:
"Base table or view not found: 1146 Table 'gen_skudb.colordb' doesn't exist"
- gen_skuDB is my data base and colordb is the table that i am trying to create ( you called it folk in your example).

Of course i tried dis/enabling and Un/installing and flushing cache, but still i get that error, and phpMyAdmin does not show that table either.

any ideas where else i should look... i googled without any luck...

Correct function names

Hi,
Please take a look at the articles first code listing "function proto_schema() {". My guess is, your schema method is not called because your function name does not match your module name.

In the article, the module's names is "proto". Therefore, when an administrator enables the module Drupal looks for a function called MODULE_NAME_schema(). Thus the function name "proto_schema()". "proto_schema()" is an implementation of hook_schema. You replace the word "hook" with your module name.

Next, it sounds like you don't have a normal development environment. You should always install Drupal on your local machine (i.e. laptop or desktop). When you develop on your local machine, you have full access to the web server and database logs. I use linux and keep a terminal open which "tails" (displays) the last 40 messages from the web server and database. Many times, those logs have a precise message as to where the application exception is.

When you develop on a remote machine without real time access to the logs, you guarantee that a simple 30 second diagnosis and fix will take hours or days.

I always use a source code debugger. Drupal is written in PHP, thus I use xdebug. I highly recommend you learn how to use a source code debugger. Trying to diagnose a simple problem like "my function is not being called" is child's play with a source code debugger. It is highly unlikely that you will find the exact source code line which is the issue through google. Drupal is completely open source, thus you can set a debugger break point anywhere you want and peruse what Drupal is doing.

To summarize, with xdebug you set a break point on your hook_schema() implementation method. You then log on to your local site as an administrator. You install and enable your module. Verify your hook_schema method is called. If called, check the server logs to see if there is an issue (perhaps you don't have the proper database permissions, the log would show that).

Hope that helps.
Jason