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

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

$db = JFactory::getDbo();

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.

$query = $db->getQuery(true);

quoteName

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.

Example 1.1: Inserting a Record

For INSERT query, you need 3 things - table name, columns and values. We will first create an array of columns and values and then prepare the insert query. We will also use quoteName function 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));

Example 1.2: Inserting Record Using an Object

The JDatabaseDriver class also provides a convenient method for saving an object directly to the database allowing us to add a record to a table without writing a single line of SQL.

// 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 = JFactory::getDbo()->insertObject('#__user_profiles', $profile);

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.

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 JDatabaseDriver class provides a convenience 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 = JFactory::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.

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();

That's it!

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 would return up to 10 records.

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; the join type (inner, outer, left, right) and the 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

1. Single Value Result

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. For example,

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

// Reset the query using our newly populated query object.
$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). For example,

$query->select('field_name');
$query->from($db->quoteName('#__my_table'));
$query->where($db->quoteName('some_name')." = ".$db->quote($some_value));

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

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 a PHP 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']

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.