Join two tables (users and userpoints_total)

Question

I'm working with Drupal Commerce, and I am currently developing a new module that displays the best customers that have a maximum points in a single block.

I want to do a join between the two tables (users and userpoints_total)

$query = db_select('users', 'u')
  ->fields('u',array('name','uid'));
$query->join('userpoints_total', 't', 't.uid = u.uid');
$results = $query->execute();

That works but in the block views I can't see anything. This is the code:

    function best_client_block_view($delta = '') {
      switch ($delta) {
        case 'best_client':
          $block['subject'] = t('Les meilleures Clients Chez nous');
          if (user_access('access content')) {
            // Use our custom function to retrieve data.
            $result = best_client_contents();
            // Array to contain items for the block to render.
            $items = array();
            // Iterate over the resultset and format as links.
        if (is_array($result) || is_object($result))
            {
            foreach ($result as $node) {

              $items[] = array(
                'data' => t($node->name),

              ); 
            }
        }
           // No content in the last week.
            if (empty($items)) {
              $block['content'] = t('No user available.');  
            } 
            else {
              // Pass data through theme function.
              $block['content'] = theme('item_list', array(
                'items' => $items));
            }
          }
        return $block;
      }

    }

How can I fix that?

Answer 1

You don't call a result set method on your query. Try $results = $query->execute()->fetchAll(); and have a look at the results via devel or something similar.

I believe that block['content'] defaults to markup but theme functions return HTML strings not renderable arrays so you could try to use the #markup key to make them render.

$block['content']['#markup'] = theme('item_list', array('items' => $items));

Or use the theme as a part of the render array.

$block['content'] = array(
  '#theme' => 'item_list', 
  '#type' => 'ul', 
  '#items' => $items
)