{{dablink|'''Version Note:''' While this document pertains to Joomla! 2.5 and 3.x, <code>$db->execute()</code> does not exist in Joomla 2.5. In that case, change <code>$db->execute()</code> to <code>$db->query()</code>. Using <code>$db->query()</code> is possible in Joomla 3.x but will generate a deprecated notice.}}

+

{{dablink|'''Version Note:''' While this document pertains to Joomla! 2.5 and 3.x, <code>$db->query()</code> throws a deprecated notice in Joomla 3.0+. In that case, change <code>$db->query()</code> to <code>$db->execute()</code>. However note <code>$db->execute()</code> does not work in Joomla 2.5.}}

This tutorial is split into two independent parts:

This tutorial is split into two independent parts:

−

* Inserting, updating and removing data from the database. It also touches on transactions.

+

* Inserting, updating and removing data from the database.

−

* Selecting data from a table and retrieving it in a variety of different forms

+

* Selecting data from one or more tables and retrieving it in a variety of different forms

−

This section of the documentation looks at selecting data from a database table and retrieving it in a variety of formats. To see the other part [[User:Wilsonge/Inserting,_Updating_and_Removing_data_using_JDatabase|click here]]

+

This section of the documentation looks at selecting data from a database table and retrieving it in a variety of formats. To see the other part [[Inserting,_Updating_and_Removing_data_using_JDatabase|click here]]

== Introduction==

== Introduction==

Line 46:

Line 46:

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

Revision as of 10:04, 27 October 2013

This article is for Joomla! CMS Version(s)

Version Note: While this document pertains to Joomla! 2.5 and 3.x, $db->query() throws a deprecated notice in Joomla 3.0+. In that case, change $db->query() to $db->execute(). However note $db->execute() does not work in Joomla 2.5.

This 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

This section of the documentation looks at selecting data from a database table and retrieving it in a variety of formats. To see the other part click here

Introduction

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

$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

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:

// 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($db->quoteName(array('user_id','profile_key','profile_value','ordering')));$query->from($db->quoteName('#__user_profiles'));$query->where($db->quoteName('profile_key').' LIKE '.$db->quote('\'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 (see later for more options on retrieving data).$results=$db->loadObjectList();

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

Selecting Records from Multiple Tables

Using the JDatabaseQuery's 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).

// 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.// Note by putting 'a' as a second parameter will generate `#__content` AS `a`$query->select($db->quoteName(array('a.*','b.username','b.name')))->from($db->quoteName('#__content','a'))->join('INNER',$db->quoteName('#__users','b').' ON ('.$db->quoteName('a.created_by').' = '.$db->quoteName('b.id').')')->where($db->quoteName('b.username').' LIKE \'a%\'')->order($db->quoteName('a.created').' DESC');// Reset the query using our newly populated query object.$db->setQuery($query);// Load the results as a list of stdClass objects (see later for more options on retrieving data).$results=$db->loadObjectList();

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 inner, left, right and outer joins.

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.

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.

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