joomla！读取数据库操作，使用JDatabase

https://docs.joomla.org/Selecting_data_using_JDatabase

Besides instantiating the object you need just two lines of code to get a result from the database in a variety of formats.

包括实例化数据库对象，只需要2行代码就可以熊数据库中读取数据

The recommended way of building database queries is through "query chaining" (although string queries are still supported).

推荐的查询是“链式查询”，字符串查询仍然支持。

JDatabaseQuery 实例的获取方法如下：

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);

1、从一个表里查数据

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

简写如下：

$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');

设置个数

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

2、从多个表里查数据

使用join方法，2个参数，type:inner, outer, left, right和条件

// 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');

//a表的created_by 使用的是b表的id，要一并把b表的username 和name读出来，条件是b表中的name是以开头<span style="font-family: 'Helvetica Neue', Helvetica, Helvetica, Arial, sans-serif; line-height: 21.2799987792969px;"> </span>

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

3、返回的结果

loadResult()返回一个结果，多个结果只返回第一个，就是一个值

Single Row Results:
loadRow()
结果形式如下：Array ( [0] => 1 [1] => John Smith [2] => johnsmith@domain.example [3] => johnsmith ) 


loadAssoc()
结果形式如下：Array ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )


loadObject()
结果形式如下：stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@domain.example [username] => johnsmith )


Multi-Row Results：
loadObjectList()
结果形式如下：
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 ) 
)

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);