function database_example_advanced_list

Renders a filtered list of entries in the database.

Backdrop helps in processing queries that return several rows, providing the found objects in the same query execution call.

This function queries the database using a JOIN between the users and the example entries, to provide the username that created the entry; then, it creates a table with the results, processing each row.

SELECT e.pid as pid, e.name as name, e.surname as surname, e.age as age u.name as username FROM {database_example} e JOIN users u ON e.uid = u.uid WHERE e.name = 'John' AND e.age > 18

See also

db_select()

Related topics

File

modules/examples/database_example/database_example.module, line 247

Hook implementations for the Database Example module.

Code

function database_example_advanced_list() {
  $output = '';

  $select = db_select('database_example', 'e');
  // Join the users table, so we can get the entry creator's username.
  $select->join('users', 'u', 'e.uid = u.uid');
  // Select these specific fields for the output.
  $select->addField('e', 'pid');
  $select->addField('u', 'name', 'username');
  $select->addField('e', 'name');
  $select->addField('e', 'surname');
  $select->addField('e', 'age');
  // Filter only users named "John".
  $select->condition('e.name', 'John');
  // Filter only users older than 18 years.
  $select->condition('e.age', 18, '>');
  // Make sure we only get items 0-49, for scalability reasons.
  $select->range(0, 50);

  // Now, loop all these entries and show them in a table. The following line
  // could have been written as $entries = $select->execute()->fetchAll() which
  // would return each record as an object instead of an array.
  $entries = $select->execute()->fetchAll(PDO::FETCH_ASSOC);
  if (!empty($entries)) {
    $rows = array();
    foreach ($entries as $entry) {
      // Sanitize the data before handing it off to the theme layer.
      $rows[] = array_map('check_plain', $entry);
    }
    // Make a table for them.
    $header = array(t('Id'), t('Created by'), t('Name'), t('Surname'), t('Age'));
    $output .= theme('table', array('header' => $header, 'rows' => $rows));
  }
  else {
    backdrop_set_message(t('No entries meet the filter criteria (Name = "John" and Age > 18).'));
  }
  return $output;
}