8.5.x database.inc db_select($table, $alias = NULL, array $options = [])
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())
8.2.x database.inc db_select($table, $alias = NULL, array $options = array())
8.3.x database.inc db_select($table, $alias = NULL, array $options = [])
8.4.x database.inc db_select($table, $alias = NULL, array $options = [])
8.6.x database.inc db_select($table, $alias = NULL, array $options = [])
7.x database.inc db_select($table, $alias = NULL, array $options = array())

Returns a new SelectQuery object for the active database.


$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

107 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.
book_get_books in modules/book/book.module
Returns an array of all books.
book_menu_subtree_data in modules/book/book.module
Gets the data representing a subtree of the book hierarchy.
book_update_bid in modules/book/book.module
Updates the book ID of a page and its children when it moves to a new book.

... See full list


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


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


soyarma’s picture

If you would like to select * use this syntax

$result = db_select('contact', 'c')

naught101’s picture

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

omrmankar’s picture

@soyarma For Fetech All rows

$result = db_select('contact', 'c')
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')
    ->condition('nid', $node->nid,'=')
    ->condition('status', 0,'>')
    ->condition('uid', array(1,5,7),'IN')
kingandy’s picture

Note that fetchAssoc() only fetches one row from the result set (in associative array format rather than the object that fetch() returns).

fetchAll() fetches all the results, as an array of objects. fetchAllAssoc() does the same but as an associative array - that is to say, still an array of objects, but the array is keyed by a property of the result (eg fetchAllAssoc('nid') will return an array keyed by the 'nid' field of your result set but the rows themselves are still objects.) To get the results as an array of arrays, use the same functions but supply PDO::FETCH_ASSOC as an argument:
$results = $query->fetchAll(PDO::FETCH_ASSOC);
$results = $query->fetchAllAssoc('nid', PDO::FETCH_ASSOC);

joseonate’s picture

Thank you. Small thing that makes a huge difference in convenience.

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()) {

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:

Documentations on how to use db_select():

The list of "fetch*()" functions:

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')
devtherock’s picture

For paging you have to write like

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

Then somewhere before output.

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


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');
// ...

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')
$query = $query->extend('PagerDefault')->limit(2);
kingandy’s picture

Right, the point is that you can't just chain db_select() all the way through extend() to execute() because extend() doesn't return the right kind of object.

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.
// 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->orderBy('g.country', 'ASC');
  $result = $query->execute();
// Set active default database.
  while($record = $result->fetchAssoc()) {
brulik67’s picture

Work code:

    $query = db_select('file_managed', 'fm');
    $query->join('field_data_field_image', 'fi', 'fm.fid = fi.field_image_fid');
    $query->fields('fm', array('filename', 'uri'));
    $query->fields('fi', array('field_image_width', 'field_image_height'));
    $result = $query->execute();
        while($record = $result->fetchAssoc()) {
domignon’s picture

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

kingandy’s picture

$query = db_select('file_managed', 'fm');
$fi_alias = $query->join('field_data_field_image', 'fi', 'fm.fid = %alias.field_image_fid');
$query->fields('fm', array('filename', 'uri'));
$query->fields($fi_alias, array('field_image_width', 'field_image_height'));
$query->condition($fi_alias . '.bundle', 'image_for_slider', '=');
$result = $query->execute();
foreach ($result as $record) {

TBF the joined alias will almost certainly be 'fi', because this code doesn't allow anything else to step in and alter the query before the join is added. (It's usually only different if another table is using that alias in the query, in which case it adds an identifier to the end.) But using the returned alias is still a good habit to get into.

Most of these methods can also be chained ($query->fields()->condition()->execute()) but that's enough for now

kenorb’s picture

Example 1:

$vid = db_select("taxonomy_vocabulary", "tv")
  ->fields("tv", array("vid"))
  ->condition("machine_name", "tags")

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:


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


If useful, here is howto:


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, '=')
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();

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')
  ->condition('status', 1)
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

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');
     ->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 ?
   ->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:

$data = db_select('node', 'n')

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

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", '!=')

Query results is like this

SELECT fpp.revision_id AS revision_id
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

omrmankar’s picture

$result = db_select('contact', 'c')

gulshan kumar’s picture

Hi to all i am new to drupal . Actually i created a module . This module creates a table in database after being installed and create two text boxes in the admin that inserts data to that table. what i want to do, after inserting data to database i want to show that data in the textboxes that were used to insert that data . I want to show only last inserted row in the text boxes. I am using drupal 8.3.2 version
Thanks in advance