資料庫操作語法

$result = db_query('SELECT name FROM {role} WHERE rid = :rid', array(':rid' => 2));
db_query( 'SELECT name FROM {role} WHERE rid > :rid AND rid < :max_rid'
    , array(':rid' => 0, ':max_rid' => 3));
$nbr_records = db_query("SELECT count(nid) FROM {joke}")->fetchField(); //若為單一值可用 fetchField() 直接取值
 
//&diams; 取得資料集(DataSet)
$result = db_query("SELECT nid, title FROM {node} WHERE type = :type AND status = :status"
    ,array( ':type' => $type, ':status' => 1,) );
foreach ($result as $row) {  
   echo $row->title."<br/>";
}
//&diams; 物件模式操作
$result = db_query('SELECT name FROM {role} WHERE rid = :rid', array(':rid' => 2));
等同
$query = db_select('role', 'r'); //r 為資料表role的代號
$query
    ->condition('rid', 2) // where ...
    ->fields('r', array('name')); //取出資料的欄位名稱
    ->range(0,100); //限制取出筆數
 
$result = $query->execute();
foreach($result as $row) {  
    echo $row->name."<br/>";
}
//其他函式
$query
    ->leftJoin(資料表, 'ra', 'ra.fieldName = ds.fieldName')
    ->join(資料表, 'ra', 'ra.fieldName = ds.fieldName')
    ->condition($fieldName, array(1,2,3), 'IN')
    ->condition($fieldName, "%$searchtext%", 'LIKE')
    ->orderBy('changed', 'DESC')
    ->orderBy('title', 'ASC')
    ->distinct()

$query = db_select('Exhibitors', 'e');
$db_or = db_or();
foreach ($search_fields as $field) {
    $db_or->condition($field, "%$searchtext%", 'LIKE');
}
if (!is_null($search_category)) {
    $db_and = db_and();
    $db_and->condition('ProductCategory', array(1,2,3), 'IN');
    $db_and->condition($db_or);
}
$query->condition($db_and);

$query = db_select('node', 'n')
    ->extend('PagerDefault')->limit(10)
    ->extend('TableSort')->orderByHeader($header);
$query
  ->condition('type', 'page')
  ->fields('n', array('title'))
  ->limit(10); //表示每頁最多10筆資料
$result = $query->execute();
$output = '';
foreach ($result as $row) {  
   $output .= $row->title."<br/>";
}
$output .= theme('pager');// 套用系統頁面格式
print $output;

$nid = db_insert('joke')
    ->fields( array( 'nid' => '4', 'vid' => 1, 'punchline' => 'And the pig said oink!', ) )
    ->execute();

$num_updated = db_update('joke')
    ->fields( array('punchline' => 'Take my wife please!',) )
    ->condition('nid', 3, '>=')
    ->execute();
 
$query = db_insert('block_node_type')->fields(array('type', 'module'));
foreach (array_filter($form_state['values']['types']) as $type) {
    $query->values(array(
        'type' => $type,
        'module' => $form_state['values']['module'],
    ));
}
$query->execute();

$num_deleted = db_delete('joke')
    ->condition('punchline', 'Take my wife please!')
    ->execute();

• General concepts
• Database configuration
• Static queries
• Dynamic queries
• Result sets
• Insert queries
• Update queries
• Delete queries
• Merge queries using db_merge
• Writing database drivers
• Error handling
• Transactions
• Chaining
• Functions and operators

unctions

Name	Description
db_and	Returns a new DatabaseCondition, set to "AND" all conditions together.
db_close	Closes the active database connection.
db_condition	Returns a new DatabaseCondition, set to the specified conjunction.
db_delete	Returns a new DeleteQuery object for the active database.
db_driver	Retrieves the name of the currently active database driver.
db_escape_field	Restricts a dynamic column or constraint name to safe characters.
db_escape_table	Restricts a dynamic table name to safe characters.
db_insert	Returns a new InsertQuery object for the active database.
db_like	Escapes characters that work as wildcard characters in a LIKE pattern.
db_merge	Returns a new MergeQuery object for the active database.
db_next_id	Retrieves a unique id.
db_or	Returns a new DatabaseCondition, set to "OR" all conditions together.
db_query	Executes an arbitrary query string against the active database.
db_query_range	Executes a query against the active database, restricted to a range.
db_query_temporary	Executes a query string and saves the result set to a temporary table.
db_select	Returns a new SelectQuery object for the active database.
db_set_active	Sets a new active database.
db_transaction	Returns a new transaction object for the active database.
db_truncate	Returns a new TruncateQuery object for the active database.
db_update	Returns a new UpdateQuery object for the active database.
db_xor	Returns a new DatabaseCondition, set to "XOR" all conditions together.