Difference between revisions of "Selecting data using JDatabase"

From Joomla! Documentation

(Marked for translation)
(Marked this version for translation)
Line 2: Line 2:
 
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
 
<noinclude>{{Joomla version|version=3.x}}{{Joomla version|version=2.5|status=eos}}</noinclude>
 
{{-}}
 
{{-}}
{{tip|<translate>Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate>Version Note</translate>}}
+
{{tip|<translate><!--T:1-->
 +
Note many examples online use <code>$db->query()</code> instead of <code>$db->execute()</code>. This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.</translate>|title=<translate><!--T:2-->
 +
Version Note</translate>}}
  
<translate>This tutorial is split into two independent parts:</translate>
+
<translate><!--T:3-->
<translate>* Inserting, updating and removing data from the database.</translate>
+
This tutorial is split into two independent parts:</translate>
<translate>* Selecting data from one or more tables and retrieving it in a variety of different forms</translate>
+
<translate><!--T:4-->
 +
* Inserting, updating and removing data from the database.</translate>
 +
<translate><!--T:5-->
 +
* Selecting data from one or more tables and retrieving it in a variety of different forms</translate>
  
<translate>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 [[S:MyLanguage/Inserting,_Updating_and_Removing_data_using_JDatabase|click here]]</translate>
+
<translate><!--T:6-->
 +
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 [[S:MyLanguage/Inserting,_Updating_and_Removing_data_using_JDatabase|click here]]</translate>
  
 
<translate>
 
<translate>
== Introduction==
+
== Introduction== <!--T:7-->
 
</translate>
 
</translate>
<translate>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.</translate>
+
<translate><!--T:8-->
 +
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.</translate>
  
<translate>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.</translate>
+
<translate><!--T:9-->
 +
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.</translate>
  
 
<translate>
 
<translate>
==The Query==
+
==The Query== <!--T:10-->
 
</translate>
 
</translate>
<translate>Joomla's database querying changed with the introduction of Joomla 1.6. The recommended way of building database queries is through "query chaining" (although string queries are still supported).</translate>
+
<translate><!--T:11-->
 +
Joomla's database querying changed with the introduction of Joomla 1.6. The recommended way of building database queries is through "query chaining" (although string queries are still supported).</translate>
  
<translate>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.</translate>
+
<translate><!--T:12-->
 +
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.</translate>
  
<translate>To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>
+
<translate><!--T:13-->
 +
To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 32: Line 43:
 
</source>
 
</source>
  
<translate>The JDatabaseDriver::getQuery takes an optional argument, $new, which can be true or false (the default being false).</translate>
+
<translate><!--T:14-->
 +
The JDatabaseDriver::getQuery takes an optional argument, $new, which can be true or false (the default being false).</translate>
  
<translate>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.</translate>  
+
<translate><!--T:15-->
 +
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.</translate>  
  
<translate>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</translate>.
+
<translate><!--T:16-->
 +
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</translate>.
  
 
<translate>
 
<translate>
==Selecting Records from a Single Table==
+
==Selecting Records from a Single Table== <!--T:17-->
 
</translate>
 
</translate>
  
<translate>Below is an example of creating a database query using the <tt>JDatabaseQuery</tt> class. Using the select, from, where and order methods, we can create queries which are flexible, easily readable and portable:</translate>
+
<translate><!--T:18-->
 +
Below is an example of creating a database query using the <tt>JDatabaseQuery</tt> class. Using the select, from, where and order methods, we can create queries which are flexible, easily readable and portable:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 65: Line 80:
 
</source>
 
</source>
  
<translate>The query can also be chained to simplify further:</translate>
+
<translate><!--T:19-->
 +
The query can also be chained to simplify further:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 75: Line 91:
 
</source>
 
</source>
  
<translate>Chaining can become useful when queries become longer and more complex.</translate>
+
<translate><!--T:20-->
 +
Chaining can become useful when queries become longer and more complex.</translate>
  
<translate>Grouping can be achieved simply too.  The following query would count the number of articles in each category.</translate>
+
<translate><!--T:21-->
 +
Grouping can be achieved simply too.  The following query would count the number of articles in each category.</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 86: Line 104:
 
</source>
 
</source>
  
<translate>A limit can be set to a query using "setLimit". For example in the following query, it would return up to 10 records.</translate>
+
<translate><!--T:22-->
 +
A limit can be set to a query using "setLimit". For example in the following query, it would return up to 10 records.</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 96: Line 115:
  
 
<translate>
 
