Joomla provides a sopisticated database abstraction layer to simplify the usage for 3PD. This guide should help you using this layer.

+

{{version/tutor|1.5,2.5,3.1}}

+

==Useful information==

+

If you are looking for practical information about accessing the database it has been moved to the [[J1.5:Accessing_the_database_using_JDatabase|Joomla! 1.5 page here]]

−

==Why should I use the Joomla database class?==

+

{{other versions/navbox|The '''{{PAGENAME}}''' Tutorial is available in these versions:-|version}}

−

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.

+

−

==Preparing the query==

+

==Supported Storage Connectors==

−

<source lang="php">

+

−

// Get a database object

+

−

$db =& JFactory::getDBO();

+

−

$query = "SELECT * FROM #__example_table WHERE id = 999999;";

+

The table below outlines the database and storage connectors available for Joomla! as well as which version of Joomla they became available in.

−

$db->setQuery($query);

+

−

</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 &ldquo;#__&rdquo;. In the next step, the $db->setQuery(), this string is replaced with the correct prefix.

+

To make a connector available in Joomla's installer or global configuration manager, you will need to ensure the PHP library is installed (E.g. for PHP5 and MySQL the php5-mysql library would need to be installed).

−

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 should be quoted. For example, MySQL uses backticks &#096;&#096; for names and single quotes &lsquo;&lsquo; for values. Joomla has some functions to do this for us and to ensure code compatibility between different databases. We can pass the names to the function $db->nameQuote($name) and the values to the function $db->Quote($value).

+

{| class="wikitable"

−

+

! scope="col" | Database

−

A fully quoted query example is:

+

! scope="col" | Joomla Versions

−

<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 $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.

+

−

+

−

==== setQuery($query) ====

+

−

+

−

The ''setQuery($query)'' method sets up a database query for later execution either by the query() method or one of the Load result methods. <pre>$db =& JFactory::getDBO();

+

−

$query = "/* some valid sql string */";

+

−

$db->setQuery($query);</pre>

+

−

+

−

Notes:

+

−

The parameter $query must be a valid sql string, it can either be added as a string parameter or as a variable; generally a variable is preferred as it leads to more legible code and can help in debugging.

+

−

+

−

setQuery() also takes three other parameters: $offset, $limit - both used in list pagination; and $prefix - an alternative table prefix. All three of these variables have default values set and can usually be ignored.

+

−

+

−

==Executing the Query==

+

−

To execute the query, Joomla provides several functions, which differ in their return value.

+

−

+

−

=== Basic Query Execution ===

+

−

+

−

==== query() ====

+

−

+

−

The ''query()'' method is the the basic tool for executing sql queries on a database. In Joomla it is most often used for updating or administering the database simple because the various load methods detail on this page have the query step built in to them.

+

−

+

−

The syntax is very straightforward:<pre>$db =& JFactory::getDBO();

+

−

$query = "/* some valid sql string */";

+

−

$db->setQuery($query);

+

−

$result = $db->query();</pre>

+

−

+

−

Note: $query() returns an appropriate database resource if successful, or FALSE if not

+

−

+

−

=== Query Execution Information ===

+

−

* getAffectedRows()

+

−

* explain()

+

−

* insertid()

+

−

+

−

=== Insert Query Execution ===

+

−

* insertObject()

+

−

+

−

==Query Results ==

+

−

The database class contains many methods for working with a query's result set.

+

−

+

−

=== Single Value Result ===

+

−

+

−

==== loadResult() ====

+

−

+

−

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:

+

−

<source lang='php'>

+

−

$db =& JFactory::getDBO();

+

−

$query = "

+

−

SELECT COUNT(*)

+

−

FROM ".$db->nameQuote('#__my_table')."

+

−

WHERE ".$db->nameQuote('name')." = ".$db->quote($value).";

+

−

";

+

−

$db->setQuery($query);

+

−

$count = $db->loadResult();

+

−

</source>

+

−

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).

+

−

<source lang='php'>

+

−

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

+

−

</source>

+

−

+

−

===Single Row Results ===

+

−

+

−

Each of these results functions will return a single record from the database even though there may be several records that meet the criteria that you have set. To get more records you need to call the function again.

You can access the individual rows by using:<pre>$row['key_value'] // e.g. $row['johnsmith']</pre>

+

−

