Getting Data from the Concrete5 Database

Warning! This tutorial is for deprecated Concrete5 versions 5.6 and below. Many of the examples and methods are no longer relevant to modern Concrete5.

Whenever I build a client website or application I am constantly in need of either reading from or writing to tables in the Concrete5 Database. It's an integral part of the whole "Database Driven" technology that dominated dynamically created websites. Everything about your pages, files, and users is stored within the tables of the Concrete5 database, and freeing that data for use in your own methods and pages is an essential skill you should master.

We'll start with a simple read operation. Now before I get down to the nitty gritty, you need to understand that if your intent is to access data about a built in property or attribute of concrete5, there is proabbly already a method to safely get that data written. The functions require no direct access to the database with your code and it's highly recommened that you check the Concrete5 API, and developer docs for the correct built-in solution.

Before you can read or write from the database you need to load the database object.

Step 1. Load the Database Object

You can put the commands to access the database almost anywhere, but I typically find myself following MVC protocol and collecting all functions that directly access the database into the model. These are classes found within the files stored in the models/ directory. It's also not unusual to find these functions in a page or block controller.php file as well.

First things first, lets load the Concrete5 database object like so:

$db = Loader::db();

The beauty of a nice framework like Concrete5, is that you can foget about all of the connection commands, and storing database login credentials, as that has already been taken care of for you. Once you call up the object you can now begin interacting with the database directly.

Step 2. Understanding ADOdb

Because Concrete5 is all about being helpful, they have thoughfully integrated a sort of database middleman that can really simplify your interactions with the C5 database. ADOdb is the name of the middleman, and its useful for a couple of reasons.

First off, if you use it wisely it will allow your application to be used on any number of database types. I personally, only have experience with MySQL, which dominates the web, and will have to claim ignorance on its ease of use with other databases, but that really is ADOdb's primary reason for existance.

Another great feature is it's AutoExecute INSERT and UPDATE methods. If you stick with these you are protecting yourself from a lot of dangerous SQL Injection vulnerabilities that can easily rear their head when writing hundreds of queries by hand.

Finally, I really like ADOdb because it makes grabbing data from the database extremely easy, always packing it neatly for me in an associative array like ($arrayname[$column_name] = $value).

Step 3. Getting Data from the Database

I probably pull data from databases ten times more than I write it to the database, so we will start here: ADOdb allows for several ways of asking the database for data. Which one you use depends on what you are looking for.

We will use the following table structure for our queries:

deepSpaceProbes
scIDnameorgindestination
1Voyager 1USAHeliopause
2Lunokhod 1USSRMoon
3JunoUSAJupiter

GetOne

The GetOne method is useful when you want to return just a single value. For instance, If I want to write a query that will tell me the detsination of a particular space probe, I could do it like so:

$probeName = "Juno";
$destination = $db->getOne("SELECT destination FROM deepSpaceProbes WHERE name LIKE '?' " , array($probeName) );

After running the command above $destination would be set to the string "Jupiter". No need to deal with recordsets, nothing to clean up. The function takes two arguments, the first is the general query, with question marks where the variable data will go. The second argument is an input array, that will be used to replace the question marks successively.

It's worth noting that the command above could have been simplified to the following, but would be more vulnerable to SQL Injection attack in the event that $probeName was somehow being sent from the browser as a $_POST or $_GET variable.

$probeName = "Juno";
$destination = $db->getOne("SELECT destination from deepSpaceProbes WHERE name LIKE '$probeName' ");

GetRow

The GetRow command is useful when you want to retrieve all of the fields in a matching row. Like the GetOne command, this is only useful if you want just a single match to your query.

$scID = 2;
$probeData = $db->GetRow("SELECT * FROM deepSpaceProbes WHERE scID = ?" , array($scID));

The resultant $probeData variable would be an associative array of the following structure:

$probeData['scID'] = 2
$probeData['name'] = "Lunokhod 1"
$probeData['origin'] = "USSR"
$probeData['destination'] = "Moon"

Data in this format can easily be displayed using a simple foreach loop:

foreach ($probeData as $column => $value) {
	echo "$column = $value <br />";
}

The snippet above would print

scID = 2
name = Lunokhod 1
origin = USSR
destination = Moon

GetAll

Finally, the GetAll command is used when you expect to return one or more rows of data. Take the following example:

$origin = "USA";
$usProbes = $db->GetAll("SELECT * FROM deepSpaceProbes WHERE origin LIKE '?' ", array($origin)); 

The GetAll function creates a multidimensional array which is numerically indexed for each matching row with an nested associative array inside. The $usProbes variable would look like so:

$usProbes[0]['scID'] = 1
$usProbes[0]['name'] = "Voyager 1"
$usProbes[0]['origin'] = "USA"
$usProbes[0]['destination'] = "Heliopause"

$usProbes[1]['scID'] = 3
$usProbes[0]['name'] = "Juno"
$usProbes[1]['origin'] = "USA"
$usProbes[1]['destination'] = "Jupiter"

The contents of this array could be printed to the screen with the following code:

foreach ($usProbes as $probeData) {
	foreach ($probeData as $column => $value) {
		echo "$column = $value <br />";
	}
	echo "<hr />";
}

This code would print the following:

scID = 1
name = Voyager 1
origin = USA
destination = Heliopause


scid = 3
name = Juno
origin = USA
destination = Jupiter

These are the basics of retrieving data from a Concrete5 databse using the ADOdb methods. Make sure you visit the ADOdb page to find out more about the methods available for database manipulation.