PHP db_select函数代码示例

 public function content()
 {
     $user = \Drupal\user\Entity\User::load(\Drupal::currentUser()->id());
     $userId = $user->get('uid')->value;
     $data = array(array('job_title' => '', 'job_path' => '', 'company_name' => '', 'application_date' => ''));
     $appliedJobs = db_select('user_job_application', 'uja')->condition('uja.user_id', $userId, '=')->fields('uja', array('job_id', 'date'))->orderBy('uja.date', 'DESC')->execute()->fetchAll();
     if ($appliedJobs) {
         $x = 0;
         foreach ($appliedJobs as $appliedJob) {
             $jobNode = \Drupal\node\Entity\Node::load($appliedJob->job_id);
             $jobTitle = $jobNode->getTitle();
             $jobPathAlias = \Drupal::service('path.alias_manager')->getAliasByPath('/node/' . $appliedJob->job_id);
             $companyNodeEntity = $jobNode->get('field_company');
             $companyNode = \Drupal\node\Entity\Node::load($companyNodeEntity->entity->id());
             $companyName = $companyNode->getTitle();
             $data[$x]['job_title'] = $jobTitle;
             $data[$x]['job_path'] = $jobPathAlias;
             $data[$x]['company_name'] = $companyName;
             $data[$x]['application_date'] = $appliedJob->date;
             $x++;
         }
     }
     $markUp = $this->createMarkUp($data);
     return array('#type' => 'markup', '#markup' => $markUp);
 }

/**
 * Implements hook_draw_chart_alter().
 */
function hook_draw_chart_alter(&$settings)
{
    foreach ($settings as $chart) {
        if (isset($chart['chart']['chartCategory']) && !empty($chart['chart']['chartCategory'])) {
            // Geting the count result by vocabulary machine name.
            $voc = taxonomy_vocabulary_machine_name_load('categories');
            $tree = taxonomy_get_tree($voc->vid);
            $header = array();
            foreach ($tree as $term) {
                // Feeds the header with terms names.
                $header[] = $term->name;
                $query = db_select('taxonomy_index', 'ti');
                $query->condition('ti.tid', $term->tid, '=')->fields('ti', array('nid'));
                // Feeding the terms with the node count.
                $terms[] = $query->countQuery()->execute()->fetchField();
            }
            $columns = array('Content per category');
            $rows = array($terms);
            // Replacing the data of the chart.
            $chart['chart']['chartCategory']['header'] = $header;
            $chart['chart']['chartCategory']['rows'] = $rows;
            $chart['chart']['chartCategory']['columns'] = $columns;
            // Adding a colors attribute to the pie.
            $chart['chart']['chartCategory']['options']['colors'] = array('red', '#004411');
        }
    }
}

 public function __construct($arguments)
 {
     parent::__construct($arguments);
     // Make sure we can use it for node and term only.
     if (!in_array($this->entityType, array('node', 'taxonomy_term'))) {
         throw new Exception('\\SkeletonMigration supports only nodes and terms.');
     }
     $this->description = t('Import @type - @bundle from SQL table', array('@type' => $this->entityType, '@bundle' => $this->bundle));
     $this->fields = !empty($this->fields) ? $this->fields : array();
     $sql_fields[] = '_unique_id';
     if ($this->entityType == 'node') {
         $this->addFieldMapping('title', '_title');
         $class_name = 'MigrateDestinationNode';
         $sql_fields[] = '_title';
     } elseif ($this->entityType == 'taxonomy_term') {
         $this->addFieldMapping('name', '_name');
         $class_name = 'MigrateDestinationTerm';
         $sql_fields[] = '_name';
     }
     // Rebuild the csv columns array.
     $this->fields = array_merge($sql_fields, $this->fields);
     // Create a map object for tracking the relationships between source rows
     $key = array('_unique_id' => array('type' => 'varchar', 'length' => 255, 'not null' => TRUE));
     $destination_handler = new MigrateDestinationEntityAPI($this->entityType, $this->bundle);
     $this->map = new MigrateSQLMap($this->machineName, $key, $destination_handler->getKeySchema($this->entityType));
     // Create a MigrateSource object.
     $sql_table = isset($this->sqlTable) ? '_raw_' . $this->sqlTable : '_raw_' . $this->bundle;
     $query = db_select($sql_table, 't')->fields('t')->orderBy('__id');
     $this->source = new MigrateSourceSQL($query, $this->fields);
     $this->destination = new $class_name($this->bundle, array('text_format' => 'filtered_html'));
 }

/**
 * Example function: creates a graph of user logins by day.
 */
