Working with Joomla Database

To query data source, you can call a number of DatabaseQuery methods like select, from, join, insert, update, delete, where and order. By using these methods, you can create almost any query.

The first step is to obtain a database connection. Joomla knows what database you are using based on the configuration which stores the global configuration of the website. It has all the database connection details.

To obtain a new instance of the DatabaseQuery class, use the DatabaseDriver getQuery method:

$db = Factory::getDbo();
$query = $db->getQuery(true);

Then, you need to create a new query object. This variable is used to prepare the query that you are going to run on the database. The true parameter in the getQuery() states that you are starting a new query and not continuing a previous one.

$db->quoteName(($name, $as = null))

You can wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection risks and reserved word conflicts.

$db->quote($text, $escape = true)

This function quotes and optionally escapes a string to database requirements for use in database queries. You can pass a string or an array of strings to quote.

$db->getDateFormat()

The function returns a PHP date() function compliant date format for the database driver.

$db->getPrefix()

The function returns the common table prefix for the database driver.

Example 1.1: Inserting a Record

For INSERT query, you need 3 things - table name, columns and values. First create an array of columns and values, and then prepare the insert query. Also, use quoteName() method for insert and columns.

$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

$values = array(1001, $db->quote('custom.message'), $db->quote('Inserting a record using insert()'), 1);

$query
 ->insert($db->quoteName('#__user_profiles'))
 ->columns($db->quoteName($columns))
 ->values(implode(',', $values));

To get the ID of the row that you just inserted, you can use the insertid() method. For example,

// Get the row that was just inserted
$new_row_id = $db->insertid();

Example 1.2: Inserting Record Using Object

The DatabaseDriver class also provides a simple method for saving an object directly to the database.

// Create and populate an object.
$profile = new \stdClass();
$profile->user_id = 1001;
$profile->profile_key = 'custom.message';
$profile->profile_value = 'Inserting a record using insertObject()';
$profile->ordering = 1;

// Insert the object into the user profile table.
$result = Factory::getDbo()->insertObject('#__user_profiles', $profile, $key);

The $key is name of the primary key like id (optional). If provided, it is updated with the newly inserted row's primary key value.

You do not need to escape the table name. You need to ensure that the record does not exist before attempting to insert it, so adding some kind of record check before executing the insertObject() method is recommended.

The insertObject() method will throw a error if there is a problem inserting the record into the database table.

Example 2.1: Updating a Record

To UPDATE a record, you need 3 things - table name, fields and conditions.

$fields = array(
 $db->quoteName('profile_value') . ' = ' . $db->quote('Updating custom message for user 1001.'),
 $db->quoteName('ordering') . ' = 2'
);

$conditions = array(
 $db->quoteName('user_id') . ' = 42', 
 $db->quoteName('profile_key') . ' = ' . $db->quote('custom.message')
);

$query->update($db->quoteName('#__user_profiles'))
 ->set($fields)
 ->where($conditions);

Example 2.2: Updating Record Using an Object

The DatabaseDriver class provides a simple method for updating an object. You can update table with new values using an existing id primary key.

// Create an object for the record we are going to update.
$object = new stdClass();

// Must be a valid primary key value.
$object->id = 1;
$object->title = 'My Custom Record';
$object->description = 'A custom record being updated in the database.';

// Update their details in the users table using id as the primary key.
$result = Factory::getDbo()->updateObject('#__custom_table', $object, 'id');

You need to ensure that the record already exists before attempting to update it, so add some kind of record check before executing the updateObject() method. The updateObject() method will throw a error if there is a problem updating the record into the database table.

Example 3.1: Deleting a Record

To DELETE a record, you need 2 thing - table name and conditions.

$conditions = array(
 $db->quoteName('user_id') . ' = 1001', 
 $db->quoteName('profile_key') . ' = ' . $db->quote('custom.%'));

$query->delete($db->quoteName('#__user_profiles'));
$query->where($conditions);

Execute the Query

After the query is prepared, you can execute the query on the database.

$db->setQuery($query);
$db->execute();

Example 4.1: Selecting Records from Single Table

Using the select(), from(), where() and order() methods, you can create queries which are flexible, easily readable and portable. For example,

$query
 ->select($db->quoteName(array('user_id', 'profile_key', 'profile_value', 'ordering')))
 ->from($db->quoteName('#__user_profiles'))
 ->where($db->quoteName('profile_key') . ' LIKE '. $db->quote('custom.%'))
 ->order('ordering ASC');

