J2.5

Difference between revisions of "Accessing the database using JDatabase"

From Joomla! Documentation

(→‎The Query: Introduction to query chaining.)
(→‎Selecting Records from a Single Table: Example of a simple query.)
Line 46: Line 46:
  
 
====Selecting Records from a Single Table====
 
====Selecting Records from a Single Table====
 +
 +
<source lang="php">
 +
// Get a db connection.
 +
$db = JFactory::getDbo();
 +
 +
// Create a new query object.
 +
$query = $db->getQuery(true);
 +
 +
// Select all records from the user profile table where key begins with "custom.".
 +
// Order it by the ordering field.
 +
$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'));
 +
$query->from('#__user_profiles');
 +
$query->where('profile_key LIKE \'custom.%\'');
 +
$query->order('ordering ASC');
 +
 +
// Reset the query using our newly populated query object.
 +
$db->setQuery($query);
 +
 +
// Load the results as a list of stdClass objects.
 +
$results = $db->loadObjectList();
 +
</source>
  
 
====Selecting Records from Multiple Tables====
 
====Selecting Records from Multiple Tables====

Revision as of 07:35, 19 November 2012

The "J2.5" namespace is a namespace scheduled to be archived. This page contains information for a Joomla! version which is no longer supported. It exists only as a historical reference, it will not be improved and its content may be incomplete and/or contain broken links.

Quill icon.png
Page Actively Being Edited!

This j2.5 page is actively undergoing a major edit for a short while.
As a courtesy, please do not edit this page while this message is displayed. The user who added this notice will be listed in the page history. This message is intended to help reduce edit conflicts; please remove it between editing sessions to allow others to edit the page. If this page has not been edited for several hours, please remove this template, or replace it with {{underconstruction}} or {{incomplete}}.

Joomla provides a sophisticated database abstraction layer to simplify the usage for third party developers. New versions of the Joomla Platform API provide additional functionality which extends the database layer further, and includes features such as connectors to a greater variety of database servers and the query chaining to improve readability of connection code and simplify SQL coding.

Usage[edit]

Joomla can use different kinds of SQL database systems and 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 need just two lines of code to get a result from the database in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension.

The Connection[edit]

Joomla supports a generic interface for connecting to the database, hiding the intricacies of a specific database server from the Framework developer, thus making Joomla code easier to port from one database to another.

To obtain a database connection, use the JFactory getDbo static method:

$db = JFactory::getDbo();

Alternatively, if your class inherits from the JModel (or a decendent class) getDbo method:

$db = $this->getDbo();

Joomla 3.0 users should note that the JModel class has been renamed JModelLegacy and will eventually be deprecated in favour of the class JModelBase. Therefore, any Joomla 3.0 core or extension code should use JFactory::getDbo() to obtain a database connection.

The Query[edit]

Joomla's database querying has changed since the new Joomla Framework was introduced "query chaining" is now the recommended method for building database queries (although string queries are still supported).

Query chaining refers to a method of connecting a number of methods, one after the other, with each method returning an object that can support the next method, improving readability and simplifying code.

To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

The JDatabaseDriver::getQuery takes an optional argument, $new, which can be true or false (the default being false).

To query our data source we can call a number of JDatabaseQuery methods; these methods encapsulate the data source's query language (in most cases SQL), hiding query-specific syntax from the developer and increasing the portability of the developer's source code.

Some of the more frequently used methods include; select, from, join, where and order. There are also methods such as insert, update and delete for modifying records in the data store. By chaining these and other method calls, you can create almost any query against your data store without compromising portability of your code.

Selecting Records from a Single Table[edit]

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Select all records from the user profile table where key begins with "custom.".
// Order it by the ordering field.
$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'));
$query->from('#__user_profiles');
$query->where('profile_key LIKE \'custom.%\'');
$query->order('ordering ASC');

// Reset the query using our newly populated query object.
$db->setQuery($query);

// Load the results as a list of stdClass objects.
$results = $db->loadObjectList();

Selecting Records from Multiple Tables[edit]

Inserting a Record[edit]

Updating a Record[edit]

Deleting a Record[edit]