function user_last_login_by_day($n = 40)
{
    $query = db_select('users');
    $query->addField('users', 'name');
    $query->addField('users', 'uid');
    $query->addField('users', 'created');
    $query->condition('uid', 0, '>');
    $query->orderBy('created', 'DESC');
    $query->range(0, $n);
    $result = $query->execute();
    $g = graphapi_new_graph();
    $now = time();
    $days = array();
    foreach ($result as $user) {
        $uid = $user->uid;
        $user_id = 'user_' . $uid;
        $day = intval(($now - $user->created) / (24 * 60 * 60));
        $day_id = 'data_' . $day;
        graphapi_set_node_title($g, $user_id, l($user->name, "user/" . $uid));
        graphapi_set_node_title($g, $day_id, "Day " . $day);
        graphapi_set_link_data($g, $user_id, $day_id, array('color' => '#F0F'));
    }
    $options = array('width' => 400, 'height' => 400, 'item-width' => 50, 'engine' => 'graph_phyz');
    return theme('graphapi_dispatch', array('graph' => $g, 'config' => $options));
}

 public function submitForm(array &$form, \Drupal\Core\Form\FormStateInterface $form_state)
 {
     // Check to make sure that the file was uploaded to the server properly
     $userInputValues = $form_state->getUserInput();
     $uri = db_select('file_managed', 'f')->condition('f.fid', $userInputValues['import']['fids'], '=')->fields('f', array('uri'))->execute()->fetchField();
     if (!empty($uri)) {
         if (file_exists(\Drupal::service("file_system")->realpath($uri))) {
             // Open the csv
             $handle = fopen(\Drupal::service("file_system")->realpath($uri), "r");
             // Go through each row in the csv and run a function on it. In this case we are parsing by '|' (pipe) characters.
             // If you want commas are any other character, replace the pipe with it.
             while (($data = fgetcsv($handle, 0, ',', '"')) !== FALSE) {
                 $operations[] = ['csvimport_import_batch_processing', [$data]];
             }
             // Once everything is gathered and ready to be processed... well... process it!
             $batch = ['title' => t('Importing CSV...'), 'operations' => $operations, 'finished' => $this->csvimport_import_finished(), 'error_message' => t('The installation has encountered an error.'), 'progress_message' => t('Imported @current of @total products.')];
             batch_set($batch);
             fclose($handle);
         } else {
             drupal_set_message(t('Not able to find file path.'), 'error');
         }
     } else {
         drupal_set_message(t('There was an error uploading your file. Please contact a System administator.'), 'error');
     }
 }

