J3.x:Accessing the database using JDatabase - Revision history

Wilsonge: Remove and add redirect

2013-09-01T21:55:00Z

Remove and add redirect

Wilsonge: Wilsonge moved page Accessing the database using JDatabase/3.1 to J3.1:Accessing the database using JDatabase: Move to 3.1 namespace

2013-06-06T10:03:46Z

Wilsonge moved page Accessing the database using JDatabase/3.1 to J3.1:Accessing the database using JDatabase: Move to 3.1 namespace

← Older revision	Revision as of 10:03, 6 June 2013
(No difference)

Wilsonge: /* The Connection */

2013-04-29T09:55:48Z

The Connection

← Older revision		Revision as of 09:55, 29 April 2013
Line 25:		Line 25:
	</source>		</source>

	{{JVer\|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.		{{JVer\|3.x}} 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.x core or extension code should use JFactory::getDbo() to obtain a database connection.

	===The Query===		===The Query===

Wilsonge: Wilsonge moved page Accessing the database using JDatabase/3.0 to Accessing the database using JDatabase/3.1: Move to version 3.1

2013-04-29T08:54:39Z

Wilsonge moved page Accessing the database using JDatabase/3.0 to Accessing the database using JDatabase/3.1: Move to version 3.1

← Older revision	Revision as of 08:54, 29 April 2013
(No difference)

Wilsonge: Mark as incomplete

2013-04-27T12:44:47Z

Mark as incomplete

← Older revision		Revision as of 12:44, 27 April 2013
Line 1:		Line 1:
			{{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.		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.

Wilsonge: Remove Under Construction Tag as 56 days since last edit

2013-04-27T12:43:24Z

Remove Under Construction Tag as 56 days since last edit

← Older revision		Revision as of 12:43, 27 April 2013
Line 1:		Line 1:
	~~{{underconstruction}}~~

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

Martinkolle: /* Updating a Record */

2013-03-01T17:19:12Z

Updating a Record

← Older revision		Revision as of 17:19, 1 March 2013
Line 182:		Line 182:

	====Updating a Record====		====Updating a Record====
			This is just a simple example to update the params column in the menu table.
			<source lang="php">
			// Get a db connection.
			$db = JFactory::getDbo();

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

			//Build the query
			$query->update("#__menu");
			$query->set('params = '.$db->quote($param));
			$query->where('id = '. $db->quote($itemid));
			$db->setQuery($query);

			//execute db object
			try {
			// Execute the query in Joomla 3.0.
			$result = $db->execute();
			} catch (Exception $e) {
			//print the errors
			print_r($e);
			}
			</source>

	====Deleting a Record====		====Deleting a Record====

Elkuku: Undo revision 79968 by Matthewtbaker (talk) this is a parse error ;)

2013-01-18T20:12:31Z

Undo revision 79968 by Matthewtbaker (talk) this is a parse error ;)

← Older revision		Revision as of 20:12, 18 January 2013
Line 60:		Line 60:
	// Select all records from the user profile table where key begins with "custom.".		// Select all records from the user profile table where key begins with "custom.".
	// Order it by the ordering field.		// Order it by the ordering field.
	$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'))		$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'));
	$query->from('#__user_profiles')		$query->from('#__user_profiles');
	$query->where('profile_key LIKE \'custom.%\'')		$query->where('profile_key LIKE \'custom.%\'');
	$query->order('ordering ASC');		$query->order('ordering ASC');

Matthewtbaker: /* Selecting Records from a Single Table */

2013-01-18T15:25:06Z

Selecting Records from a Single Table

← Older revision		Revision as of 15:25, 18 January 2013
Line 60:		Line 60:
	// Select all records from the user profile table where key begins with "custom.".		// Select all records from the user profile table where key begins with "custom.".
	// Order it by the ordering field.		// Order it by the ordering field.
	$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'));		$query->select(array('user_id', 'profile_key', 'profile_value', 'ordering'))
	$query->from('#__user_profiles');		$query->from('#__user_profiles')
	$query->where('profile_key LIKE \'custom.%\'');		$query->where('profile_key LIKE \'custom.%\'')
	$query->order('ordering ASC');		$query->order('ordering ASC');

Haydenyoung: 3.0 documentation.

2012-11-20T07:26:00Z

3.0 documentation.

New page

{{underconstruction}}

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==

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===

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 platform to another.

