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

Select:

    $con = \Drupal\Core\Database\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();

Another example:

$query = $connection->select('node', 'n', $options);

$query->join('node_field_data', 'nfd', 'n.nid = nfd.nid');

$query

  ->fields('n', array('nid'))

  ->fields('nfd', array('title'))

  ->condition('nfd.type', 'page')

  ->condition('nfd.status', '1')

  ->orderBy('nfd.created', 'DESC')

  ->addTag('node_access');