function db_select_all_row($sql)
{
    $result = db_select($sql);
    while ($return[] = db_fetch_row($result)) {
    }
    return $return;
}

 function testMenuLinkTitle()
 {
     $plugin = new \menu_CrumbsMultiPlugin_link_title();
     $q = db_select('menu_links', 'ml');
     $q->fields('ml', array('mlid', 'link_path', 'menu_name'));
     $titles_by_path = array();
     foreach ($q->execute() as $row) {
         $link = menu_link_load($row->mlid);
         if ($link && !isset($links_by_path[$row->link_path][$row->menu_name])) {
             $titles_by_path[$row->link_path][$row->menu_name] = $link['title'];
         }
     }
     foreach ($titles_by_path as $path => $titles) {
         $router_item = crumbs_get_router_item($path);
         if ($router_item) {
             $plugin_result = $plugin->findTitle($path, $router_item);
             if (!is_array($plugin_result)) {
                 $this->fail("Plugin result for !path should not be empty.", array('!path' => $path));
                 continue;
             }
             ksort($plugin_result);
             ksort($titles);
             $this->assertEqual($titles, $plugin_result);
         }
     }
 }

 /**
  * Create an overview of webform content.
  */
 public function contentOverview()
 {
     // @todo This needs to be removed and the view webform_webforms used instead.
     $query = db_select('webform', 'w');
     $query->join('node', 'n', 'w.nid = n.nid');
     $query->fields('n');
     $nodes = $query->execute()->fetchAllAssoc('nid');
     module_load_include('inc', 'webform', 'includes/webform.admin');
     $header = array(t('Title'), array('data' => t('View'), 'colspan' => '4'), array('data' => t('Operations'), 'colspan' => '3'));
     $rows = array();
     if (!empty($nodes)) {
         foreach ($nodes as $node) {
             $node = Node::load($node->nid);
             $rows[] = array(\Drupal::l($node->getTitle(), Url::fromRoute('entity.node.canonical', ['node' => $node->id()])), t('Submissions'), t('Analysis'), t('Table'), t('Download'), $node->access('update') ? \Drupal::l(t('Edit'), Url::fromRoute('entity.node.edit_form', ['node' => $node->id()])) : '', t('Components'), t('Clear'));
         }
     }
     if (empty($nodes)) {
         $webform_types = webform_node_types();
         if (empty($webform_types)) {
             $message = t('Webform is currently not enabled on any content types.') . ' ' . t('Visit the <a href="!url">Webform settings</a> page and enable Webform on at least one content type.', array('!url' => Url::fromRoute('webform.settings')->toString()));
         } else {
             $webform_type_list = webform_admin_type_list();
             $message = t('There are currently no webforms on your site. Create a !types piece of content.', array('!types' => $webform_type_list));
         }
         $rows[] = array(array('data' => $message, 'colspan' => 7));
     }
     $table = array('#type' => 'table', '#header' => $header, '#rows' => $rows);
     return $table;
 }

 /**
  * Get album images by $node->id().
  * @todo move function so it is easily accessible.
  */
 public function getAlbumImages($nid, $limit = 10) {
   $images = array();
   $column = isset($_GET['field']) ? \Drupal\Component\Utility\Html::escape($_GET['field']) : '';
   $sort = isset($_GET['sort']) ? \Drupal\Component\Utility\Html::escape($_GET['sort']) : '';
   $term = _photos_order_value($column, $sort, $limit, array('column' => 'p.wid', 'sort' => 'asc'));
   $query = db_select('file_managed', 'f')
     ->extend('Drupal\Core\Database\Query\PagerSelectExtender');
   $query->join('photos_image', 'p', 'p.fid = f.fid');
   $query->join('users_field_data', 'u', 'f.uid = u.uid');
   $query->join('node', 'n', 'n.nid = p.pid');
   $query->fields('f', array('uri', 'filemime', 'created', 'filename', 'filesize'));
   $query->fields('p');
   $query->fields('u', array('uid', 'name'));
   $query->condition('p.pid', $nid);
   $query->limit($term['limit']);
   $query->orderBy($term['order']['column'], $term['order']['sort']);
   $query->addTag('node_access');
   $result = $query->execute();
   foreach ($result as $data) {
     // @todo create new function to return image object.
     $images[] = photos_get_info(0, $data);
   }
   if (isset($images[0]->fid)) {
     $node = \Drupal::entityManager()->getStorage('node')->load($nid);
     $images[0]->info = array(
       'pid' => $node->id(),
       'title' => $node->getTitle(),
       'uid' => $node->getOwnerId()
     );
     if (isset($node->album['cover'])) {
       $images[0]->info['cover'] = $node->album['cover'];
     }
   }
   return $images;
 }

function dexp_layerslider_clone($slideid)
{
    $slide = db_select('{dexp_layerslider}', 'd')->fields('d')->condition('id', $slideid, '=')->execute()->fetchAssoc();
    db_insert("{dexp_layerslider}")->fields(array('name' => "Copy of " . $slide['name'], 'settings' => $slide['settings'], 'data' => $slide['data']))->execute();
    drupal_set_message("Successfully cloned.");
    drupal_goto('admin/dexp_layerslider');
}

 /**
  * {@inheritdoc}
  */
 function getExtensionTypes($extension_names)
 {
     $q = db_select('system');
     $q->condition('name', $extension_names);
     $q->fields('system', array('name', 'type'));
     return $q->execute()->fetchAllKeyed();
 }

 /**
  * Test password strength behaviors.
  */
 function testPasswordStrengthBehaviors()
 {
     global $base_url;
     // Create user with permission to create policy.
     $user1 = $this->drupalCreateUser(array('administer site configuration'));
     $this->drupalLogin($user1);
     // Create new password length policy.
     $edit = array();
     $edit['score'] = '4';
     $this->drupalPostForm('admin/config/security/password-policy/zxcvbn', $edit, t('Add policy'));
     // Get latest ID to get policy.
     $id = db_select("password_policy_zxcvbn_policies", 'p')->fields('p', array('pid'))->orderBy('p.pid', 'DESC')->execute()->fetchObject();
     // Create user with policy applied.
     $user2 = $this->drupalCreateUser(array('enforce password_policy_zxcvbn_constraint.' . $id->pid . ' constraint'));
     $uid = $user2->id();
     // Login.
     $this->drupalLogin($user2);
     // Change own password with one not very complex.
     $edit = array();
     $edit['pass'] = '1';
     $edit['current_pass'] = $user2->pass_raw;
     $this->drupalPostAjaxForm("user/" . $uid . "/edit", $edit, 'pass');
     // Verify we see an error.
     $this->assertText('Fail - The password has a score of 0 but the policy requires a score of at least 4');
     // Change own password with one strong enough.
     $edit = array();
     $edit['pass'] = 'aV3ryC*mplexPassword1nd33d!';
     $edit['current_pass'] = $user2->pass_raw;
     $this->drupalPostAjaxForm("user/" . $uid . "/edit", $edit, 'pass');
     // Verify we see do not error.
     $this->assertNoText('Fail - The password has a score of 0 but the policy requires a score of at least 4');
     $this->drupalLogout();
 }

 protected function loadFromDb()
 {
     if (module_exists('ctools')) {
         ctools_include('export');
         $result = ctools_export_load_object('ldap_authorization', 'names', array($this->consumerType));
         // @todo, this is technically wrong, but I don't quite grok what we're doing in the non-ctools case - justintime
         $server_record = array_pop($result);
         // There's no ctools api call to get the reserved properties, so instead of hardcoding a list of them
         // here, we just grab everything.  Basically, we sacrifice a few bytes of RAM for forward-compatibility.
     } else {
         $select = db_select('ldap_authorization', 'ldap_authorization');
         $select->fields('ldap_authorization');
         $select->condition('ldap_authorization.consumer_type', $this->consumerType);
         $server_record = $select->execute()->fetchObject();
     }
     if (!$server_record) {
         $this->inDatabase = FALSE;
         return FALSE;
     }
     foreach ($this->field_to_properties_map() as $db_field_name => $property_name) {
         if (isset($server_record->{$db_field_name})) {
             if (in_array($db_field_name, $this->field_to_properties_serialized())) {
                 $this->{$property_name} = unserialize($server_record->{$db_field_name});
             } else {
                 $this->{$property_name} = $server_record->{$db_field_name};
             }
         }
     }
     $this->numericConsumerConfId = isset($server_record->numeric_consumer_conf_id) ? $server_record->numeric_consumer_conf_id : NULL;
     $this->server = ldap_servers_get_servers($this->sid, NULL, TRUE);
     return TRUE;
 }