<translate>
==Selecting Records from Multiple Tables==
+
==Selecting Records from Multiple Tables== <!--T:23-->
 
</translate>
 
</translate>
<translate>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).</translate>
+
<translate><!--T:24-->
 +
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).</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 124: Line 144:
 
</source>
 
</source>
  
<translate>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 joins:</translate>
+
<translate><!--T:25-->
 +
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 joins:</translate>
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_innerJoin innerJoin()]
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_innerJoin innerJoin()]
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_leftJoin leftJoin()]
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_leftJoin leftJoin()]
Line 130: Line 151:
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_outerJoin outerJoin()]
 
* [http://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_outerJoin outerJoin()]
  
<translate>We can use multiple joins to query across more than two tables:</translate>
+
<translate><!--T:26-->
 +
We can use multiple joins to query across more than two tables:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 143: Line 165:
 
</source>
 
</source>
  
<translate>Notice how chaining makes the source code much more readable for these longer queries.</translate>
+
<translate><!--T:27-->
 +
Notice how chaining makes the source code much more readable for these longer queries.</translate>
  
<translate>In some cases, you will also need to use the AS clause when selecting items to avoid column name conflicts. In this case, multiple select statements can be chained in conjunction with using the second parameter of $db->quoteName.</translate>
+
<translate><!--T:28-->
 +
In some cases, you will also need to use the AS clause when selecting items to avoid column name conflicts. In this case, multiple select statements can be chained in conjunction with using the second parameter of $db->quoteName.</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 158: Line 182:
 
</source>
 
</source>
  
<translate>A second array can also be used as the second parameter of the select statement to populate the values of the AS clause. Remember to include nulls in the second array to correspond to columns in the first array that you don't want to use the AS clause for:</translate>
+
<translate><!--T:29-->
 +
A second array can also be used as the second parameter of the select statement to populate the values of the AS clause. Remember to include nulls in the second array to correspond to columns in the first array that you don't want to use the AS clause for:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 170: Line 195:
  
 
<translate>
 
<translate>
==Query Results ==
+
==Query Results == <!--T:30-->
 
</translate>
 
</translate>
<translate>The database class contains many methods for working with a query's result set.</translate>
+
<translate><!--T:31-->
 +
The database class contains many methods for working with a query's result set.</translate>
  
 
<translate>
 
<translate>
=== Single Value Result ===
+
=== Single Value Result === <!--T:32-->
 
</translate>
 
</translate>
 
<translate>
 
<translate>
==== loadResult() ====
+
==== loadResult() ==== <!--T:33-->
 
</translate>
 
</translate>
<translate>Use '''loadResult()''' when you expect just a single value back from your database query.</translate>  
+
<translate><!--T:34-->
 +
Use '''loadResult()''' when you expect just a single value back from your database query.</translate>  
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
 
|-
 
|-
! <translate>id</translate> !! <translate>name</translate> !! <translate>email</translate> !! <translate>username</translate>
+
! <translate><!--T:35-->
 +
id</translate> !! <translate><!--T:36-->
 +
name</translate> !! <translate><!--T:37-->
 +
email</translate> !! <translate><!--T:38-->
 +
username</translate>
 
|-  
 
|-  
| 1 || style="background:yellow" | John Smith || <translate>johnsmith@domain.example</translate> || johnsmith
+
| 1 || style="background:yellow" | John Smith || <translate><!--T:39-->
 +
johnsmith@domain.example</translate> || johnsmith
 
|-
 
|-
| 2 || Magda Hellman || <translate>magda_h@domain.example</translate> || magdah
+
| 2 || Magda Hellman || <translate><!--T:40-->
 +
magda_h@domain.example</translate> || magdah
 
|-
 
|-
| 3 || Yvonne de Gaulle || <translate>ydg@domain.example</translate> || ydegaulle
+
| 3 || Yvonne de Gaulle || <translate><!--T:41-->
 +
ydg@domain.example</translate> || ydegaulle
 
|}
 
|}
  
<translate>This is often the result of a 'count' query to get a number of records:</translate>
+
<translate><!--T:42-->
 +
This is often the result of a 'count' query to get a number of records:</translate>
  
 
<source lang="php">
 
<source lang="php">
Line 219: Line 254:
  
 
<translate>
 
<translate>
===Single Row Results ===
+
===Single Row Results === <!--T:43-->
 
</translate>
 
</translate>
<translate>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.</translate>
+
<translate><!--T:44-->
 +
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.</translate>
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
 
|-
 
