8.2.x database.inc db_select($table, $alias = NULL, array $options = array())
8.0.x database.inc db_select($table, $alias = NULL, array $options = array())
8.1.x database.inc db_select($table, $alias = NULL, array $options = array())
7.x 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

250 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 2552
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

soyarma’s picture

If you would like to select * use this syntax


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


naught101’s picture

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

redsd’s picture

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

    $result = db_select('node', 'n')
    ->fields('n')
    ->condition('nid', $node->nid,'=')
    ->condition('status', 0,'>')
    ->condition('uid', array(1,5,7),'IN')
    ->execute()
    ->fetchAssoc();
redsd’s picture

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

    $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...

Berdir’s picture

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

earthday47’s picture

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

danithaca’s picture

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/...

Dave Reid’s picture

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).

ookami.kb’s picture

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

$query = db_select('node', 'n')
      ->condition('type', 'article')
      ->fields('n')
      ->extend('PagerDefault')
      ->limit(30);
devtherock’s picture

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

luco’s picture

now it works for me! thanks! :)

Begun’s picture

There is no need to write it like this if the extender is added when the query is first declared.

From Extenders documentation: "... Note that $query is not altered in place. The new object is returned from extend(), and if it is not saved to a variable it will be lost. ... To avoid this problem, the recommended convention for extending a Select query is to do so when the query is first declared. ..."

$query = db_select('node', 'n')->extend('PagerDefault')->extend('TableSort');
$query->fields(...);
// ...

It is only when one tries to extend an existing query that the following convention is required:

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

if you need joining a subquery use this:

    // 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
weboide’s picture

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:

$select = Database::getConnection('default', 'otherdb')->select('tablename', 'tblalias');
bkline’s picture

... 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.

kaido24’s picture

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);
    }
brulik67’s picture

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);
        
    } 
?>
domignon’s picture

The Join method returns the actual alias used for the join,check the answer from this stackexchange question.

kenorb’s picture

Example 1:

$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:

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.

TechNikh’s picture

any performance gain by using db_select instead of db_query?

peterx’s picture

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.

agalligani’s picture

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?

autopoietic’s picture

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

Elijah Lynn’s picture

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

parthshah’s picture

I have "select" query which is using AND operation in "where" clause but I am confused how to use this in db_select.

lieb’s picture

Just list multiple conditions

$record = db_select('node', 'n')
    ->fields('n', array('nid'))
    ->condition('type', 'book', '=')
    ->condition('status', 1, '=')
    ->execute()
    ->fetchAssoc();
parthshah’s picture

it seems like AND operation is simply putting multiple conditions but what if there is any OR operation with AND operation?

valor’s picture

The default is AND. To use OR use:

$or = db_or();
$or->condition();
$or->condition();
$query->condition($or);

Rahul Seth’s picture

Sometimes we want to show the records which is not exist in one table but still exist in other table, than this query may be helpful.

$query = db_select('employee', 'e');
$query->leftJoin('project', 'p', 'p.id = e.id');
$query->fields('e', array('id', 'names'));
$query->condition('p.id', NULL);
$result= $query->execute()->fetchAllKeyed(0,1);

Rob230’s picture

To do a COUNT(*) query, start with a db_select(), add any conditions() if necessary, and then call countQuery().

$num_active_users = db_select('users', 'u')
  ->fields('u')
  ->condition('status', 1)
  ->countQuery()
  ->execute()
  ->fetchField();
rajesh.vishwakarma’s picture

How can I write this query using db_select.
db_query("SELECT status,count(status) from node group by status");

rashidkhan’s picture

Use $query->addExpression('count('status')', 'status'); Use this addExpression query and change it accordingly to make it work in your specific query.

arun.kumarp’s picture

How can I convert this quesry db_select() method?
select id, name, (select count(*) from da_pages a where b.id = a.page_id) count, active from da_pages b where page_id=$arg

Gold’s picture

It is worth mentioning here that if you are trying to ->join() tables that have common field names and you're wanting to alias them you will need to add them individually with ->addField().

kumkum29’s picture

Hello,
I want to analyse in my query some fields of differents tables.
(see image).
In the first query, i get the related values with the users with a simple code :

$user_id = arg(1);
$query = db_select('field_data_field_users', 'fdfu');
$query
     ->fields('fdfu', array('entity_id'))
    ->condition('user_id', $user_id,'=')
$results = $query->execute();				

It's ok, i get the node id related to the user id. But I need have more information on the nodes. I must connect my query with a second table (menu_links).
Is it possible to create this connexion in my query ? Or Must i use a second query to realize this ? If yes how can I write this Join Query for to link a field table with another field table (e.g. 175 > node/175).
I have test with this code without success :

$user_id = arg(1);
$query = db_select('field_data_field_users', 'fdfu');
$query->join('menu_links', 'ml', 'ml.link_path = fdfu.entity_id');
// Here I test link_path & entity_id, link_path is formatted with "node/%" and entity_id with only the "id". How to compare the both values ?
$query							  
   ->condition('user_id', $user_id, '=')
   ->fields('ml' , array('language'))
   ->fields('ml' , array('link_path'))
$results = $query->execute();

Thanks for your help.

bribread22’s picture

To query all nodes and return each record as object, use the following db_select query:

<?php
$data = db_select('node', 'n')
   ->fields('n')
   ->execute()
   ->fetchAll();
?>

The fetchAll() function will help get each record in that format.

phponwebsites’s picture

This is my query:

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

it retrieved from 0 to 30 details per page. ie, offset 1.
Simillary i want to retrieve details from 1 to 30 per pager.
How to do this?

kingandy’s picture

I don't know of any way to do that, I usually just render a pager and that handles it for me. You may find more help in the support forums.

Sazae’s picture

$results = db_select('field_data_field_package_period', 'fpp')
    ->fields('fpp', array('revision_id'))
    ->condition('fpp.field_package_period_value', "fpp.field_package_period_value2", '!=')
    ->execute();

Query results is like this

SELECT fpp.revision_id AS revision_id
FROM
field_data_field_package_period fpp
WHERE  (fpp.field_package_period_value != :db_condition_placeholder_0)

However results is not working well, I think SQL is going to like fpp.field_package_period_value != "fpp.field_package_period_value2"

Is there any way or option to fix this issue?
I use db_query instead of db_select. It works of course

njbarrett’s picture

You can use ->where('fpp.field_package_period_value <> fpp.field_package_period_value2')

NarayanaRao’s picture

How can we write a query using a function like max('salary').
---> select * from emp where salary=max('salary);

Tushar Rana’s picture

BY using db_select query how we can get rowCount;

ElusiveMind’s picture