Difference between revisions of "Accessing the database using JDatabase"

Latest revision as of 03:17, 17 October 2020

This Article Needs Your Help

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:

Inserting, updating and removing data from the database.
Selecting data from one or more tables and retrieving it in a variety of different forms.

Advanced information[edit]

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

Using transactions in Joomla

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

Using the union methods in database queries

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		Yes
Microsoft SQL Server		Yes
Microsoft SQL Azure		Yes
Postgresql		Yes (In and higher only via the PDO library for postgres)
Oracle DB		No
SQL Lite		No
PHP Data Objects (PDO)*		No

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

@@ Line 1: / Line 1: @@
-====== How to use the database classes in your script ======
+{{review|}}
-Joomla provides a sopisticated database abstraction layer to simplify the usage for 3PD. This guide should help you using this layer.
+<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>
-=====1. 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>
+<translate>
+==Supported Storage Connectors== <!--T:9-->
+</translate>
+<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>
-=====2. Preparating the query=====
+<translate><!--T:11-->
-<source>
+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>
-// Get a database object
-$db = JFactory::getDBO();
-$query = "SELECT * FROM #__example_table WHERE id = 999999";
+{| class="wikitable"
-$db->setQuery($query);
+! scope="col" | <translate><!--T:12-->
-</source>
+Database</translate>
-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 setQuery(), this string is replaced with the correct prefix.
+! scope="col" | <translate><!--T:13-->
+Joomla! Versions</translate>
+! scope="col" | <translate><!--T:14-->
+Joomla DB</translate>
+|-
+| MySQL || {{JVer|1.5}}{{JVer|2.5}}{{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:15-->
+Yes</translate>
+|-
+| Microsoft SQL Server || {{JVer|2.5}}{{JVer|3.0}} || <translate><!--T:16-->
+Yes</translate>
+|-
+| Microsoft SQL Azure || {{JVer|2.5}}{{JVer|3.0}} || <translate><!--T:17-->
+Yes</translate>
+|-
+| Postgresql || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:18-->
+Yes (In {{JVer|4.0}} and higher only via the PDO library for postgres)</translate>
+|-
+| Oracle DB || {{JVer|3.0}} || <translate><!--T:19-->
+No</translate>
+|-
+| SQL Lite || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:20-->
+No</translate>
+|-
+| PHP Data Objects (PDO)* || {{JVer|3.0}}{{JVer|4.0}} || <translate><!--T:21-->
+No</translate>
+|}
-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, therefore we have to hand over these values to the function $db->Quote($value).
+<translate><!--T:22-->
+* PHP Data Objects is a database abstraction layer and is shipped with PHP 5.1+.</translate>
-Whatever we want to do, we have to set the query with the setQuery()-function. Although you could write the query directly as a parameter for setQuery(), its commonly done by first saving it in a variable, normally $query, and then handing this variable over. This helps writing clean, readable code.
+<noinclude>
+[[Category:Database{{#translation:}}]]
-=====3. Executing the Query=====
+</noinclude>
-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
-=====4. Query Results =====
-The database class contains many methods for working with a query's result set.
-==== Single Value Result ====
-  * loadResult
-====Single Row Results ====
-  * loadObject
-  * loadRow
-  * loadResultArray
-==== Multi-Row Results ====
-  * loadObjectList
-  * loadRowList
-  * loadAssocList
-==== Misc Result Set Methods ====
-  * getNumRows
-=====5. Tips, Tricks & FAQ=====
-We had a few people lately using sub-queries like these:
-<source>
-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>
-$query = "SELECT * FROM #__example2";
-$database->setQuery($query);
-$query = "SELECT * FROM #__example WHERE id IN (". implode(",", $database->loadArray()) .")";
-</source>

Difference between revisions of "Accessing the database using JDatabase"

From Joomla! Documentation

Latest revision as of 03:17, 17 October 2020

Useful information[edit]

Advanced information[edit]

Supported Storage Connectors[edit]