/**
 * return array of nids.
 */
function get_node_list()
{
    // choisir la table et les champs que l'on veut récupérer.
    // On peut choisir de ne récupérer que le nid et utiliser plus tard
    // la fonction drupal node_load_multiple pour charger les nodes.
    $query = db_select('node', 'n');
    $query->addField('n', 'nid');
    $query->addField('n', 'title');
    // take care of node access
    $query->addTag('node_access');
    // example join for taxonomy of custom fields :
    // $query->join('taxonomy_index', 'ti', 'n.nid = ti.nid');
    // $query->join('field_data_field_article_date', 'field_date', 'n.nid = field_date.entity_id');
    // seulement les articles publiés
    $query->condition('status', 1);
    $query->condition('type', 'article');
    //$query->condition('tid', $this->tids, 'IN');
    // order by date DESC
    $query->orderBy('created', 'DESC');
    // supprimer les doublons si il y en a.
    $query->groupBy('n.nid');
    // éxécuter réellement la requete.
    $result = $query->execute();
    $nids = array();
    foreach ($result as $row) {
        $nids[] = $row->nid;
    }
    return $nids;
}

 /**
  * Tests the deletion of all the aliases.
  */
 function testDeleteAll()
 {
     // 1. Test that deleting all the aliases, of any type, works.
     $this->generateAliases();
     $edit = array('delete[all_aliases]' => TRUE);
     $this->drupalPostForm('admin/config/search/path/delete_bulk', $edit, t('Delete aliases now!'));
     $this->assertText(t('All of your path aliases have been deleted.'));
     // Make sure that all of them are actually deleted.
     $aliases = db_select('url_alias', 'ua')->fields('ua', array())->execute()->fetchAll();
     $this->assertEqual($aliases, array(), "All the aliases have been deleted.");
     // 2. Test deleting only specific (entity type) aliases.
     $manager = $this->container->get('plugin.manager.alias_type');
     $pathauto_plugins = array('node' => 'nodes', 'taxonomy_term' => 'terms', 'user' => 'accounts');
     foreach ($pathauto_plugins as $pathauto_plugin => $attribute) {
         $this->generateAliases();
         $edit = array('delete[plugins][' . $pathauto_plugin . ']' => TRUE);
         $this->drupalPostForm('admin/config/search/path/delete_bulk', $edit, t('Delete aliases now!'));
         $alias_type = $manager->createInstance($pathauto_plugin);
         $this->assertRaw(t('All of your %label path aliases have been deleted.', array('%label' => $alias_type->getLabel())));
         // Check that the aliases were actually deleted.
         foreach ($this->{$attribute} as $entity) {
             $this->assertNoEntityAlias($entity);
         }
         // Check that the other aliases are not deleted.
         foreach ($pathauto_plugins as $_pathauto_plugin => $_attribute) {
             // Skip the aliases that should be deleted.
             if ($_pathauto_plugin == $pathauto_plugin) {
                 continue;
             }
             foreach ($this->{$_attribute} as $entity) {
                 $this->assertEntityAliasExists($entity);
             }
         }
     }
 }