|-
! <translate>id</translate> !! <translate>name</translate> !! <translate>email</translate> !! <translate>username</translate>
+
! <translate><!--T:45-->
 +
id</translate> !! <translate><!--T:46-->
 +
name</translate> !! <translate><!--T:47-->
 +
email</translate> !! <translate><!--T:48-->
 +
username</translate>
 
|- style="background:yellow"
 
|- style="background:yellow"
| 1 || John Smith || <translate>johnsmith@domain.example</translate> || johnsmith
+
| 1 || John Smith || <translate><!--T:49-->
 +
johnsmith@domain.example</translate> || johnsmith
 
|-
 
|-
| 2 || Magda Hellman || <translate>magda_h@domain.example</translate> || magdah
+
| 2 || Magda Hellman || <translate><!--T:50-->
 +
magda_h@domain.example</translate> || magdah
 
|-
 
|-
| 3 || Yvonne de Gaulle || <translate>ydg@domain.example</translate> || ydegaulle
+
| 3 || Yvonne de Gaulle || <translate><!--T:51-->
 +
ydg@domain.example</translate> || ydegaulle
 
|}
 
|}
  
 
<translate>
 
<translate>
==== loadRow() ====
+
==== loadRow() ==== <!--T:52-->
 
</translate>
 
</translate>
<translate><tt>loadRow()</tt> returns an indexed array from a single record in the table:</translate>  
+
<translate><!--T:53-->
 +
<tt>loadRow()</tt> returns an indexed array from a single record in the table:</translate>  
  
 
<source lang='php'>
 
<source lang='php'>
Line 248: Line 292:
 
<pre>Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) </pre>
 
<pre>Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) </pre>
  
<translate>You can access the individual values by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
+
<translate><!--T:54-->
 +
You can access the individual values by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
  
<translate>Notes:</translate>
+
<translate><!--T:55-->
<translate># The array indices are numeric starting from zero.</translate>
+
Notes:</translate>
<translate># Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
+
<translate><!--T:56-->
 +
# The array indices are numeric starting from zero.</translate>
 +
<translate><!--T:57-->
 +
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
  
 
<translate>
 
<translate>
==== loadAssoc() ====
+
==== loadAssoc() ==== <!--T:58-->
 
</translate>
 
</translate>
<translate><tt>loadAssoc()</tt> returns an associated array from a single record in the table:</translate>
+
<translate><!--T:59-->
 +
<tt>loadAssoc()</tt> returns an associated array from a single record in the table:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 266: Line 315:
 
</source>
 
</source>
  
<translate>will give:
+
<translate><!--T:60-->
 +
will give:
 
<pre>Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )</pre></translate>
 
<pre>Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )</pre></translate>
  
<translate>You can access the individual values by using:</translate><pre>$row['name'] // e.g. $row['name']</pre>
+
<translate><!--T:61-->
 +
You can access the individual values by using:</translate><pre>$row['name'] // e.g. $row['name']</pre>
  
<translate>Notes:
+
<translate><!--T:62-->
 +
Notes:
 
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
 
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
  
 
<translate>
 
<translate>
==== loadObject() ====
+
==== loadObject() ==== <!--T:63-->
 
</translate>
 
</translate>
<translate>loadObject returns a PHP object from a single record in the table:</translate>
+
<translate><!--T:64-->
 +
loadObject returns a PHP object from a single record in the table:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 286: Line 339:
 
</source>
 
</source>
  
<translate>will give:
+
<translate><!--T:65-->
 +
will give:
 
<pre>stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )</pre></translate>
 
<pre>stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )</pre></translate>
  
<translate>You can access the individual values by using:</translate><pre>$result->index // e.g. $result->email</pre>
+
<translate><!--T:66-->
 +
You can access the individual values by using:</translate><pre>$result->index // e.g. $result->email</pre>
  
<translate>Notes:
+
<translate><!--T:67-->
 +
Notes:
 
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
 
# Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.</translate>
  
 
<translate>
 
<translate>
===Single Column Results ===
+
===Single Column Results === <!--T:68-->
 
</translate>
 
</translate>
<translate>Each of these results functions will return a single column from the database.</translate>  
+
<translate><!--T:69-->
 +
Each of these results functions will return a single column from the database.</translate>  
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
 
|-
 
|-
! <translate>id</translate> !! <translate>name</translate> !! <translate>email</translate> !! <translate>username</translate>
+
! <translate><!--T:70-->
 +
id</translate> !! <translate><!--T:71-->
 +
name</translate> !! <translate><!--T:72-->
 +
