Difference between revisions of "Accessing the database using JDatabase"
From Joomla! Documentation
(→Preparaing the query: typos and added quoting example) |
|||
Line 4: | Line 4: | ||
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. | 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== |
<source lang="php"> | <source lang="php"> | ||
// Get a database object | // Get a database object | ||
− | $db = JFactory::getDBO(); | + | $db =& JFactory::getDBO(); |
− | $query = "SELECT * FROM #__example_table WHERE id = 999999"; | + | $query = "SELECT * FROM #__example_table WHERE id = 999999;"; |
$db->setQuery($query); | $db->setQuery($query); | ||
</source> | </source> | ||
− | 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 | + | 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 | + | 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: |
+ | <source lang="php"> | ||
+ | $query = " | ||
+ | SELECT * | ||
+ | FROM ".$db->nameQuote('#__example_table')." | ||
+ | WHERE ".$db->nameQuote('id')." = ".$db->quote('999999')."; | ||
+ | "; | ||
+ | </source> | ||
− | Whatever we want to do, we have to set the query with the setQuery() | + | 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== | ==Executing the Query== |
Revision as of 05:46, 6 February 2009
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
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()) .")";