and you can access the individual values by using:<pre>$row['key_value']['column_name'] // e.g. $row['johnsmith']['email']</pre>

+

−

+

−

Note: 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.

+

−

+

−

==== loadObjectList() ====

+

−

+

−

loadObjectList() returns an indexed array of PHP objects from the table records returned by the query:

+

−

<source lang='php'>

+

−

. . .

+

−

$db->setQuery($query);

+

−

$result = $db->loadObjectList();

+

−

print_r($result);

+

−

</source>

+

−

will give (with line breaks added for clarity):

+

−

<pre>Array (

+

−

[0] => stdClass Object ( [id] => 1 [name] => John Smith

+

−

[email] => johnsmith@example.com [username] => johnsmith )

+

−

[1] => stdClass Object ( [id] => 2 [name] => Magda Hellman

+

−

[email] => magda_h@example.com [username] => magdah )

+

−

[2] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle

+

−

[email] => ydg@example.com [username] => ydegaulle )

+

−

)</pre>

+

−

+

−

You can access the individual rows by using:<pre>$row['index'] // e.g. $row['2']</pre>

+

−

and you can access the individual values by using:<pre>$row['index']->name // e.g. $row['2']->email</pre>

+

−

+

−

==== loadObjectList('key') ====

+

−

+

−

loadObjectList($key) returns an associated array - indexed on 'key' - of objects from the table records returned by the query:

+

−

<source lang='php'>

+

−

. . .

+

−

$db->setQuery($query);

+

−

$row = $db->loadObjectList('username');

+

−

print_r($row);

+

−

</source>

+

−

will give (with line breaks added for clarity):

+

−

<pre>Array (

+

−

[johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith

+

−

[email] => johnsmith@example.com [username] => johnsmith )

+

−

[magdah] => stdClass Object ( [id] => 2 [name] => Magda Hellman

+

−

[email] => magda_h@example.com [username] => magdah )

+

−

[ydegaulle] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle

+

−

[email] => ydg@example.com [username] => ydegaulle )

+

−

)</pre>

+

−

+

−

You can access the individual rows by using:<pre>$row['key_value'] // e.g. $row['johnsmith']</pre>

+

−

and you can access the individual values by using:<pre>$row['key_value']->column_name // e.g. $row['johnsmith']->email</pre>

+

−

+

−

Note: 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.

+

−

+

−

=== Misc Result Set Methods ===

+

−

+

−

==== getNumRows() ====

+

−

+

−

getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you have to run it '''after''' the query and '''before''' you have retrieved any results.

+

−

<source lang='php'>

+

−

. . .

+

−

$db->setQuery($query);

+

−

$db->query();

+

−

$num_rows = $db->getNumRows();

+

−

print_r($rows);

+

−

$result = $db->loadResultList();

+

−

</source>

+

−

will return <pre>3</pre>

+

−

Note: if you run getNumRows() after loadResultList() - or any other retrieval method - you may get a PHP Warning:

+

−

<pre>Warning: mysql_num_rows(): 80 is not a valid MySQL result resource

+

−

in D:\xampp\htdocs\joomla1.5a\libraries\joomla\database\database\mysql.php on line 344</pre>

+

−

+

−

==Tips, Tricks & FAQ==

+

−

We had a few people lately using sub-queries like these:

+

−

<source lang="SQL">

+

−

SELECT * FROM #__example WHERE id IN (SELECT * FROM #__example2);

+

−

</source>

+

−

These kind of queries are only possible in MySQL 4.1 and above. Another way to achieve this, is splitting the query into two:

Revision as of 04:05, 6 June 2013

Useful information

If you are looking for practical information about accessing the database it has been moved to the Joomla! 1.5 page here

The Accessing the database using JDatabase Tutorial is available in these versions:-

Version:

There are no other articles for different Joomla! versions.

Supported Storage Connectors

The table below outlines the database and storage connectors available for Joomla! as well as which version of Joomla they became available in.

To make a connector available in Joomla's installer or global configuration manager, you will need to ensure the PHP library is installed (E.g. for PHP5 and MySQL the php5-mysql library would need to be installed).

Database

Joomla Versions

MySQL

Postgresql

Microsoft SQL Server

Microsoft SQL Azure

Oracle DB

SQL Lite

PHP Data Objects (PDO)*

PHP Data Objects is a database abstraction layer and is shipped with PHP 5.1+.