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

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

If you would like to select * use this syntax

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

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

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

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

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

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

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

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

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

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

now it works for me! thanks! :)

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

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

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

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

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

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

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

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

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.

any performance gain by using db_select instead of db_query?

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.

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?

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

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

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

Just list multiple conditions

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

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

The default is AND. To use OR use:

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

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

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

<?php
$num_active_users
= db_select('users', 'u')
  ->
fields('u')
  ->
condition('status', 1)
  ->
countQuery()
  ->
execute()
  ->
fetchField();
?>

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

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

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

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

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.

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.

This is my query:

<?php
$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?

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.

$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