Accessing the Database to Make Queries

Making a custom database query in Concrete CMS is easy. This can be done from within any method, controller, or even a view (although not recommended). To retrieve the database object, simply call this function:

\Database::connection();

Example

public static function getFoo($id)
{
    $db = \Database::connection();
    $foo = $db->fetchColumn('SELECT foo FROM bar WHERE id = ?', [$id]);
    return $foo;
}

Note how the $id is passed in as the second parameter rather than simply appended to the string. This makes use of Prepared Statements to avoid potential SQL injection issues.

Doctrine API

Concrete's database class is based heavily on the Doctrine DBAL Connection object.

The list of Doctrine functions is available at: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.5/reference/data-retrieval-and-manipulation.html

Deprecated Methods

You may see code like Loader::db() – or Database::get(). These particular methods of obtaining the database connection are deprecated and should not be used in new code. Use \Database::connection(); instead.

Advanced: Using Dependency Injection

If you're working on a custom class and you'd like the active database connection passed into the class, rather than calling this method from within your class methods, define the database connection as as parameter in your class constructor like this:

namespace My\Custom\Name;
use Concrete\Core\Database\Connection\Connection;

class Bar
{

    protected $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function getFoo($id)
    {
        $foo = $this->connection->fetchColumn('SELECT foo FROM bar WHERE id = ?', [$id]);
        return $foo;
    }

}

By using the Core::make() method to instantiate your custom object, the dependencies like Connection will automatically be populated.

    $bar = Core::make('My\Custom\Name\Bar');
    $foo = $bar->getFoo(12);

Even though we didn't instantiate the class directly and pass the active connection in, the Core::make() method is able to intelligently instantiate and provide the necessary objects to the constructor.