Login

Drupal 7 Database Abstraction Layer

In example below we demonstrate some of the new Drupal 7 database abstraction layer features.

To add a record we are using db_insert(). "db_insert()" is more elegant than it's closest Drupal 6 counterpart (e.g. drupal_write_record). The method db_insert() returns the database key generated by the database.

In Drupal 7 db_query() now returns a prepared statement. Prepared statements are faster and more secure. Most database engines and drives will cache prepared statements. The database engine often caches the execution plan. The database driver often caches the statement itself.

Since Drupal 7 uses the object oriented PDO library we can use method chaining , as demonstrated below (dbMy_color->get()).

In the method dbMy_color->getCountByColor() we don't employ method chaining. In this example perform exception handling in a finer level of granularity.

Note! The object classes below demonstrate a very simply design patter on "Data Object" and "Database Access" object. The my_color class models the color data itself. The dbMy_color class includes the my_color data class and provides a centralized database access interface.

<?php
 
class my_color {
 
    protected $name;
    protected $id;
    const COLOR_SPAN_BEGIN = 
             '<span style="padding:6px;margin-right:4px;background-color:';
    const COLOR_SPAN_END = ';">&nbsp;</span>';
 
 
    function __construct() {
    }
 
    public function getId() {
        return $this->id;
    }
 
    public function setId($id) {
        $this->id = $id;
    }
 
 
    public function getName() {
        return $this->name;
    }
 
    public function setName($name) {
        $this->name = $name;
    }
 
}
 
class dbMy_color {
 
    const VOTE_SQL= "select name, count(id) as num_votes from {my_color}
                               group by name order by num_votes desc";
 
    function __construct() {
    }
 
    public function add($myColor) {
        $fields = array('name' => trim($myColor->name));
        $dbResult= db_insert('my_color')->fields($fields)->execute();
        // Note! $dbResult contains the serial field (autogen) int value created by the
        // database engine
        return $dbResult;
   }
 
    public function get($id = NULL) {
        $dbResult = db_query('select name from {my_color} where id = :id',
            array(':id'=>$id))->fetchObject();
        if ($dbResult == NULL) {
            throw new Exception('No value found');
        } else {
            $name = $dbResult->name;
            return $name;
        }
    }
 
    public function getCountByColor() {
        $stmnt = db_query( dbMy_color::VOTE_SQL);
        if ($stmnt == null)
            throw new Exception('Unexpected Database Exception Occurred');
 
        $rows= array();
        while($vote = $stmnt->fetch(PDO::FETCH_OBJ)) {
            $col = COLOR_SPAN_BEGIN.$vote->name.COLOR_SPAN_END.$vote->name;
            $rows[] = array($col, $vote->num_votes);
        }
 
        if (count($rows) == 0)
            throw new Exception('No Records Found');
 
        return $rows;
    }
}