To obtain a database connection, use the [http://api.joomla.org/Joomla-Platform/JFactory.html#getDbo JFactory getDbo] static method:

<source lang="php">
$db = JFactory::getDbo();
</source>

The getDbo method will return a reference to a high-level abstract class called [http://api.joomla.org/Joomla-Platform/Database/JDatabaseDriver.html JDatabaseDriver]; the Joomla Framework hides the specifics of the database server, which is loaded dynamically by getDbo via Joomla's configuration file.

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

<source lang="php">
$db = $this->getDbo();
</source>

{{JVer|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===

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:

<source lang="php">
$db = JFactory::getDbo();

$query = $db->getQuery(true);
</source>

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====

Below is an example of creating a database query using the JDatabaseQuery class. Using the select, from, where and order methods, we can create queries which are flexible, easily readable and portable:

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

The query can also be chained to simplify further:

<source lang="php">
$query
->select(array('user_id', 'profile_key', 'profile_value', 'ordering'))
->from('#__user_profiles')
->where('profile_key LIKE \'custom.%\'')
->order('ordering ASC');
</source>

Chaining can become useful when queries become longer and more complex.

====Selecting Records from Multiple Tables====

Using the JDatabaseQuery's [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#join join] methods, we can select records from multiple related tables. The generic "join" method takes two arguments; the join "type" (inner, outer, left, right) and the join condition. In the following example you will notice that we can use all of the keywords we would normally use if we were writing a native SQL query, including the AS keyword for aliasing tables and the ON keyword for creating relationships between tables. Also note that the table alias is used in all methods which reference table columns (I.e. select, where, order).

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

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

// Select all articles for users who have a username which starts with 'a'.
// Order it by the created date.
$query
->select(array('a.*', 'b.username', 'b.name'))
->from('#__content AS a')
->join('INNER', '#__users AS b ON (a.created_by = b.id)')
->where('b.username LIKE \'a%\'')
->order('a.created DESC');

// 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>

The join method above enables us to query both the content and user tables, retrieving articles with their author details. There are also convenience methods for [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#innerJoin inner], [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#leftJoin left], [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#rightJoin right] and [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#outerJoin outer] joins.

We can use multiple joins to query across more than two tables:

<source lang="php">
$query
->select(array('a.*', 'b.username', 'b.name', 'c.*', 'd.*'))
->from('#__content AS a')
->join('INNER', '#__users AS b ON (a.created_by = b.id)')
->join('LEFT', '#__user_profiles AS c ON (b.id = c.user_id)')
->join('RIGHT', '#__categories AS d ON (a.catid = d.id)')
->where('b.username LIKE \'a%\'')
->order('a.created DESC');
</source>

Notice how chaining makes the source code much more readable for these longer queries.

====Inserting a Record====

The JDatabaseQuery class provides a number of methods for building insert queries, the most common being [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#insert insert], [http://api.joomla.org/11.4/Joomla-Platform/Database/JDatabaseQuery.html#columns columns] and [http://api.joomla.org/Joomla-Platform/Database/JDatabaseQuery.html#values values].

<source lang="php">
// Get a db connection.
$db = JFactory::getDbo();

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

// Insert columns.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Insert values.
$values = array(1001, $db->quote('custom.message'), $db->quote('Inserting a record using insert()'), 1);

// Prepare the insert query.
$query
->insert($db->quoteName('#__user_profiles'))
->columns($db->quoteName($columns))
->values(implode(',', $values));

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

Since Joomla 3.0 there has been a change in how the Joomla database class executes a query.

When executing a query in the Joomla Framework 11.4 we use the JDatabase's [http://api.joomla.org/11.4/Joomla-Platform/Database/JDatabase.html#query query] method:

<source lang="php">
try {
// Execute the query in Joomla 2.5.
$result = $db->query();
} catch (Exception $e) {
// catch any database errors.
}
</source>

For Joomla Framework 12.1 and higher, we use the JDatabaseDriver's [http://api.joomla.org/Joomla-Platform/Database/JDatabaseDriver.html#execute execute] method:

<source lang="php">
try {
// Execute the query in Joomla 3.0.
$result = $db->execute();
} catch (Exception $e) {
// catch any database errors.
}
</source>

Since the phase out of PHP 4.x support, PHP's exception model has been widely implemented in the Joomla Framework. Therefore, we can wrap our database execution in a try...catch block to catch any unexpected database errors that may arise. Any database-specific errors will be thrown using an instance of the [http://api.joomla.org/Joomla-Platform/Database/JDatabaseException.html JDatabaseException] class although it is best practice to catch the generic Exception object from which JDatabaseException inherits.

====Updating a Record====

====Deleting a Record====