Momento della lettura
Circa 1 minuti
Drupal 8 Database basic commands / Examples
Dont forget the use
use Drupal\Core\Database\Database;
Select:
$con = Database::getConnection();
$query = $con->select('table_name', 't')
->fields('t', ['field_1', 'field_2']);
$result = $query->execute()->fetchAll();
NULL Condition
$query->isNull('t.field_1');
$query->isNotNull('t.field_2');
Example : Inset into database
Methode 1. Single row
$con->insert('the_table_name')
->fields([
'field_1' => 'Value 1',
'field_2' => 'Value 2',
])
->execute();
Method 2. (Multiple rows)
$con->insert('the_table_name')
->fields(['field_1' ,'field_2'])
->values(['field_1' => 'Val 1','field_2'=>'value 2'])
->values(['field_1' => 'Val 3','field_2'=>'value 4'])
->execute();
#Or also:
$con->insert('the_table_name')
->fields(['field_1' ,'field_2'])
->values(['Val 1','value 2'])
->values(['Val 3','value 4'])
->execute();
Example : DB Merge : Insert or update if fields is already exist
$query = $con->merge('the_table_name')
->key(array('the_key_field' => 'THE_KEY_VALUE'))
->insertFields(array(
'timestamp' => date(),
'count' => 1,
'field_1' => 'Value 1',
'field_2' => 'Value 2',
))
->updateFields(array(
'timestamp' => date(), // update time
))
->expression('count', 'count + :inc', array(':inc' => 1)); // update counter
$query->execute();
Example : Database merge with multiple fields keys.
$query = $con->merge('table_word')
->keys(['word', 'lang'], ['word' => $word, 'lang' => $lang])
->insertFields([
'date' => time(),
'word' => $word,
'lang' => $lang,
])
->updateFields(['date' => time()]);
$query->execute();
Example : update a field.
$con->update('my_table')
->fields(['status' => 'NEW-STATUS'])
->condition('word', $item_word)
->condition('lang', $item_lang)
->execute();
Read all rows of a table:
$data = $con->select('table_name','t')->fields('t')->execute()->fetchAll(PDO::FETCH_ASSOC);
Get the table prefix if exist
$con = Database::getConnection();
$table = 'my_table';
if ($table_prefix = $con->tablePrefix()) {
// Table name with prefix.
$table = $table_prefix . $table;
}
Get a table's columns list
$con = Database::getConnection();
$table = 'my_table';
$fields_list = $con->query("DESCRIBE `$table`")->fetchAll();
The like condition syntax is:
$query->condition('field_name', "%" . $query->escapeLike("The string") . "%", 'LIKE');
Example:
$con = \Drupal\Core\Database\Database::getConnection();
$query = $con->select('table_name', 't')
->fields('t', ['field_1', 'field_2']);
$query->condition('field_1', "%" . $query->escapeLike($string) . "%", 'LIKE');
$result = $query->execute()->fetchAll();