<?php// Create an object of type SelectQuery and directly // add extra detail to this query object: a condition, fields and a range$query = db_select('users', 'u') ->condition('u.uid', 0, '<>') ->fields('u', array('uid', 'name', 'status', 'created', 'access')) ->range(0, 50); //Execute the query $result = $query->execute(); ?>
There are two main methods that apply for all conditional objects:
Thecondition()method allows for adding a standard$field $value $operatorformat of conditional fragment. That includes any case where the condition is a binary comparison such as=,<,>=,LIKE, etc. If no operator is specified,=is assumed. That means the most common case would becondition('myfield', $value), which results in a conditional fragment ofmyfield = :value, where:valuewill be replaced with$valuewhen the query is run.
Thewhere()method allows for the addition of arbitrary SQL as a conditional fragment.$snippetmay contain any legal SQL fragment, and if it has variable content it must be added using a named placeholder. The$argsarray is an array of placeholders and values that will be substituted into the snippet. It is up to the developer to ensure that the snippet is valid SQL. No database-specific modifications are made to the snippet.
To join against another table, use the join(), innerJoin(), leftJoin(), or rightJoin() methods, like so:
Any query may have a corresponding "count query". The count query returns the number of rows in the original query. To obtain a count query, use the countQuery() method.
<?php $count_query = $query->countQuery();?>
Some SQL queries may produce duplicate results. In such cases, duplicate rows may be filtered out using the "DISTINCT" keyword in a static query. In a dynamic query, use the distinct() method.
<?php// Force filtering of duplicate records in the result set.$query->distinct()?>
To group by a given field, use the groupBy() method.
To add an order by clause to a dynamic query, use the orderBy() method:
<?php $query->orderBy('title', 'DESC');?>
Ranges and Limits
Queries may also be restricted to a certain subset of the records found. In general this is known as a "range query". In MySQL, this is implemented using the LIMIT clause. To limit the range of a query, use the range() method.
In most cases we want "the firstnrecords". To do that, pass 0 as the first argument andnas the second.
<?php// Limit the result to 10 records$query->range(0, 10);?>
To produce a result table which can be sorted by any column, use the TableSortextenderand then add the table header. Note that an extender does return a new query object that you need to use from that point on.