Tip: Keep stuff tidy

Calls to the database should be contained in your controller files. This is true whether you’re building a block or a single page or a custom theme. Try to keep the presentation layer (view) separate from any database interaction.

concrete5 stores its data in a MySQL database, and naturally any developer interested in extending the capabilities of concrete5 will need to know how to fetch and manipulate that data. Below, we'll learn about how to easily create a database connection, pass queries and simplify them by using our built-in abstraction layer.

Where are the default connection details stored?

When you install concrete5, a file is created using the database connection information you supplied during installation:

your_site_root/config/site.php

The server, database name, database username and password are stored there as constants. These are the default settings that concrete5 will use to connect to a database.

Connecting to the database

The database object

You can load the active database object within any function or class by running the following command:

$db= Loader::db();

This makes the database object available in our current scope, meaning that we can now fetch and manipulate data.

Connecting to other databases

If you need to connect to a different database, you can switch out the active database object on the fly:

$db = Loader::db( 'newserver', 'newuser', 'newpassword', 'newdatabase', true);

When you’re done working with the other database, return to your default concrete5 database by clearing out the connection details:

$db = Loader::db(null, null, null, null, true);

This will re-load your default database object.

Querying the database

Basic database queries

Once we've loaded the database object, we can fetch data be calling the Execute method and passing our query as our argument. For instance, this:

$db->Execute('select * from TestTable');

Will select everything in a table named TestTable.

Simplifying queries using ADODB

concrete5 uses the ADODB database extraction layer. This gives developers an easy way to build queries using a shortened syntax. This is the preferred way to fetch and manipulate data in concrete5. While writing out a full, traditionally formed query (like the one given above) will work, it is recommended to take advantage of ADODB’s flexibility wherever possible.

Let’s say you want to store the value of some variables in your database.

$db->Execute("update TestTable set TestColumn=? where key=?",array($colval, $key));

This will update the column named TestColumn in the table named TestTable, setting TestColumn to $colval wherever it matches $key.

Here are a few other examples of shortcuts made possible by ADODB:

$db->GetOne("select col from table where key='John'"); #returns first field
$db->GetRow("select col from table where key='John'"); #returns first row
$db->GetAll("select col from table"); #returns all rows as two-dimensional array
$db->GetAssoc("select key,col from table"); # returns associative array $key=>col

Database debugging

Debug mode

If you're troubleshooting your code and want to see everything your site is doing when it renders a page, Debug mode will come in handy. It allows you to see all database queries printer inline in your page. Add this line to an element from your view layer (a page type, include, block’s view.php, etc):

Database::setDebug(true);

You can step back out of debug mode at any time by setting debug back to false:

Database::setDebug(false);