email</translate> !! <translate><!--T:73-->
 +
username</translate>
 
|-  
 
|-  
| 1 || style="background:yellow" | John Smith || <translate>johnsmith@domain.example</translate> || johnsmith
+
| 1 || style="background:yellow" | John Smith || <translate><!--T:74-->
 +
johnsmith@domain.example</translate> || johnsmith
 
|-
 
|-
| 2 || style="background:yellow" | Magda Hellman || <translate>magda_h@domain.example</translate> || magdah
+
| 2 || style="background:yellow" | Magda Hellman || <translate><!--T:75-->
 +
magda_h@domain.example</translate> || magdah
 
|-
 
|-
| 3 || style="background:yellow" | Yvonne de Gaulle || <translate>ydg@domain.example</translate> || ydegaulle
+
| 3 || style="background:yellow" | Yvonne de Gaulle || <translate><!--T:76-->
 +
ydg@domain.example</translate> || ydegaulle
 
|}
 
|}
  
 
<translate>
 
<translate>
==== loadColumn() ====
+
==== loadColumn() ==== <!--T:77-->
 
</translate>
 
</translate>
<translate><tt>loadColumn()</tt> returns an indexed array from a single column in the table:</translate>
+
<translate><!--T:78-->
 +
<tt>loadColumn()</tt> returns an indexed array from a single column in the table:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 324: Line 389:
 
</source>
 
</source>
  
<translate>will give:</translate>
+
<translate><!--T:79-->
 +
will give:</translate>
 
<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )</pre>
 
<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )</pre>
  
<translate>You can access the individual values by using:</translate><pre>$column['index'] // e.g. $column['2']</pre>
+
<translate><!--T:80-->
 +
You can access the individual values by using:</translate><pre>$column['index'] // e.g. $column['2']</pre>
  
<translate>Notes:
+
<translate><!--T:81-->
 +
Notes:
 
# The array indices are numeric starting from zero.
 
# The array indices are numeric starting from zero.
 
# <tt>loadColumn()</tt> is equivalent to loadColumn(0).</translate>
 
# <tt>loadColumn()</tt> is equivalent to loadColumn(0).</translate>
  
 
<translate>
 
<translate>
==== loadColumn($index) ====
+
==== loadColumn($index) ==== <!--T:82-->
 
</translate>
 
</translate>
<translate>loadColumn($index) returns an indexed array from a single column in the table:</translate>
+
<translate><!--T:83-->
 +
loadColumn($index) returns an indexed array from a single column in the table:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 347: Line 416:
 
</source>
 
</source>
  
<translate>will give:
+
<translate><!--T:84-->
 +
will give:
 
<pre>Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )</pre></translate>
 
<pre>Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )</pre></translate>
  
<translate>You can access the individual values by using:</translate><pre>$column['index'] // e.g. $column['2']</pre>
+
<translate><!--T:85-->
 +
You can access the individual values by using:</translate><pre>$column['index'] // e.g. $column['2']</pre>
  
<translate>loadColumn($index) allows you to iterate through a series of columns in the results</translate>
+
<translate><!--T:86-->
 +
loadColumn($index) allows you to iterate through a series of columns in the results</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 363: Line 435:
 
</source>
 
</source>
  
<translate>will give:</translate>
+
<translate><!--T:87-->
 +
will give:</translate>
 
<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )
 
<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )
 
Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )
 
Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )
Line 369: Line 442:
 
</pre>
 
</pre>
  
<translate>Notes:
+
<translate><!--T:88-->
 +
Notes:
 
# The array indices are numeric starting from zero.</translate>
 
# The array indices are numeric starting from zero.</translate>
  
 
<translate>
 
<translate>
=== Multi-Row Results ===
+
=== Multi-Row Results === <!--T:89-->
 
</translate>
 
</translate>
<translate>Each of these results functions will return multiple records from the database.</translate>  
+
<translate><!--T:90-->
 +
Each of these results functions will return multiple records from the database.</translate>  
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
 
|-
 
|-
! <translate>id</translate> !! <translate>name</translate> !! <translate>email</translate> !! <translate>username</translate>
+
! <translate><!--T:91-->
 +
id</translate> !! <translate><!--T:92-->
 +
name</translate> !! <translate><!--T:93-->
 +
email</translate> !! <translate><!--T:94-->
 +
username</translate>
 
|- style="background:yellow"
 
|- style="background:yellow"
 
| 1 || John Smith || johnsmith@domain.example || johnsmith
 
