Displaying SQL-table as a paginated list

Question

I'm trying the following and it works:

my.module:

   function my_menu() {
      $items['page_example'] = array(
        'title' => 'Page Example',
        'description' => 'Page Example',
        'page callback' => 'page_example',
        'access callback' => TRUE,
        'file' => 'my.page.inc',
        'file path' => drupal_get_path('module', 'my'),
        'type' => MENU_CALLBACK,
      );
     return $items;
   }

my.page.inc:

function page_example() {
  return array(
    'page_example' => array(
      '#type' => 'markup',
      '#markup' => generate_list(),
    ),
  );
}

function generate_list() {
  $query = db_select('users', 'name')
    ->extend('PagerDefault')
    ->fields('name', array('Name'))
    ->orderBy('created', 'DESC')
    ->limit(20);

  $result = $query->execute();
  $output = '';
  foreach ($result as $row) {
    $output .= $row->name.'<br/>';
  }

  $output .= theme('pager');
  return $output;
}

But then I try prepending a uid-field to each row and it fails:

  $query = db_select('users', 'name', 'uid')
    ->extend('PagerDefault')
    ->fields('name', array('Name'), 'uid', array('Id'))
    //->addField('uid', 'User Id')
    //->addField('name', 'User Name')
    ->orderBy('created', 'DESC')
    ->limit(20);

  $result = $query->execute();
  $output = '';
  foreach ($result as $row) {
    $output .= $row->uid.': '.$row->name.'<br/>';
  }

With the error:

Recoverable fatal error: Argument 3 passed to db_select() 
must be an array, string given, called in my.page.inc 
on line 13 and defined in function db_select() 
(line 2387 in includes/database/database.inc).

UPDATE:

On Camsoft's comment (thank you) I've modified the code to:

  $query = db_select('users')
    ->extend('PagerDefault')
    ->fields('name', array('Name'), 'uid', array('Id'))
    //->addField('uid', 'User Id')
    //->addField('name', 'User Name')
    ->orderBy('created', 'DESC')
    ->limit(20);

And now it fails with

DOException: SQLSTATE[42P01]: Undefined table: 7 ERROR: 
missing FROM-clause entry for table "name" at character 8: 
SELECT name.Name AS Name, created AS created FROM {users} 
users ORDER BY created DESC LIMIT 20 OFFSET 0; Array ( ) 
in function PagerDefault->execute() (line 80 includes/pager.inc).

I'm just trying to figure out how to add a 2nd SQL-table field to a paginated db_select... Please do not suggest a "View" to me, because I'm trying to understand DBTNG at the moment.

Answer 1

The call to db_select() and the following calls to methods are wrong.

• The second parameter of db_select() is a table alias, which should then used when referring to the fields used by the query.
• the parameters passed to the fields() is wrong; see the following snippet for an example of how the call to db_select() should be.

$result = db_select('file_usage', 'f')
   ->fields('f', array('module', 'type', 'id', 'count'))
   ->condition('fid', $file->fid)
   ->condition('count', 0, '>')
   ->execute();

Your call to db_select() should be re-written as:

$query = db_select('users', 'u')
  ->fields('u', array('name', 'uid'))
  ->orderBy('created', 'DESC')
  ->limit(20)
  ->extend('PagerDefault');

Answer 2

Could you not just use the Views module to do this?

When creating a view just select User from the View Type list. It's a lot easier than doing this programmatically.

Update

---

According to the documentation for db_select the third paramter is supposed to be an $options array.

db_select($table, $alias = NULL, array $options = array())

The clue is in the error message:

Recoverable fatal error: Argument 3 passed to db_select() must be an array, string given, called in my.page.inc

You code passes the string uid as the third parameter.