使用 JDatabase 來選取資料
From Joomla! Documentation
請注意,很多網路上的範例會使用 $db->query()
, 而非$db->execute()
。 前者是使用在 Joomla 1.5 以及 2.5 的舊method,在 Joomla 3.0+會回傳 deprecated notice。
這個教學主要分為兩個部分:
- 從資料庫中插入,更新以及刪除資料。
- 從一個或多個資料表中選取資料並以不同的形式檢索。
此章節將先聚焦於如何從資料庫中的表格中選取資料,並重新以不同的形式檢索。欲查看其他部分請點擊此處
介紹
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 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.
從單個資料表中選取紀錄
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();
(Here the quoteName() function adds appropriate quotes around the column names to avoid conflicts with any database reserved word, now or in the future.)
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 method calls improves code readability and reduces code bloat as 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');
從多個資料表中選取紀錄
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(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 ' . $db->quote('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(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 ' . $db->quote('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('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 ' . $db->quote('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(array('a.*'))
->select($db->quoteName(array('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 ' . $db->quote('a%'))
->order($db->quoteName('a.created') . ' DESC');
在查詢語法中使用 OR
要在查詢語法中,使用多重的 WHERE 叢集,它們要被當成 AND 看待
So, for example, the query below will return results where the 'name' field AND the 'state' field match.
$query = $db
->getQuery(true)
->select('COUNT(*)')
->from($db->quoteName('#__my_table'))
->where($db->quoteName('name') . " = " . $db->quote($value)
->where($db->quoteName('state') . " = " . $db->quote($state));
To use a WHERE clause as an OR, the query can be written like this
$query = $db
->getQuery(true)
->select('COUNT(*)')
->from($db->quoteName('#__my_table'))
->where($db->quoteName('name') . " = " . $db->quote($name_one), 'OR')
->where($db->quoteName('name') . " = " . $db->quote($name_two));
it can also be written like this
$query = $db
->getQuery(true)
->select('COUNT(*)')
->from($db->quoteName('#__my_table'))
->where($db->quoteName('name') . " = " . $db->quote($name_one)
->orWhere($db->quoteName('name') . " = " . $db->quote($name_two));
Query Results
The database class contains many methods for working with a query's result set.
If there are no matches to the query, the result will be null.
Single Value Result
loadResult()
Use loadResult() when you expect just a single value back from your database query.
id | name | 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)
->select('COUNT(*)')
->from($db->quoteName('#__my_table'))
->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)
->select('field_name')
->from($db->quoteName('#__my_table'))
->where($db->quoteName('some_name') . " = " . $db->quote($some_value));
$db->setQuery($query);
$result = $db->loadResult();
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.
id | name | 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()
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:
- The array indices are numeric starting from zero.
- Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.
loadAssoc()
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['email']
Notes:
- Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.
loadObject()
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:
- 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
Each of these results functions will return a single column from the database.
id | name | 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()
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:
- The array indices are numeric starting from zero.
- loadColumn() is equivalent to loadColumn(0).
loadColumn($index)
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:
- The array indices are numeric starting from zero.
Multi-Row Results
Each of these results functions will return multiple records from the database.
id | name | 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()
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:
- The array indices are numeric starting from zero.
loadAssocList()
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)
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)
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()
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)
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
getNumRows()
getNumRows() will return the number of result rows found by the last SELECT or SHOW 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. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use getAffectedRows().
. . .
$db->setQuery($query);
$db->execute();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();
will return
3
Note: getNumRows() is only valid for statements like SELECT or SHOW that return an actual result set. If you run getNumRows() after loadRowList() - or any other retrieval method - you will 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
Sample Module Code
Below is the code for a simple Joomla module which you can install and run to demonstrate use of the JDatabase functionality, and which you can adapt to experiment with some of the concepts described above. If you are unsure about development and installing a Joomla module then following the tutorial at Creating a simple module will help.
Important note: In any Joomla extensions which you develop that you should avoid accessing the core Joomla tables directly like this and should instead use the Joomla APIs if at all possible, because the database structures may change without warning.
In a folder mod_db_select create the following 2 files:
mod_db_select.xml
<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
<name>Database select query demo</name>
<version>1.0.1</version>
<description>Code demonstrating use of Joomla Database class to perform SQL SELECT queries</description>
<files>
<filename module="mod_db_select">mod_db_select.php</filename>
</files>
</extension>
mod_db_select.php
<?php
defined('_JEXEC') or die('Restricted Access');
use Joomla\CMS\Factory;
$db = Factory::getDbo();
$me = Factory::getUser();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('name', 'email')))
->from($db->quoteName('#__users'))
->where($db->quoteName('id') . ' != ' . $db->quote($me->id))
->order($db->quoteName('name') . ' ASC');
$db->setQuery($query);
echo $db->replacePrefix((string) $query);
$results = $db->loadAssocList();
foreach ($results as $row) {
echo "<p>" . $row['name'] . ", " . $row['email'] . "<br></p>";
}
The code above selects and outputs the username and email of the records in the Joomla users table, apart from those of the currently logged-on user. The method Factory::getUser() returns the user object of the currently logged-on user, or if not logged on, then a blank user object, whose id field is set to zero.
The $db->replacePrefix((string) $query) expression returns the actual SQL statement, and outputting this can be useful in debugging.
Zip up the mod_db_select directory to create mod_db_select.zip.
Within your Joomla administrator go to Install Extensions and via the Upload Package File tab select this zip file to install this sample log module.
Make this module visible by editing it (click on it within the Modules page) then:
- making its status Published
- selecting a position on the page for it to be shown
- on the menu assignment tab specify the pages it should appear on
When you visit a site web page then you should see the module in your selected position, and it should output the SQL SELECT statement and the sequence of name, email values from the Joomla users table.