| 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
235 calls to db_select()
File
- includes/
database/ database.inc, line 2484 - 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
Select *
If you would like to select * use this syntax
<?php$result = db_select('contact', 'c')
->fields('c')
->execute()
->fetchAssoc();
?>
That actually only gives
That actually only gives
SELECT contact.* from `contact`, which is equivalent, unless you're doing a join as well, I think.Where
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();
?>
Advanced query
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
Hint: Instead of your while loop, just write "foreach ($result as $record) {". Much simpler.
watch out for $query.This
watch out for $query.
This won't work
<?php$query = db_select('node', 'n')
->join('users', 'u', 'n.uid = u.uid') //JOIN node with users
->groupBy('u.uid')//GROUP BY user ID
->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
?>
This WILL work
<?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
?>
Documents of the functions
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/...
Getting the string value of the query to inspect it.
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).
Paged 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
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
if you need joining a
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
?>
How to use if condition in db_select.
Hi,
How to use following mysql query in db_select format:
"SELECT DISTINCT f.company_value AS Company, IF(c.country_value = ' ', 'undefined', c.country_value) AS Country FROM company_table f
INNER JOIN country_table c ON c.entity_id = f.entity_id
ORDER BY Country"
Use the "addExpression" method
I've adapted this from another query but this should be what you're looking for...
<?php$select = db_select('company_table', 'f');
$select->distinct();
$select->addField('f', 'company_value', 'Company' );
$select->addExpression( "IF(c.country_value='', 'undefined', c.country_value)", "Country" );
$select->innerJoin('country_table', 'c', 'f.entity_id = c.entity_id');
$select->orderBy('Country');
$results = $select->execute();
?>
Best of luck!
Conditions
Hi there,
I am new in Drupal and I have a question.
How I can add "OR" statement between two conditions?
Foe example I want to run this query:
select * from tb_table
where id = 5115 or name = 'name'
I have to do something like that:
$result = db_select('tb_table', 't')
->fields('t')
->condition('id', 5115,'=')
->condition('name', 'name','=')
->execute()
but I want that result set will be returned also when one of
conditions TRUE and not only when both conditions are TRUE.
Thanks.
SELECT * FROM data WHERE age
SELECT * FROM data WHERE age >=30 OR name = 'Lady Gaga'
drupal>> db_select('data','d')
->fields('d')
->condition(db_or()
->condition('age',30,'>=')
->condition('name','Lady Gaga'))
->execute()
->fetchAll();
drupal 7 db_select tablename keyword
$results = db_select('order', 'o')
->extend('TableSort')
->orderByHeader($header)
->fields ('o', array (
'team_id',
'sku',
'pay_time',
'state',
'money',
'order_num',
))
->condition('team_id', (int) $team_id)
->execute()->fetchAll();
have something wrong , but this:
$results = db_select('order1', 'o')
->extend('TableSort')
->orderByHeader($header)
->fields ('o', array (
'team_id',
'sku',
'pay_time',
'state',
'money',
'order_num',
))
->condition('team_id', (int) $team_id)
->execute()->fetchAll();
and I changed my tablename from order to order1
that is OK.
I know that is db keyword , but how I can use orderByHeader in D7 and don't edit my db tablename.
I am from China , my English is poor.
Using SQL Functions on Where clauses
I am trying to figure out how to use LOWER() on a where clause so that it compares a column in lowercase to a string I pass in. How do I accomplish this? I have already checked the addExpression function and that only serves on the column selection side of things. Not in the conditionals.
More SQL Functions
Yeah, like the previews comment. I would like to know how to use another functions like lower(), match(), replace(), substr()
I think once you know how to use one of them, the rest should be similar?
Suggestion
This worked for me:
$arguments = array(':day' => $today);$result = db_select('tablename', 't')
->fields('t', array('field_foo_value', 'field_foo_value2'))
->where('date(field_foo_value2) > :day', $arguments)
->execute();
The Date API stores dates in varchars (PostgreSQL), hence the use of the PostgreSQL date function.
prep_statement for condition
I was hoping that something like this would work, but it seems like I have a syntax error.
->condition('table.field', ':string', '=', array(':string' => 'test'));thanks for your code snippet.
thanks for your code snippet. i was wondering how to use placeholders in case of db_select condition. thank you
date_format condition
Hi can anybody suggest me how to solve this problem.
I have condition where i have to compare form date and the date in the table. The date in the table is a timestamp and the form date is (Y-m-d) format.
i tried this way:
<?php$query->condition(FROM_UNIXTIME(timestampfieldintable,'%y%m%d'),formdatevalue,'=');
?>
But does not seem to work.
My query is somewhat like:
SELECT * FROM node WHERE FROM_UNIXTIME(created,'%Y%m%d')='20111129'Found the solution
Hi i found the solution for the above problem.
<?php$query->where('FROM_UNIXTIME(timestampfieldintable,'%Y%m%d') = :timestampfieldintable', array(':timestampfieldintable' => date("Ymd",strtotime(formdatevalue))));
?>
Drupal views way
How drupal's views makes quires with datetime:
AND (DATE_FORMAT(field_data_field_order_date.field_order_date_value, '%Y-%m-%d') = '2011-12-07')
Max(value)
How can i show the max values?
$query = db_select('data', 'n')
->fields('n', array('field1, max(field2)))
->groupBy('field1')
->execute();
Use addExpression()
addExpression
Users by a specific role name?
Hi all,
How can I implement db_select for getting users by a specific role?
E.g getting all the users whose role'name is 'authenticated user'.
Users by a specific role
Hi,
Drupal provides 2 roles by default - anonymous user and authenticated user. If you create any other role and associate it with a user, it gets saved in {users_roles} table.
1) Fetch users, who has the default 'authenticated user' role, query would be:-
SELECT u.uid, u.name
FROM users u
WHERE u.status =1
AND u.uid NOT
IN (
SELECT u.uid
FROM users u
INNER JOIN users_roles r ON u.uid = r.uid
)
which is written in a drupal way like this -
<?php
$subquery = db_select('users', 'u');
$subquery->join('users_roles', 'r', 'u.uid = r.uid');
$subquery->fields('u',array('uid'));
// Main Query
$query = db_select('users', 'u');
$query->fields('u',array('uid', 'name'));
$query->condition('u.status', 1, '=');
$query->condition('u.uid',$subquery, 'NOT IN');
$result = $query->execute();
while($record = $result->fetchAssoc()) {
print_r($record);
}
?>
2) Fetch users, belong to a specific role, query would be :-
<?php$query = db_select('users', 'u');
$query->join('users_roles', 'r', 'u.uid = r.uid'); // JOIN users with users_roles
$query->fields('u',array('uid','name')); // SELECT the fields from users
$query->fields('r',array('rid')); // SELECT the fields from users_roles
$query->condition('r.rid', 3,'='); // WHERE role is 3
$result = $query->execute();
while($record = $result->fetchAssoc()) {
print_r($record);
}
?>
Querying another database
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');
?>
Target option
Because I saw it wrong in different places I just want to point out that target is not a different database, the target is typically either "default" or "slave", indicating to use a slave SQL server if one is available. To use a different db see above comment from weboide.