Accessing the database using JDatabase

Joomla provides a sopisticated database abstraction layer to simplify the usage for 3PD. This guide should help you using this layer.

Why should I use the Joomla database class?

Joomla is build to be able to use several different kinds of SQL-database-systems and to run in a variety of environments with different table-prefixes. In addition to these functions, the class automatically creates the database connection. Besides instantiating the object, you only need 2 lines of code to get a result from the database and that in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension.

Preparaing the query

// Get a database object
$db =& JFactory::getDBO();

$query = "SELECT * FROM #__example_table WHERE id = 999999;";
$db->setQuery($query);

First we instantiate the database object, then we prepare the query. You can use the normal SQL-syntax, the only thing you have to change is the table-prefix. To make this as flexible as possible, Joomla uses a placeholder for the prefix, the “#__”. In the next step, the $db->setQuery(), this string is replaced with the correct prefix.

Now, if we don't want to get information from the database, but insert a row into it, we need one more function. Every string-value in the SQL-syntax has to be quoted using backticks for names and singel quotes for values. Joomla has some functions to do this and we can pass the names to the function $db->nameQuote($name) and the values to the function $db->Quote($value). A fully quoted query example is:

$query = "
  SELECT * 
    FROM ".$db->nameQuote('#__example_table')."  
    WHERE ".$db->nameQuote('id')." = ".$db->quote('999999').";
  ";

Whatever we want to do, we have to set the query with the $db->setQuery() function. Although you could write the query directly as a parameter for $db->setQuery(), it's commonly done by first saving it in a variable, normally $query, and then handing this variable over. This helps writing clean, readable code.

Executing the Query

To execute the query, Joomla provides several functions, which differ in their return value.

Basic Query Execution

• query

Query Execution Information

• getAffectedRows
• explain
• insertid

Insert Query Execution

• insertObject

Query Results

The database class contains many methods for working with a query's result set.

Single Value Result

loadResult

Use loadResult when you expect just a single value back from your database query.

This is often the result of a 'count' query to get a number of records:

$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*)
    FROM ".$db->nameQuote('#__my_table')."
    WHERE ".$db->nameQuote('name')." = ".$db->quote($value).";
  ";
$db->setQuery($query);
$count = $db->loadResult();

or where you are just looking for a single field from a single row of the table (or possibly a single field from the first row returned).

$db =& JFactory::getDBO();
$query = "
  SELECT ".$db->nameQuote('field_name')."
    FROM ".$db->nameQuote('#__my_table')."
    WHERE ".$db->nameQuote('some_name')." = ".$db->quote($some_value).";
  ";
$db->setQuery($query);
$result = $db->loadResult();

Single Row Results

Each of these results functions will return a single record from the database even though there may be several records that meet the criteria that you have set. To get more records you need to call the function again.

id	name	email	username
1	John Smith	johnsmith@example.com	johnsmith
2	Magda Hellman	magda_h@example.com	magdah
3	Yvonne de Gaulle	ydg@example.com	ydegaulle

loadRow

loadRow returns an indexed array from a single record in the table:

. . .
$db->setQuery($query);
$row = $db->loadRow();
print_r($row);

will give:

array('0' => '0', '1' => 'John Smith', '2' => 'johnsmith@example.com', '3' => 'johnsmith')

You can access the individual values by using:

$row['index'] // e.g. $row['2']

Notes:

1. The array indices are numeric starting from zero.
2. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful

loadObject

loadRow returns a PHP object from a single record in the table:

. . .
$db->setQuery($query);
$result = $db->loadRow();
print_r($result);

will give:

stdObject('id' => '0', 'name' => 'John Smith', 'email' => 'johnsmith@example.com', 'username' => 'johnsmith')

You can access the individual values by using:

$row->index // e.g. $row->email

Notes:

1. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful

Multi-Row Results

• loadObjectList
• loadRowList
• loadAssocList

Misc Result Set Methods

• getNumRows

Tips, Tricks & FAQ

We had a few people lately using sub-queries like these:

SELECT * FROM #__example WHERE id IN (SELECT * FROM #__example2);

These kind of queries are only possible in MySQL 4.1 and above. Another way to achieve this, is splitting the query into two:

$query = "SELECT * FROM #__example2";
$database->setQuery($query);
$query = "SELECT * FROM #__example WHERE id IN (". implode(",", $database->loadArray()) .")";