Joomla Database Queries Explained

Joomla Platform Framework

Joomla 5 serves as the foundational application framework for all of our web applications, providing a structured, object-oriented PHP environment that handles core concerns such as routing, database access, user authentication, MVC architecture, and extension management.

Our PHP code leverages Joomla to securely connect to the database, build and sanitize queries, handle various result types (object lists, associative arrays, single values), work with user identity and authorization groups, manage raw URL routing and URI parameters, and utilize application-level methods (e.g., getApplication) to control request flow and behavior consistently across applications. We code within Joomla’s PHP framework using its classes, services, and dependency injection patterns to ensure consistency, security, and maintainability across all our applications.

On top of Joomla's framework, we leverage Fabrik as our rapid application layer for building data-driven interfaces (forms, lists, and workflows). Our development approach combines Joomla’s backend architecture with Fabrik’s application-building capabilities: Joomla handles the underlying system design and custom business logic (via PHP classes, plugins, and services), while Fabrik manages data interaction and UI scaffolding (automatically generating forms, lists, and views from configured data structures). We extend and integrate both frameworks cohesively across all our applications—writing structured, reusable PHP code that aligns with Joomla standards while enhancing and customizing Fabrik functionality—so that all our applications remain consistent, scalable, and easy for any team member to understand and maintain over time.

Working with Joomla Database Queries

In Joomla, to interact with the database is by using Joomla's built-inFactory class. This class gives us access to many objects including the database connection, user information, etc.

Accessing the Database

Database interaction is a core part of every application we build. All database access is performed using Joomla’s database API, ensuring queries are properly structured, secure (sanitized/quoted), and consistent across all applications. We use Joomla’s database driver and query builder to construct, execute, and retrieve query results in a standardized way, supporting multiple return formats (e.g., object lists, associative arrays, single values) depending on the use case.

Writing and returning a database query in Joomla:

Get the database object
This gives us access to the database connection.
```
$db = \Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
```
Get the query object
This is where we start building our SQL query. We pass true to start with a clean query object.
```
$query = $db->getQuery(true);
```

Write your query
For example, to select all rows from a table:


$query
    ->select('*')
    ->from($db->quoteName('#__my_table'));

Set and run the query
Once the query is built, we tell Joomla to run it and return the results.
```
$db->setQuery($query);
$results = $db->loadObjectList(); // Returns an array of row objects
```

Common Query Methods

Method	Description
`getQuery(true)`	Creates a new query object
`select()`	Defines which columns to select (e.g. `'*'` or specific columns)
`from()`	Specifies the table name
`where()`	Adds conditions (e.g. `'id = 5'`)
`order()`	Sets an ORDER BY clause
`setQuery()`	Assigns the query to be executed
`loadObjectList()`	Returns multiple rows as an array of objects
`loadObject()`	Returns a single row as an object
`loadResult()`	Returns a single value (first column of first row)
`loadColumn()`	Returns an array of values from a single column
`execute()`	Executes the query (for INSERT, UPDATE, DELETE)

Joomla accessing the database documentation

Here's couple of Joomla doc articles going over this in more detail with examples:
https://docs.joomla.org/J5.x:Selecting_data_using_JDatabase
https://docs.joomla.org/J5.x:Inserting_Updating_and_Removing_data_using_JDatabase
https://docs.joomla.org/J4.x:Moving_Joomla_To_Prepared_Statements

Db Connection and Query Code

You'll notice above each step is set into a variable and passed to the next step.

use Joomla\CMS\Factory;
$db = Factory::getContainer()->get('DatabaseDriver');
$query = $myDb->getQuery(true);
$query = 'MySQL query here';
$db->setQuery($query);
$theReturnedVal = $db->loadResult();

The $db and $query variables can be any variables of your choosing, and with Joomla using $db and $query throughout their queries, I (and others) prefer to use our own variables just to rule out any conflict, and the most common is to use $myDb and $myQuery respectively - and what I choose to do as well.

use Joomla\CMS\Factory;
$myDb = Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery = 'MySQL query here';
$myDb->setQuery($myQuery);
$myReturnedVal = $myDb->loadResult();

Real Example Code

*Note, to execute PHP within a Joomla article, a "custom code" plugin is needed, I use Regular Labs Soucerer (https://regularlabs.com/sourcerer), and can see how that is used in the "Joomla Database Queries Examples" (ID: 10) article. So the below code will not run since it's not within a plugin snippet and is for display only.

*In Joomla's documention above, you'll see them using "quote" and "quoteName", within Joomla's framework, they have quote Methods to prevent SQL injection attacks using your queries; and is part of the framework's overall code securing:
https://docs.joomla.org/Secure_coding_guidelines

The below query code does not contain the quote Methods in order keep this example code clean and simplified.

// Load a single value with loadResult():

use Joomla\CMS\Factory;
$myDb = Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery = 'SELECT last_name FROM formula1_drivers WHERE id = 1';
$myDb->setQuery($myQuery);
$lastName = $myDb->loadResult();

echo 'Last Name of ID(1) is: '.$lastName;

Manually run above code within phpMyAdmin:
SELECT last_name FROM formula1_drivers WHERE id = 1;

// Load a column of values with loadColumn() and based on passed down variable:

use Joomla\CMS\Factory;
$myDb = Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery = 'SELECT team FROM formula1_drivers WHERE id = 1';
$myDb->setQuery($myQuery);
$teamId = $myDb->loadResult();
// This returns ID 9 in this case, to pass to the below query

use Joomla\CMS\Factory;
$myDb = Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery = 'SELECT last_name FROM formula1_drivers WHERE team = '.$teamId;
$myDb->setQuery($myQuery);
$lastNamesCol = $myDb->loadColumn();

// This creates an array of all the Last Names on Team ID 9 do do whatever with. Such as:
$lastNamesCommaSep = implode(', ',$lastNamesCol);

echo 'Last Names of Team ID comma separated: '.$lastNamesCommaSep;

Manually run the aobe query code in phpMyAdmin:
SELECT last_name FROM formula1_drivers WHERE team = 9;

View these queries executed >