function db_select

You are here

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

244 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! :)

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