Chaining can become useful when queries become longer and more complex. A limit can be set to a query using setLimit().

$query
 ->select($db->quoteName(array('user_id', 'profile_key', 'profile_value', 'ordering')))
 ->from($db->quoteName('#__user_profiles'))
 ->setLimit('10');

Above query will return up to 10 records.

You can also use group() for grouping. The following query will count the number of articles in each category.

$query
 ->select(array('catid', 'COUNT(*)'))
 ->from($db->quoteName('#__content'))
 ->group($db->quoteName('catid'));

After generating the query, you need to reset the query using newly populated query object.

$db->setQuery($query);

Example 4.2: Selecting Records From Multiple Tables

Using the join() methods, you can select records from multiple related tables. The join method takes two arguments:

Join type (inner, outer, left, right)
Join condition

Following example will select all articles for users who have a username which starts with 'z'. By putting 'a' as the second parameter will generate `#__content` AS `a`. The join method enables to query both the content and user tables, retrieving articles with their author details.

$query
 ->select(array('a.*', 'b.username', 'b.name'))
 ->from($db->quoteName('#__content', 'a'))
 ->join('INNER', $db->quoteName('#__users', 'b') . ' ON (' . $db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id') .   ')')
 ->where($db->quoteName('b.username') . ' LIKE \'z%\'')
 ->order($db->quoteName('a.created') . ' DESC');

You can also use multiple joins to query across more than two tables. In some cases, you also need to use the AS clause when selecting items to avoid column name conflicts.

Query Results

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

1. Single Value Result

Use loadResult() when you expect just a single value from your database query. This is often the result of a 'count' query to get a number of records. For example,

$query->select('COUNT(*)');
$query->from($db->quoteName('#__my_table'));
$query->where($db->quoteName('name')." = ".$db->quote($value));

$db->setQuery($query);
$count = $db->loadResult();

It is also useful when 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).

2. Single Row Result

These functions return a single record from the database even though there may be several records that meet the criteria that you have set.

The loadRow() returns an indexed array from a single record in the table

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

You can access the individual values by using:

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

The loadAssoc() returns an associated array from a single record in the table.

$db->setQuery($query);
$row = $db->loadAssoc();

You can access the individual values by using:

$row['name'] // e.g. $row['email']

The loadObject() returns an object from a single record in the table.

$db->setQuery($query);
$result = $db->loadObject();

You can access the individual values by using:

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

3. Single Column Result

The loadColumn() returns an indexed array from a single column in the table.

$db->setQuery($query);
$column= $db->loadColumn();

You can access the individual values by using:

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

The loadColumn($index) returns an indexed array from a single column when there are multiple fields in the select method. The loadColumn() is equivalent to loadColumn(0).

4. Multiple Rows Result

The loadRowList() returns an indexed array of indexed arrays from the table records returned by the query.

$db->setQuery($query);
$row = $db->loadRowList();

You can access the individual rows by using:

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

You can access the individual values by using:

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

The loadAssocList() returns an indexed array of associated arrays from the table records returned by the query.

$db->setQuery($query);
$row = $db->loadAssocList();

You can access the individual rows by using:

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

You can access the individual values by using:

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

The loadAssocList('key') returns an associated array - indexed on 'key' - of associated arrays from the table records returned by the query.

$db->setQuery($query);
$row = $db->loadAssocList('username');

You can access the individual rows by using:

$row['key_value'] // e.g. $row['johnsmith']

You can access the individual values by using:

$row['key_value']['column_name'] // e.g. $row['johnsmith']['email']

The Key must be a valid column name from the table. It does not have to be an Index or a Primary Key. But if it does not have a unique value you may not be able to retrieve results reliably.

The loadObjectList() returns indexed array of objects.

5. Number of Rows

The getNumRows() will return the number of result rows found by the last SELECT or SHOW query and waiting to be read. To get a result from getNumRows() you have to run it after the query and before you have retrieved any results.

$db->setQuery($query);
$db->execute();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();

Using OR in Queries

When using multiple WHERE clauses in your query, they will be treated as an AND. To use a WHERE clause as an OR, the query can be written like this:

$query = $db
 ->getQuery(true)
 ->select('COUNT(*)')
 ->from($db->quoteName('#__my_table'))
 ->where($db->quoteName('name') . " = " . $db->quote($name_one), 'OR')
 ->where($db->quoteName('name') . " = " . $db->quote($name_two));