Drupal Database API
Drupal Database API is an abstraction layer built upon PHP’s PDO (PHP Data Objects) database API. The database abstraction layer also provides a structured way to construct complex queries and it protects the database by using good security practices.
Because different databases require different sorts of interaction, the Drupal database layer requires a driver for each database type.
Database connection objects
The examples here all use functions like db_select() and db_query(), which can be called from any Drupal method or function code. In some classes, you may already have a database connection object in a member variable, or it may be passed into a class constructor via dependency injection. If that is the case, you can look at the code for db_select() and the other functions to see how to get a query object from your connection variable. For example:
$query = $connection->select(‘example’, ‘e’);
would be the equivalent of
$query = db_select(‘example’, ‘e’);
To use the simple database query functions, you will need to make a couple of modifications to your bare SQL query:
- Enclose your table name in {}. Drupal allows site builders to use database table name prefixes, so you cannot be sure what the actual name of the table will be. So, use the name that is in the hook_schema(), enclosed in {}, and Drupal will calculate the right name.
- Instead of putting values for conditions into the query, use placeholders. The placeholders are named and start with :, and they take the place of putting variables directly into the query, to protect against SQL injection attacks. Like
$nids = array(13, 42, 144);
db_query(“SELECT * FROM {node} WHERE nid IN (:nids)”, array(‘:nids’ => $nids);
- LIMIT syntax differs between databases, so if you have a ranged query, use \Drupal::database()->queryRange() instead of \Drupal::database()->query().
Perform alterations to existing database schemas
When a module modifies the database structure of another module (by changing, adding or removing fields, keys or indexes), it should implement hook_schema_alter() to update the default $schema to take its changes into account.
Drupal transactions
Drupal also supports transactions, including a transparent fallback for databases that do not support transactions. To start a new transaction, call $txn = db_transaction(); The transaction will remain open for as long as the variable $txn remains in scope; when $txn is destroyed, the transaction will be committed. If your transaction is nested inside of another then Drupal will track each transaction and only commit the outer-most transaction when the last transaction object goes out out of scope.
To start a new transaction, simply call startTransaction() and store the return value in the variable $transaction. Like
$transaction = \Drupal::database()->startTransaction();
You must assign the return value of $connection->startTransaction(); to a variable, as in the example. If you call the method without assigning the return value to a variable, your transaction will commit instantly, making it useless.
The Database API throws exceptions on error, which can be picked up by wrapping your database operations in try {} catch() {} blocks
How to debug any custom query?
Here is an example of custom query -
$query = db_select(‘taxonomy_term_data’, ‘td’);
$query->fields(‘td’);
$query->join(‘taxonomy_index’, ‘ti’, ‘td.tid = ti.tid’);
$query->join(‘node’, ’n’, ‘ti.nid = n.nid’);
$query->condition(‘n.nid’, $nid, ‘=’);
To print this query we can use dump() function.
// Debug
dump($query->__toString());
// Debug.
dump($query->execute());
How to fetch all records at once into a single array?
use one of the following:
Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);Retrieve a 2-column result set as an associative array of field 0 => field 1.
$result->fetchAllKeyed();You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0If you need an array where keys and values contain the same field (e.g. for creating a ‘checkboxes’ form element), the following is a perfectly valid method:
$result->fetchAllKeyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]Retrieve a 1-column result set as one single array.
$result->fetchCol();Column number can be specified otherwise defaults to first column
$result->fetchCol($column_index);
Reference:
https://www.drupal.org/docs/drupal-apis/database-api/database-api-overview
https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21database.api.php/group/database/8.2.x
Keep Learning… Keep Sharing…
🙏