| 1 || John Smith || johnsmith@domain.example || johnsmith
Line 389: Line 468:
  
 
<translate>
 
<translate>
==== loadRowList() ====
+
==== loadRowList() ==== <!--T:95-->
 
</translate>
 
</translate>
<translate><tt>loadRowList()</tt> returns an indexed array of indexed arrays from the table records returned by the query:</translate>
+
<translate><!--T:96-->
 +
<tt>loadRowList()</tt> returns an indexed array of indexed arrays from the table records returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 400: Line 480:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:97-->
 +
will give (with line breaks added for clarity):</translate>
  
 
<pre>Array (  
 
<pre>Array (  
Line 408: Line 489:
 
)</pre>
 
)</pre>
  
<translate>You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
+
<translate><!--T:98-->
<translate>and you can access the individual values by using:</translate><pre>$row['index']['index'] // e.g. $row['2']['3']</pre>
+
You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
 +
<translate><!--T:99-->
 +
and you can access the individual values by using:</translate><pre>$row['index']['index'] // e.g. $row['2']['3']</pre>
  
<translate>Notes:
+
<translate><!--T:100-->
 +
Notes:
 
# The array indices are numeric starting from zero.</translate>
 
# The array indices are numeric starting from zero.</translate>
  
<translate>==== loadAssocList() ====</translate>
+
<translate>==== loadAssocList() ==== <!--T:101--></translate>
<translate><tt>loadAssocList()</tt> returns an indexed array of associated arrays from the table records returned by the query:</translate>
+
<translate><!--T:102-->
 +
<tt>loadAssocList()</tt> returns an indexed array of associated arrays from the table records returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 424: Line 509:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:103-->
 +
will give (with line breaks added for clarity):</translate>
 
<pre>Array (  
 
<pre>Array (  
 
[0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )  
 
[0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )  
Line 431: Line 517:
 
) </pre>
 
) </pre>
  
<translate>You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
+
<translate><!--T:104-->
<translate>and you can access the individual values by using:</translate><pre>$row['index']['column_name'] // e.g. $row['2']['email']</pre>
+
You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
 +
<translate><!--T:105-->
 +
and you can access the individual values by using:</translate><pre>$row['index']['column_name'] // e.g. $row['2']['email']</pre>
  
<translate>==== loadAssocList($key) ====</translate>
+
<translate>==== loadAssocList($key) ==== <!--T:106--></translate>
<translate><tt>loadAssocList('key')</tt> returns an associated array - indexed on 'key' - of associated arrays from the table records returned by the query:</translate>
+
<translate><!--T:107-->
 +
<tt>loadAssocList('key')</tt> returns an associated array - indexed on 'key' - of associated arrays from the table records returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 444: Line 533:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:108-->
 +
will give (with line breaks added for clarity):</translate>
 
<pre>Array (  
 
<pre>Array (  
 
[johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )  
 
[johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )  
Line 451: Line 541:
 
)</pre>
 
)</pre>
  
<translate>You can access the individual rows by using:</translate><pre>$row['key_value'] // e.g. $row['johnsmith']</pre>
+
<translate><!--T:109-->
<translate>and you can access the individual values by using:</translate><pre>$row['key_value']['column_name'] // e.g. $row['johnsmith']['email']</pre>
+
You can access the individual rows by using:</translate><pre>$row['key_value'] // e.g. $row['johnsmith']</pre>
 +
<translate><!--T:110-->
 +
and you can access the individual values by using:</translate><pre>$row['key_value']['column_name'] // e.g. $row['johnsmith']['email']</pre>
  
<translate>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.</translate>
+
<translate><!--T:111-->
 +
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.</translate>
  
<translate>==== loadAssocList($key, $column) ====</translate>
+
<translate>==== loadAssocList($key, $column) ==== <!--T:112--></translate>
<translate><tt>loadAssocList('key', 'column')</tt> returns an associative array, indexed on 'key', of values from the column named 'column' returned by the query:</translate>
+
<translate><!--T:113-->
 +
<tt>loadAssocList('key', 'column')</tt> returns an associative array, indexed on 'key', of values from the column named 'column' returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 466: Line 560:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:114-->
 +
will give (with line breaks added for clarity):</translate>
 
<pre>Array (  
 
<pre>Array (  
 
[1] => John Smith  
 
[1] => John Smith  
Line 473: Line 568:
 
)</pre>
 
)</pre>
  
<translate>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.</translate>
+
<translate><!--T:115-->
 +
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.</translate>
  
