Difference between revisions of "Accessing the database using JDatabase"

From Joomla! Documentation

(→‎Multi-Row Results: added content)
(This document is not orpahned by is part of the developers' documentation. Not sure why it was in the orphan section.)
 
(90 intermediate revisions by 28 users not shown)
Line 1: Line 1:
−
Joomla provides a sopisticated database abstraction layer to simplify the usage for 3PD. This guide should help you using this layer.
+
{{review|}}
 +
<noinclude><languages /></noinclude>
 +
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
 +
<translate>
 +
==Useful information== <!--T:1-->
 +
</translate>
 +
<translate><!--T:2-->
 +
Being able to use JDatabase is vital when you start developing for Joomla. The tutorial is split into two independent parts:</translate>
 +
<translate><!--T:3-->
 +
* [[S:MyLanguage/Inserting, Updating and Removing data using JDatabase|Inserting, updating and removing data from the database]].</translate>
 +
<translate><!--T:4-->
 +
* [[S:MyLanguage/Selecting data using JDatabase|Selecting data from one or more tables]] and retrieving it in a variety of different forms.</translate>
  
−
==Why should I use the Joomla database class?==
+
<translate>
−
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.
+
==Advanced information== <!--T:5-->
 +
</translate>
 +
<translate><!--T:6-->
 +
This tutorial looks at how to use transactions with JDatabase (available since {{JVer|3.x}} only):</translate>
 +
<translate><!--T:7-->
 +
* [[S:MyLanguage/Using transactions in Joomla|Using transactions in Joomla]]</translate>
 +
<translate><!--T:8-->
 +
This tutorial looks at how to use the union methods in JDatabaseQuery (available in working form since {{JVer|3.3}} only):
 +
* [[S:MyLanguage/Using the union methods in database queries|Using the union methods in database queries]]</translate>
  
−
==Preparing the query==
+
<translate>
−
<source lang="php">
+
==Supported Storage Connectors== <!--T:9-->
−
// Get a database object
+
</translate>
−
$db =& JFactory::getDBO();
+
<translate><!--T:10-->
 +
The table below outlines the database and storage connectors available for Joomla! as well as which version of Joomla they became available in.</translate>
  
−
$query = "SELECT * FROM #__example_table WHERE id = 999999;";
+
<translate><!--T:11-->
−
$db->setQuery($query);
+
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).</translate>
−
</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.
+
{| class="wikitable"
−
 
+
! scope="col" | <translate><!--T:12-->
−
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:
+
Database</translate>  
−
<source lang="php">
+
! scope="col" | <translate><!--T:13-->
−
$query = "
+
Joomla! Versions</translate>
−
  SELECT *
+
! scope="col" | <translate><!--T:14-->
−
    FROM ".$db->nameQuote('#__example_table')." 
+
Joomla DB</translate>
−
    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.
 
−
 
 
−
==Executing the Query==
 
−
To execute the query, Joomla provides several functions, which differ in their return value.
 
−
 
 
−
=== Basic Query Execution ===
 
−
* query
 
−
 
 
−
=== 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.
 
−
 
 
−
{| class="wikitable" style="text-align:center"
 
 
|-
 
|-
−
! id !! name !! email !! username
+
| MySQL || {{JVer|1.5}}{{JVer|2.5}}{{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:15-->
−
|-
+
Yes</translate>
−
| 1 || style="background:yellow" | John Smith || johnsmith@example.com || johnsmith
 
 
|-
 
|-
−
| 2 || Magda Hellman || magda_h@example.com || magdah
+
| Microsoft SQL Server || {{JVer|2.5}}{{JVer|3.0}} || <translate><!--T:16-->
 +
Yes</translate>
 
|-
 
|-
−
| 3 || Yvonne de Gaulle || ydg@example.com || ydegaulle
+
| Microsoft SQL Azure || {{JVer|2.5}}{{JVer|3.0}} || <translate><!--T:17-->
−
|}
+
Yes</translate>
−
 
 
−
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.
 
−
{| class="wikitable" style="text-align:center"
 
 
|-
 
|-
−
! id !! name !! email !! username
+
| Postgresql || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:18-->
−
|- style="background:yellow"
+
Yes (In {{JVer|4.0}} and higher only via the PDO library for postgres)</translate>
−
| 1 || John Smith || johnsmith@example.com || johnsmith
 
 
|-
 
|-
−
| 2 || Magda Hellman || magda_h@example.com || magdah
+
| Oracle DB || {{JVer|3.0}} || <translate><!--T:19-->
 +
No</translate>
 
|-
 
|-
−
| 3 || Yvonne de Gaulle || ydg@example.com || ydegaulle
+
| SQL Lite || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:20-->
−
|}
+
No</translate>
−
 
 
−
==== loadRow ====
 
−
 
 
−
loadRow() returns an indexed array from a single record in the table:
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$row = $db->loadRow();
 
−
print_r($row);
 
−
</source>
 
−
will give:
 
−
<pre>array('0' => '0', '1' => 'John Smith', '2' => 'johnsmith@example.com', '3' => 'johnsmith')</pre>
 
−
 
 
−
You can access the individual values by using:<pre>$row['index'] // e.g. $row['2']</pre>
 
−
 
 
−
Notes:
 
−
# The array indices are numeric starting from zero.
 
−
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful
 
−
 
 
−
==== loadAssoc ====
 
−
 
 
−
loadAssoc() returns an associated array from a single record in the table:
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$row = $db->loadRow();
 
−
print_r($row);
 
−
</source>
 
−
will give:
 
−
<pre>array('id' => '0', 'name' => 'John Smith', 'email' => 'johnsmith@example.com', 'username' => 'johnsmith')</pre>
 
−
 
 
−
You can access the individual values by using:<pre>$row['name'] // e.g. $row['name']</pre>
 
−
 
 
−
Notes:
 
−
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful
 
−
 
 
−
==== loadObject ====
 
−
 
 
−
loadRow returns a PHP object from a single record in the table:
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$result = $db->loadRow();
 
−
print_r($result);
 
−
</source>
 
−
will give:
 
−
<pre>stdObject('id' => '0', 'name' => 'John Smith', 'email' => 'johnsmith@example.com', 'username' => 'johnsmith')</pre>
 
−
 
 
−
 
 
−
You can access the individual values by using:<pre>$row->index // e.g. $row->email</pre>
 
−
 
 
−
Notes:
 
−
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful
 
−
 
 
−
===Single Column Results ===
 
−
 
 
−
Each of these results functions will return a single column from the database.
 
−
 
 
−
{| class="wikitable" style="text-align:center"
 
−
|-
 
−
! id !! name !! email !! username
 
−
|-  
 
−
| 1 || style="background:yellow" | John Smith || johnsmith@example.com || johnsmith
 
−
|-
 
−
| 2 || style="background:yellow" | Magda Hellman || magda_h@example.com || magdah
 
−
|-
 
−
| 3 || style="background:yellow" | Yvonne de Gaulle || ydg@example.com || ydegaulle
 
−
|}
 
−
 
 
−
==== loadResultArray ====
 
−
 
 
−
loadResultArray() returns an indexed array from a single column in the table:  
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$column= $db->loadResultArray();
 
−
print_r($column);
 
−
</source>
 
−
will give:
 
−
<pre>array('0' => 'John Smith', '1' => 'Magda Hellman', '2' => 'Yvonne de Gaulle')</pre>
 
−
 
 
−
You can access the individual values by using:<pre>$column['index'] // e.g. $column['2']</pre>
 
−
 
 
−
Notes:
 
−
# The array indices are numeric starting from zero.
 
−
# Whilst you can repeat the call to get further columns, one of the functions that returns multiple rows might be more useful
 
−
 
 
−
=== Multi-Row Results ===
 
−
 
 
−
Each of these results functions will return multiple records from the database.
 
−
 
 
−
{| class="wikitable" style="text-align:center"
 
 
|-
 
|-
−
! id !! name !! email !! username
+
| PHP Data Objects (PDO)* || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:21-->
−
|- style="background:yellow"
+
No</translate>
−
| 1 || John Smith || johnsmith@example.com || johnsmith
 
−
|- style="background:yellow"
 
−
| 2 || Magda Hellman || magda_h@example.com || magdah
 
−
|- style="background:yellow"
 
−
| 3 || Yvonne de Gaulle || ydg@example.com || ydegaulle
 
 
|}
 
|}
  
−
==== loadRowList ====
+
<translate><!--T:22-->
−
 
+
* PHP Data Objects is a database abstraction layer and is shipped with PHP 5.1+.</translate>
−
loadRowList() returns an indexed array of indexed arrays from the table records returned by the query:
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$row = $db->loadRowList();
 
−
print_r($row);
 
−
</source>
 
−
will give:
 
−
<pre>array(
 
−
[0] => array([0] => '0', [1] => 'John Smith', [2] => 'johnsmith@example.com', [3] => 'johnsmith')
 
−
[1] => array([0] => '1', [1] => 'Magda Hellman', [2] => 'magda_h@example.com', [3] => 'magdah')
 
−
[2] => array([0] => '2', [1] => 'Yvonne de Gaulle ', [2] => 'ydg@example.com', [3] => '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']['index'] // e.g. $row['2']['3']</pre>
 
−
 
 
−
Notes:
 
−
# The array indices are numeric starting from zero.
 
−
 
 
−
==== loadAssocList ====
 
−
 
 
−
loadAssocList() returns an indexed array of associated arrays from the table records returned by the query:
 
−
<source lang='php'>
 
−
. . .
 
−
$db->setQuery($query);
 
−
$row = $db->loadAssocList();
 
−
print_r($row);
 
−
</source>
 
−
will give:
 
−
<pre>array(
 
−
[0] => array(['id'] => '0', ['name'] => 'John Smith', ['email'] => 'johnsmith@example.com', ['username'] => 'johnsmith')
 
−
[1] => array(['id'] => '1', ['name'] => 'Magda Hellman', ['email'] => 'magda_h@example.com', ['username'] => 'magdah')
 
−
[2] => array(['id'] => '2', ['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 ====
 
−
 
 
−
loadRow 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:
 
−
<pre>array(
 
−
[0] => stdObject(['id'] => '0', ['name'] => 'John Smith', ['email'] => 'johnsmith@example.com', ['username'] => 'johnsmith')
 
−
[1] => stdObject((['id'] => '1', ['name'] => 'Magda Hellman', ['email'] => 'magda_h@example.com', ['username'] => 'magdah')
 
−
[2] => stdObject(['id'] => '2', ['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>
 
−
 
 
−
=== Misc Result Set Methods ===
 
−
* getNumRows
 
  
−
==Tips, Tricks & FAQ==
+
<noinclude>
−
We had a few people lately using sub-queries like these:
+
[[Category:Database{{#translation:}}]]
−
<source lang="SQL">
+
</noinclude>
−
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:
 
−
<source lang="php">
 
−
$query = "SELECT * FROM #__example2";
 
−
$database->setQuery($query);
 
−
$query = "SELECT * FROM #__example WHERE id IN (". implode(",", $database->loadArray()) .")";
 
−
</source>
 
−
<noinclude>[[Category:Development]]</noinclude>
 

Latest revision as of 03:17, 17 October 2020

Copyedit.png
This Article Needs Your Help

This article is tagged because it NEEDS REVIEW. You can help the Joomla! Documentation Wiki by contributing to it.
More pages that need help similar to this one are here. NOTE-If you feel the need is satistified, please remove this notice.


Other languages:
Bahasa Indonesia • ‎Deutsch • ‎English • ‎Nederlands • ‎español • ‎français • ‎português do Brasil • ‎română • ‎русский • ‎فارسی • ‎हिन्दी • ‎বাংলা • ‎中文（台灣）‎
Joomla! 
3.x
Joomla! 
2.5

Useful information[edit]

Being able to use JDatabase is vital when you start developing for Joomla. The tutorial is split into two independent parts:

Advanced information[edit]

This tutorial looks at how to use transactions with JDatabase (available since Joomla 3.x only):

This tutorial looks at how to use the union methods in JDatabaseQuery (available in working form since Joomla 3.3 only):

Supported Storage Connectors[edit]

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 Joomla DB
MySQL Joomla 1.5Joomla 2.5Joomla 3.0Joomla 4.0 Yes
Microsoft SQL Server Joomla 2.5Joomla 3.0 Yes
Microsoft SQL Azure Joomla 2.5Joomla 3.0 Yes
Postgresql Joomla 3.0Joomla 4.0 Yes (In Joomla 4.0 and higher only via the PDO library for postgres)
Oracle DB Joomla 3.0 No
SQL Lite Joomla 3.0Joomla 4.0 No
PHP Data Objects (PDO)* Joomla 3.0Joomla 4.0 No
  • PHP Data Objects is a database abstraction layer and is shipped with PHP 5.1+.
Retrieved from "https://docs.joomla.org/index.php?title=Accessing_the_database_using_JDatabase&oldid=756896"