Using Database::getConnection() for queriesfor Drupal 8 , 9 , 10 , and 11

In our previous lesson, you learned how to create database tables using the Schema API in your module's .install file. Now, we'll advance to interacting with these tables using Database::getConnection(). This lesson will equip you with the foundations needed to execute basic database operations in Drupal, such as select, insert, update, and delete queries.

Introduction to Database::getConnection()

Database::getConnection() is the gateway to Drupal’s database API, allowing developers to perform various CRUD (Create, Read, Update, Delete) operations. The abstraction provided by Drupal's database layer simplifies working with databases, while simultaneously supporting multiple database backends. This ensures that your code is portable across different environments.

Establishing a Connection

To begin, you'll first need to establish a connection to the database. This is accomplished using the Database::getConnection() method. In most cases, you can follow this pattern:

<?php
use Drupal\Core\Database\Database;

// Establishing connection
$connection = Database::getConnection();
?>

Performing Select Queries

Let's start by retrieving data from the example table we created in the previous lesson. Assume the table is named example_table, having fields: id, name, and created. Here's how you can select data:

<?php
use Drupal\Core\Database\Database;

$connection = Database::getConnection();

// Define a select query
$query = $connection->select('example_table', 'e')
    ->fields('e', ['id', 'name', 'created'])
    ->orderBy('created', 'DESC');

$result = $query->execute();

// Fetching all results
$records = $result->fetchAll();

foreach ($records as $record) {
    // Example output
    echo "ID: {$record->id}, Name: {$record->name}, Created: {$record->created}
";
}
?>

Understanding the Select Query

• Select: The select() method initiates the query. The first argument is the table name, and the second is an alias used in the query.
• Fields: Specifies columns to be retrieved.
• OrderBy: Defines the order of the results.
• Execute: Executes the query and returns a result set.
• FetchAll: Retrieves all rows from the result set.

Inserting Data

Inserting data into a database table can be achieved using the insert() method:

<?php
$connection = Database::getConnection();

// Insert a record
$connection->insert('example_table')
    ->fields([
        'name' => 'New Entry',
        'created' => time(),
    ])
    ->execute();
?>

Here, the fields() method is used to provide the data to be inserted. The execute() method then runs the query.

Updating Data

Updating records in a table uses a similar syntax with the update() method:

<?php
$connection = Database::getConnection();

// Update a record where id equals 1
$connection->update('example_table')
    ->fields(['name' => 'Updated Entry'])
    ->condition('id', 1)
    ->execute();
?>

The condition() method is crucial, as it specifies which records should be updated.

Deleting Data

Finally, the delete() method is used to remove records:

<?php
$connection = Database::getConnection();

// Delete a record where id equals 1
$connection->delete('example_table')
    ->condition('id', 1)
    ->execute();
?>

Important Note:

While performing delete operations, always ensure that your condition() method is properly set to avoid unintended data loss.

Conclusion

In this lesson, we explored the basics of executing queries using Database::getConnection(), including selecting, inserting, updating, and deleting records in Drupal. Practicing these operations will build a solid foundation for more complex database manipulations.

Up Next

Our next lesson will focus on building dynamic queries with conditions and joins. This will enhance your ability to fetch related data across multiple tables efficiently. Stay tuned to learn more about Drupal's powerful database querying capabilities!