<translate>==== loadObjectList() ====</translate>
+
<translate>==== loadObjectList() ==== <!--T:116--></translate>
<translate><tt>loadObjectList()</tt> returns an indexed array of PHP objects from the table records returned by the query:</translate>
+
<translate><!--T:117-->
 +
<tt>loadObjectList()</tt> returns an indexed array of PHP objects from the table records returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 485: Line 582:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:118-->
 +
will give (with line breaks added for clarity):</translate>
 
<pre>Array (  
 
<pre>Array (  
 
[0] => stdClass Object ( [id] => 1 [name] => John Smith  
 
[0] => stdClass Object ( [id] => 1 [name] => John Smith  
Line 495: Line 593:
 
)</pre>
 
)</pre>
  
<translate>You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
+
<translate><!--T:119-->
<translate>and you can access the individual values by using:</translate><pre>$row['index']->name // e.g. $row['2']->email</pre>
+
You can access the individual rows by using:</translate><pre>$row['index'] // e.g. $row['2']</pre>
 +
<translate><!--T:120-->
 +
and you can access the individual values by using:</translate><pre>$row['index']->name // e.g. $row['2']->email</pre>
  
<translate>==== loadObjectList('key') ====</translate>
+
<translate>==== loadObjectList('key') ==== <!--T:121--></translate>
<translate><tt>loadObjectList($key)</tt> returns an associated array - indexed on 'key' - of objects from the table records returned by the query:</translate>
+
<translate><!--T:122-->
 +
<tt>loadObjectList($key)</tt> returns an associated array - indexed on 'key' - of objects from the table records returned by the query:</translate>
  
 
<source lang='php'>
 
<source lang='php'>
Line 508: Line 609:
 
</source>
 
</source>
  
<translate>will give (with line breaks added for clarity):</translate>
+
<translate><!--T:123-->
 +
will give (with line breaks added for clarity):</translate>
 
<pre>Array (  
 
<pre>Array (  
 
[johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith  
 
[johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith  
Line 518: Line 620:
 
)</pre>
 
)</pre>
  
<translate>You can access the individual rows by using:</translate><pre>$row['key_value'] // e.g. $row['johnsmith']</pre>
+
<translate><!--T:124-->
<translate>and you can access the individual values by using:</translate><pre>$row['key_value']->column_name // e.g. $row['johnsmith']->email</pre>
+
You can access the individual rows by using:</translate><pre>$row['key_value'] // e.g. $row['johnsmith']</pre>
 +
<translate><!--T:125-->
 +
and you can access the individual values by using:</translate><pre>$row['key_value']->column_name // e.g. $row['johnsmith']->email</pre>
  
<translate>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.</translate>
+
<translate><!--T:126-->
 +
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.</translate>
  
 
<translate>
 
<translate>
=== Miscellaneous Result Set Methods ===
+
=== Miscellaneous Result Set Methods === <!--T:127-->
 
</translate>
 
</translate>
 
<translate>
 
<translate>
==== getNumRows() ====
+
==== getNumRows() ==== <!--T:128-->
 
</translate>
 
</translate>
<translate><tt>getNumRows()</tt> 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.</translate>
+
<translate><!--T:129-->
 +
<tt>getNumRows()</tt> 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.</translate>
 
   
 
   
 
<source lang='php'>
 
<source lang='php'>
Line 540: Line 646:
 
</source>
 
</source>
  
<translate>will return</translate> <pre>3</pre>
+
<translate><!--T:130-->
<translate>Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning:</translate>
+
will return</translate> <pre>3</pre>
 +
<translate><!--T:131-->
 +
Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning:</translate>
 
<pre>Warning: mysql_num_rows(): 80 is not a valid MySQL result resource  
 
<pre>Warning: mysql_num_rows(): 80 is not a valid MySQL result resource  
 
in libraries\joomla\database\database\mysql.php on line 344</pre>
 
in libraries\joomla\database\database\mysql.php on line 344</pre>
Line 547: Line 655:
 
<noinclude>
 
<noinclude>
 
<translate>
 
<translate>
 +
<!--T:132-->
 
[[Category:Database]]
 
[[Category:Database]]
 
[[Category:JFactory]]
 
[[Category:JFactory]]

Revision as of 09:12, 16 September 2015

Other languages:
English • ‎Nederlands • ‎español • ‎français • ‎русский • ‎中文(中国大陆)‎ • ‎中文(台灣)‎
Joomla! 
3.x
Joomla! 
2.5
Version Note

Note many examples online use $db->query() instead of $db->execute(). This was the old method in Joomla 1.5 and 2.5 and will throw a deprecated notice in Joomla 3.0+.

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[edit]

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[edit]

Joomla's database querying changed with the introduction of Joomla 1.6. The recommended way of building database queries is through "query chaining" (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]

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();

The query can also be chained to simplify further:

$query
    ->select($db->quoteName(array('user_id', 'profile_key', 'profile_value', 'ordering')))
    ->from($db->quoteName('#__user_profiles'))
    ->where($db->quoteName('profile_key') . ' LIKE '. $db->quote('\'custom.%\''))
    ->order('ordering ASC');

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

Grouping can be achieved simply too. The following query would count the number of articles in each category.

$query
    ->select( array('catid', 'COUNT(*)') )
    ->from($db->quoteName('#__content'))
    ->group($db->quoteName('catid'));

A limit can be set to a query using "setLimit". For example in the following query, it would return up to 10 records.

$query
    ->select($db->quoteName(array('user_id', 'profile_key', 'profile_value', 'ordering')))
    ->from($db->quoteName('#__user_profiles'))
    ->setLimit('10');

Selecting Records from Multiple Tables[edit]

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

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

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

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

In some cases, you will also need to use the AS clause when selecting items to avoid column name conflicts. In this case, multiple select statements can be chained in conjunction with using the second parameter of $db->quoteName.

$query
    ->select($db->quoteName('a.*'))
    ->select($db->quoteName('b.username', 'username'))
    ->select($db->quoteName('b.name', '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');

A second array can also be used as the second parameter of the select statement to populate the values of the AS clause. Remember to include nulls in the second array to correspond to columns in the first array that you don't want to use the AS clause for:

$query
    ->select($db->quoteName(array('a.*', 'b.username', 'b.name'), array('', 'username', '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');

Query Results[edit]

The database class contains many methods for working with a query's result set.

Single Value Result[edit]

loadResult()[edit]

Use loadResult() when you expect just a single value back from your database query.

id name email username
1 John Smith johnsmith@domain.example johnsmith
2 Magda Hellman magda_h@domain.example magdah
3 Yvonne de Gaulle ydg@domain.example ydegaulle

This is often the result of a 'count' query to get a number of records:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from($db->quoteName('#__my_table'));
$query->where($db->quoteName('name')." = ".$db->quote($value));

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

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

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('field_name');
$query->from($db->quoteName('#__my_table'));
$query->where($db->quoteName('some_name')." = ".$db->quote($some_value));

$db->setQuery($query);
$result = $db->loadResult();

Single Row Results[edit]

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.

id name email username
1 John Smith johnsmith@domain.example johnsmith
2 Magda Hellman magda_h@domain.example magdah
3 Yvonne de Gaulle ydg@domain.example ydegaulle

loadRow()[edit]

loadRow() returns an indexed array from a single record in the table:

. . .
$db->setQuery($query);
$row = $db->loadRow();
print_r($row);

will give:

Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) 

You can access the individual values by using:

$row['index'] // e.g. $row['2']

Notes:

  1. The array indices are numeric starting from zero.
  2. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.

loadAssoc()[edit]

loadAssoc() returns an associated array from a single record in the table:

. . .
$db->setQuery($query);
$row = $db->loadAssoc();
print_r($row);

will give:

Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )

You can access the individual values by using:

$row['name'] // e.g. $row['name']

Notes:

  1. Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.

loadObject()[edit]

loadObject returns a PHP object from a single record in the table:

. . .
$db->setQuery($query);
$result = $db->loadObject();
print_r($result);

will give:

stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )

You can access the individual values by using:

$result->index // e.g. $result->email

Notes:

  1. 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[edit]

Each of these results functions will return a single column from the database.

id name email username
1 John Smith johnsmith@domain.example johnsmith
2 Magda Hellman magda_h@domain.example magdah
3 Yvonne de Gaulle ydg@domain.example ydegaulle

loadColumn()[edit]

loadColumn() returns an indexed array from a single column in the table:

$query->select('name'));
      ->from . . .";
. . .
$db->setQuery($query);
$column= $db->loadColumn();
print_r($column);

will give:

Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )

You can access the individual values by using:

$column['index'] // e.g. $column['2']

Notes:

  1. The array indices are numeric starting from zero.
  2. loadColumn() is equivalent to loadColumn(0).

loadColumn($index)[edit]

loadColumn($index) returns an indexed array from a single column in the table:

$query->select(array('name', 'email', 'username'));
      ->from . . .";
. . .
$db->setQuery($query);
$column= $db->loadColumn(1);
print_r($column);

will give:

Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )

You can access the individual values by using:

$column['index'] // e.g. $column['2']

loadColumn($index) allows you to iterate through a series of columns in the results

. . .
$db->setQuery($query);
for ( $i = 0; $i <= 2; $i++ ) {
  $column= $db->loadColumn($i);
  print_r($column);
}

will give:

Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )
Array ( [0] => johnsmith@domain.example [1] => magda_h@domain.example [2] => ydg@domain.example )
Array ( [0] => johnsmith [1] => magdah [2] => ydegaulle )

