function db_select

7 database.inc db_select($table, $alias = NULL, array $options = array())
8 database.inc db_select($table, $alias = NULL, array $options = array())

Returns a new SelectQuery object for the active database.

Parameters

$table: The base table for this query. May be a string or another SelectQuery object. If a query object is passed, it will be used as a subselect.

$alias: The alias for the base table of this query.

$options: An array of options to control how the query operates.

Return value

SelectQuery A new SelectQuery object for this connection.

Related topics

Database abstraction layer
Allow the use of different database servers using the same code base.
239 calls to db_select()
actions_do in includes/actions.inc
Performs a given list of actions by executing their callback functions.
AggregatorUpdatePathTestCase::testAggregatorUpdate in modules/simpletest/tests/upgrade/update.aggregator.test
Tests that the aggregator.module update is successful.
aggregator_feed_items_load in modules/aggregator/aggregator.pages.inc
Loads and optionally filters feed items.
blog_block_view in modules/blog/blog.module
Implements hook_block_view().
blog_feed_last in modules/blog/blog.pages.inc
Menu callback; displays an RSS feed containing recent blog entries of all users.

... See full list

File

includes/database/database.inc, line 2516
Core systems for the database layer.

Code

function db_select($table, $alias = NULL, array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'default';
  }
  return Database::getConnection($options['target'])->select($table, $alias, $options);
}

Comments

Posted by soyarma on

If you would like to select * use this syntax

<?php
$result = db_select('contact', 'c')
    ->fields('c')
    ->execute()
    ->fetchAssoc();
?>

Posted by naught101 on

That actually only gives SELECT contact.* from `contact`, which is equivalent, unless you're doing a join as well, I think.

Posted by redsd on

To preform a WHERE on your query use conditions, you can aply one or more conditions like the example below.

<?php
    $result = db_select('node', 'n')
    ->fields('n')
    ->condition('nid', $node->nid,'=')
    ->condition('status', 0,'>')
    ->condition('uid', array(1,5,7),'IN')
    ->execute()
    ->fetchAssoc();
?>

Posted by redsd on

To preform a more advanced query using JOIN, GROUP BY, ORDER BY and LIMIT a select could look like this:

<?php
    $query = db_select('node', 'n');

   

$query->join('users', 'u', 'n.uid = u.uid'); //JOIN node with users

   

$query->groupBy('u.uid');//GROUP BY user ID

   

$query->fields('n',array('title','created'))//SELECT the fields from node
    ->fields('u',array('name'))//SELECT the fields from user
    ->orderBy('created', 'DESC')//ORDER BY created
    ->range(0,2);//LIMIT to 2 records

   

$result = $query->execute();

    while(

$record = $result->fetchAssoc()) {
        print_r($record);
    }
?>

For more advanced queries I recommend looking here for the possibilities: http://api.drupal.org/api/drupal/includes--database--select.inc/class/Se...

Posted by Berdir on

Hint: Instead of your while loop, just write "foreach ($result as $record) {". Much simpler.

Posted by earthday47 on

Sometimes foreach() is a performance hit, so it depends on the situation.

Posted by danithaca on

The whole list of $query=db_select(...) functions:
http://api.drupal.org/api/drupal/includes--database--select.inc/class/Se...

Documentations on how to use db_select():
http://drupal.org/node/310075

The list of "fetch*()" functions:
http://api.drupal.org/api/drupal/includes--database--prefetch.inc/class/...

Posted by Dave Reid on

If you have the $query variable, then you can do something like drupal_set_message((string) $query) before you run $query->execute(). Otherwise you can download and enable the Devel module and call dpq($query).

Posted by ookami.kb on

For building a paged query, you can apply the example below:

<?php
$query = db_select('node', 'n')
      ->condition('type', 'article')
      ->fields('n')
      ->extend('PagerDefault')
      ->limit(30);
?>

Posted by devtherock on

For paging you have to write like

$query = db_select('node', 'n')
      ->condition('type', 'article')
      ->fields('n');
$query = $query->extend('PagerDefault')->limit(2);

Then somewhere before output.

$output .= theme('pager', array('tags' => array()));

Thanks
Kuldev

Posted by luco on

now it works for me! thanks! :)

Posted by ParisLiakos on

if you need joining a subquery use this:

<?php
    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->condition('priority', 1);

   

// Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test', 't');
    $select->join($subquery, 'tt', 't.id=tt.pid');
    $select->addField('t', 'name');

   

// The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    //   INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
?>

Posted by weboide on

If you need to query another database, instead of changing the active database with db_set_active(): Make sure your db settings in settings.php file contains another database, then just do:

<?php
$select = Database::getConnection('default', 'otherdb')->select('tablename', 'tblalias');
?>

Posted by bkline on

... but I'm apprehensive about the significant increase that would impose on memory usage. The correlated query checks each candidate separately, whereas the join method has to collect all the joined rows before winnowing the results down.

Thanks for the suggestion, though.

Posted by kaido24 on

This is usecase with using external database.. It took me some time to figure it out and I hope its helpful for someone.

// set external database.
db_set_active('panel');
// Start select query.
  $query = db_select('gw_route', 'g');
  $query->fields('g', array('country', 'cost'));

// Create expression (acts the same as any db value at sql) and use it.
  $query->addExpression('MIN(g.cost)', 'min_cost');

  $query->condition('g.country', '','!=');
  $query->groupBy('g.country');
  $query->orderBy('g.country', 'ASC');
  $result = $query->execute();
// Set active default database.
  db_set_active();
  while($record = $result->fetchAssoc()) {
        print_r($record);
    }

Posted by brulik67 on

Work code:

<?php
    $query = db_select('file_managed', 'fm');
    $query->join('field_data_field_image', 'fi', 'fm.fid = fi.field_image_fid');
    $query->groupBy('fm.fid');
    $query->fields('fm', array('filename', 'uri'));
    $query->fields('fi', array('field_image_width', 'field_image_height'));
    $query->condition('fi.bundle','image_for_slider','=');
    $result = $query->execute();
        while($record = $result->fetchAssoc()) {
            print_r($record);
       
    }
?>

Posted by kenorb on

Example 1:

<?php
$vid = db_select("taxonomy_vocabulary", "tv")
  ->fields("tv", array("vid"))
  ->condition("machine_name", "tags")
  ->execute()
  ->fetchField();
?>

It will return vid of taxonomy Tags.

Practical usage:

<?php
taxonomy_term_save((object) array( "name" => "New tag", "vid" => db_select("taxonomy_vocabulary", "tv")->fields("tv", array("vid"))->condition("machine_name", "tags")->execute()->fetchField(), ));
?>

It will add new term 'New tag' into Tags vocabulary.

Posted by TechNikh on

any performance gain by using db_select instead of db_query?

Posted by peterx on

db_query is faster because it skips some field processing and a bunch of hook calls. db_select is more compatible with different databases because of the separate field processing and is more compatible with other modules because of those hook calls.

Posted by agalligani on

Is there an easy way to query the cck fields when querying the node table or is it necessary to do joins for each and every field you want?

Posted by autopoietic on

You could load the whole node object with node_load. In D6 matching conditions could be added to the node_load:

http://api.drupal.org/api/drupal/modules%21node%21node.module/function/n...

In D7 it looks like you would use 'EntityFieldQuery()':

http://api.drupal.org/api/drupal/modules%21node%21node.module/function/n...

If useful, here is howto:

http://drupal.org/node/1343708

Posted by Elijah Lynn on

Note: in 90% of select query use cases you will have a static query. If in a critical performance path, you should use db_query() and friends instead of db_select() for performance reasons.

Source: http://drupal.org/node/310075