if you need joining a subquery use this:
// 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
// db_query
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid AND n.type = :type', array(':uid' => $uid, ':type' => 'page'));
// Result is returned as a iterable object that returns a stdClass object on each iteration
foreach ($result as $record) {
// Perform operations on $record->title, etc. here.
print($record->title . "");
// in this example the available data would be mapped to object properties:
// $record->nid, $record->title, $record->created
}
$sql = "SELECT (DATE_FORMAT(FROM_UNIXTIME(co.changed), '%d-%m-%Y')) AS date, ci.type AS type, co.status, COUNT(distinct(co.order_id)) AS count
FROM commerce_order co
LEFT JOIN commerce_line_item ci ON co.order_id = ci.order_id
GROUP BY date, co.status, ci.type
ORDER BY date";
$result = db_query($sql);
foreach ($result as $row) {
print_r($row);
}
//Left Join SubQuery
Query I am executing with db_select:
$query = db_select('node_view_count', 'n');
$query->join('users', 'u', 'n.uid = u.uid'); //JOIN node with users
$query->groupBy('n.nid');//GROUP BY user ID
$query->groupBy('u.name');//GROUP BY user ID
$query->fields('n',array('nid'))//SELECT the fields from node_view_count
->fields('u',array('name'))//SELECT the fields from user
->condition('n.uid','0','<>')
->orderBy('timestamp', 'DESC');//ORDER BY created
2
$connection = Database::getConnection();
$sth = $connection->select('file_managed', 'fm');
$sth->addField('fm', 'filemime');
$sth->addExpression('COUNT(fm.filemime)', 'count');
$sth->groupBy('fm.filemime');
// Execute the statement
$data = $sth->execute();
// Get all the results
$results = $data->fetchAll(PDO::FETCH_ASSOC);
//结果集处理：
db_like($prefix);
https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_like/7.x
$search_string ="per";
$result = db_query('SELECT title
FROM {node} n
WHERE n.title like :title'
,array(':title' => "%".$search_string."%"))
->fetchAll();
print_r($result);
$result = db_select('person', 'p')
->fields('p')
->condition('name', db_like($prefix) . '%', 'LIKE')
->execute()
->fetchAll();
$sql = 'SELECT sid, score FROM {search_index} WHERE word LIKE :term';
$result = db_query($sql, array(':term' => '%' . db_like($search_term)));
<?php
// Using the same query from above...
$uid = 1;
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));
// Fetch next row as a stdClass object.
$record = $result->fetchObject();
// Fetch next row as an associative array.
$record = $result->fetchAssoc();
// Fetch data from specific column from next row
// Defaults to first column if not specified as argument
$data = $result->fetchColumn(1); // Grabs the title from the next row
// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();
// Retrieve all records as stdObjects into an associative array
// keyed by the field in the result specified.
// (in this example, the title of the node)
$result->fetchAllAssoc('title');
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// Also good to note that you can specify which two fields to use
// by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be nid => created
$result->fetchAllKeyed(1,0); // would be title => nid
// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($db_column_number);
// Count the number of rows
$result->rowCount();
//count just one
$dev_query = "select id from aa where ont > UNIX_TIMESTAMP() - 60 group by id";
$id = db_query($dev_query)->fetchField(); //print only one id string
$a = 1;
?>
//DB_insert
https://www.drupal.org/node/310079
https://dev.mysql.com/doc/refman/5.7/en/insert-select.html //db insert 语法
//多条数据的插入
$values = array(
array(
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
),
array(
'title' => 'Example 2',
'uid' => 1,
'created' => REQUEST_TIME,
),
array(
'title' => 'Example 3',
'uid' => 2,
'created' => REQUEST_TIME,
),
);
$query = db_insert('node')->fields(array('title', 'uid', 'created'));
foreach ($values as $record) {
$query->values($record);
}
$query->execute();
//基于select的插入：
<?php
// Build the SELECT query.
$query = db_select('node', 'n');
// Join to the users table.
$query->join('users', 'u', 'n.uid = u.uid');
// Add the fields we want.
$query->addField('n','nid');
$query->addField('u','name');
// Add a condition to only get page nodes.
$query->condition('type', 'page');
// Perform the insert.
db_insert('mytable')
->from($query)
->execute();
?>
db_insert 模拟 insert ignore 参数：
https://drupal.stackexchange.com/questions/89253/how-to-set-insert-ignore-in-db-insert-without-db-merge （
貌似不可实现）
解决方案：
（1）
try {
$insertID = db_insert('crawl_data')->fields(array(
'url' => $url,
))->execute();
} catch (Exception $ex) {
//这样就不会执行插入，并且不报错...
}
（2）：先查一下，再入库
其他：
db_merge('people')
->key(array('job' => 'Speaker'))
->insertFields(array('age' => 31,'name' => 'Meredith'))
->updateFields(array('name' => 'Tiffany'))
->execute();
//如果存在job为Speaker的一条记录，则更新name为Tiffany，如果不存在，就插入一条age为31，name为Meredith，job为Speaker的记录。
6.对数据库某字段值自动加一或者自增。
复制代码 代码如下:
db_update('example_table')
->expression('count', 'count + 1')
->condition('field1', $some_value)
->expression('field2', 'field2 + :inc', array(':inc' => 2))
->execute();
//通过子sql查询插入
<?php
// Build the SELECT query.
$query = db_select('node', 'n');
// Join to the users table.
$query->join('users', 'u', 'n.uid = u.uid');
// Add the fields we want.
$query->addField('n','nid');
$query->addField('u','name');
// Add a condition to only get page nodes.
$query->condition('type', 'page');
// Perform the insert.
db_insert('mytable')
->from($query)
->execute();
?>
https://api.drupal.org/api/drupal/includes%21database%21database.inc/7.x
$and = db_and()->condition('mid', 1)->condition('cache_type', 'year');
$and = db_or()->condition('mid', 1)->condition('cache_type', 'year');
$query->condition($or);
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);
}
//LEFT_JOIN 子查询 sub_query
原理(code)：
public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) {
return $this->addJoin('LEFT OUTER', $table, $alias, $condition, $arguments);
}
//add join
public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()) {
if (empty($alias)) {
if ($table instanceof SelectQueryInterface) {
$alias = 'subquery';
}
else {
$alias = $table;
}
}//对table 做了是否是查询的判断
$alias_candidate = $alias;
$count = 2;
while (!empty($this->tables[$alias_candidate])) {
$alias_candidate = $alias . '_' . $count++;
}
$alias = $alias_candidate;
if (is_string($condition)) {
$condition = str_replace('%alias', $alias, $condition);
}
$this->tables[$alias] = array(
'join type' => $type,
'table' => $table,
'alias' => $alias,
'condition' => $condition,
'arguments' => $arguments,
);
return $alias;
}
QQQQ:
$query = db_select('super_long_table', 'slt');
$query->addField('slt', 'mys_super_long_field', 'mslf');
if you need joining a subquery use this:
// db_querydb_like($prefix);$sql = 'SELECT sid, score FROM {search_index} WHERE word LIKE :term'; $result = db_query($sql, array(':term' => '%' . db_like($search_term)));How to set 'INSERT IGNORE' in db_insert without db_merge
//如果存在job为Speaker的一条记录，则更新name为Tiffany，如果不存在，就插入一条age为31，name为Meredith，job为Speaker的记录。
6.对数据库某字段值自动加一或者自增。
复制代码 代码如下:
//通过子sql查询插入
https://api.drupal.org/api/drupal/includes%21database%21database.inc/7.x
//LEFT_JOIN 子查询 sub_query
原理(code)：
How to set an alias to a fields on a db_select?