Notes:

  1. The array indices are numeric starting from zero.

Multi-Row Results[edit]

Each of these results functions will return multiple records from the database.

id name email username
1 John Smith johnsmith@domain.example johnsmith
2 Magda Hellman magda_h@domain.example magdah
3 Yvonne de Gaulle ydg@domain.example ydegaulle

loadRowList()[edit]

loadRowList() returns an indexed array of indexed arrays from the table records returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadRowList();
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[0] => Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) 
[1] => Array ( [0] => 2 [1] => Magda Hellman [2] => magda_h@domain.example [3] => magdah ) 
[2] => Array ( [0] => 3 [1] => Yvonne de Gaulle [2] => ydg@domain.example [3] => ydegaulle ) 
)

You can access the individual rows by using:

$row['index'] // e.g. $row['2']

and you can access the individual values by using:

$row['index']['index'] // e.g. $row['2']['3']

Notes:

  1. The array indices are numeric starting from zero.

loadAssocList()[edit]

loadAssocList() returns an indexed array of associated arrays from the table records returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadAssocList();
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith ) 
[1] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@domain.example [username] => magdah ) 
[2] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@domain.example [username] => ydegaulle ) 
) 

You can access the individual rows by using:

$row['index'] // e.g. $row['2']

and you can access the individual values by using:

