Joomla Platform Framework
The Joomla developers created their own PHP framework, and they built Joomla using this framework. And in turn we have access to all the Classes, Objects, etc. As well as 3rd party extension developers build their extensions using this framework - and is the case with Joomla.
As we're doing PHP coding within Fabrik, we're using Joomla's framework's Classes, Objects, Methods, etc - in addition to Fabrik's Classes, Objects, Methods, etc, that they've created.
*Note, to use Joomla's framework, it has to be within Joomla; such as within a Joomla extension (Component, Module, Plugin, etc), or within a Joomla Article, etc. It's possible to create a "outside" Joomla PHP file, such as manually creating a PHP directly on the server and include the Joomla libraries, but the below code is based on being within Joomla. An example of using the Joomla Framework externally is what I do for the BLT print files at the bottom of the BLT Series forms, and that'll be explained in a different article.
Joomla Db Queries
JFactory
Joomla core class used throughout giving access to core Objects, Methods, etc, such as connecting to the database, access Joomla's application Objects and Classes, getting logged in user data, etc. In regards to writing our database queries, we use the JFactory Class.
https://docs.joomla.org/Using_the_JFactory_class
Which allows us to access their JDatabase abstract (parent) class for connecting to the database, and Methods such as JDatabaseDriver, and JDatabase's extended child extended classes, objects, drivers, functions, etc; JDatabaseQuery, getDbo, getQuery, setQuery, loadResult, loadColumn, etc.
https://api.joomla.org/cms-3/classes/JDatabaseDriver.html
When we write database queries we use JDatabase's JDatabaseQuery's getDbo, getQuery, setQuery; and one of their return functions - loadResult, loadColumn, execute, etc.
1. To connect to the websites database we use the JFactory's database Object:
getDbo()
2. To get the query Object to start the database query, we use:
getQuery(true)
Regarding the "true" function argument above, the default getQuery is false so just getQuery() would be false, so we have to tell it "true" (yes) to get the query method.
We then use this getQuery to write our query.
3. And then SET the returned result of that getQuery within an object.
setQuery()
https://dev.mysql.com/doc/refman/8.0/en/set.html
4. And lastly we return that SET object using one of the result functions:
loadResult(), loadColumn(), loadRow(), loadAssoc(), loadObject(), etc.
Here's couple of Joomla doc articles going over this in more detail with examples:
https://docs.joomla.org/Selecting_data_using_JDatabase
https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase
Db Connection and Query Code
You'll notice above each step is set into a variable and passed to the next step.
$db = JFactory::getDbo();
$query = $db->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.
$myDb = JFactory::getDbo();
$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():
$myDb = JFactory::getDbo();
$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:
$myDb = JFactory::getDbo();
$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
$myDb = JFactory::getDbo();
$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;