Database Basic Examples (Select, Merge, Insert ...)

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();