$row['index']['column_name'] // e.g. $row['2']['email']

loadAssocList($key)[edit]

loadAssocList('key') returns an associated array - indexed on 'key' - of associated arrays from the table records returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadAssocList('username');
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith ) 
[magdah] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@domain.example [username] => magdah ) 
[ydegaulle] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@domain.example [username] => ydegaulle ) 
)

You can access the individual rows by using:

$row['key_value'] // e.g. $row['johnsmith']

and you can access the individual values by using:

$row['key_value']['column_name'] // e.g. $row['johnsmith']['email']

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.

loadAssocList($key, $column)[edit]

loadAssocList('key', 'column') returns an associative array, indexed on 'key', of values from the column named 'column' returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadAssocList('id', 'username');
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[1] => John Smith 
[2] => Magda Hellman 
[3] => Yvonne de Gaulle
)

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.

loadObjectList()[edit]

loadObjectList() returns an indexed array of PHP objects from the table records returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadObjectList();
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[0] => stdClass Object ( [id] => 1 [name] => John Smith 
    [email] => johnsmith@domain.example [username] => johnsmith ) 
[1] => stdClass Object ( [id] => 2 [name] => Magda Hellman 
    [email] => magda_h@domain.example [username] => magdah ) 
[2] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle 
    [email] => ydg@domain.example [username] => ydegaulle ) 
)

You can access the individual rows by using:

$row['index'] // e.g. $row['2']

and you can access the individual values by using:

$row['index']->name // e.g. $row['2']->email

loadObjectList('key')[edit]

loadObjectList($key) returns an associated array - indexed on 'key' - of objects from the table records returned by the query:

. . .
$db->setQuery($query);
$row = $db->loadObjectList('username');
print_r($row);

will give (with line breaks added for clarity):

Array ( 
[johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith 
    [email] => johnsmith@domain.example [username] => johnsmith ) 
[magdah] => stdClass Object ( [id] => 2 [name] => Magda Hellman 
    [email] => magda_h@domain.example [username] => magdah ) 
[ydegaulle] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle 
    [email] => ydg@domain.example [username] => ydegaulle ) 
)

You can access the individual rows by using:

$row['key_value'] // e.g. $row['johnsmith']

and you can access the individual values by using:

$row['key_value']->column_name // e.g. $row['johnsmith']->email

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[edit]

getNumRows()[edit]

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.

. . .
$db->setQuery($query);
$db->execute();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();

will return

3

Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning:

Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
in libraries\joomla\database\database\mysql.php on line 344