Accessing the database using JDatabase
From Joomla! Documentation
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?[edit]
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[edit]
// 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[edit]
To execute the query, Joomla provides several functions, which differ in their return value.
Basic Query Execution[edit]
- query
Query Execution Information[edit]
- getAffectedRows
- explain
- insertid
Insert Query Execution[edit]
- insertObject
Query Results[edit]
The database class contains many methods for working with a query's result set.
Single Value Result[edit]
loadResult[edit]
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[edit]
- loadObject
- loadRow
- loadResultArray
Multi-Row Results[edit]
- loadObjectList
- loadRowList
- loadAssocList
Misc Result Set Methods[edit]
- getNumRows
Tips, Tricks & FAQ[edit]
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()) .")";