Joomla! Documentation™

Joomla! 

3.x

Joomla! 

2.5

Nota de la Versión

Este tutorial se divide en dos partes independientes:

• Insertar, actualizar y eliminar datos de la base de datos.
• Seleccionar datos de una o más tablas y recuperarlos en una variedad de formas diferentes.

Esta sección de la documentación se ve la selección de datos de una tabla de base de datos y como recuperarlos en una variedad de formatos. Para ver la otra parte has clic aquí

Introducción

Joomla! ofrece una sofisticada capa de abstracción de la base de datos para simplificar su uso por parte de los desarrolladores de terceras partes. Las nuevas versiones de la Plataforma API de Joomla! proporciona funcionalidades adicionales que extiende la capa de base de datos; que incluye características tales como conectores para una mayor variedad de servidores de base de datos y la consulta encadenada. para mejorar la legibilidad del código de conexión y simplificar el código SQL.

Joomla puede utilizar diferentes tipos de sistemas de base de datos SQL y ejecutar una variedad de entornos con diferentes prefijos de las tablas. Además de estas funciones, la clase crea automáticamente la conexión a base de datos. Además de crear una instancia del objeto, necesitas sólo dos líneas de código para obtener un resultado de la base de datos, en una variedad de formatos. El uso de la capa de base de datos de Joomla! asegura una máxima compatibilidad y flexibilidad para tu extensión.

La Consulta

Las consultas a las base de datos de Joomla! a cambiado con la introducción de Joomla! 1.6. La forma recomendada de la construcción de consultas de base de datos es a través de la consulta "encadenada" (aunque la cadena de consultas aún son compatibles).

Consulta encadenada se refiere a un método de conexión de un número de métodos, uno tras otro, con cada método devolviendo un objeto que puede soportar el método siguiente, se mejora la legibilidad y la simplificación de código.

Para obtener una nueva instancia de la clase JDatabaseQuery utilizamos el método getQuery de JDatabaseDriver  :

$db = JFactory::getDbo();

$query = $db->getQuery(true);

JDatabaseDriver::getQuery toma un argumento opcional, $new, que puede ser true o false (el valor predeterminado es false).

La consulta a nuestra base de datos puede llamar a un número de métodos JDatabaseQuery; estos métodos encapsulan el lenguaje de consulta de la fuente de datos (en la mayoría de los casos SQL), ocultando la sintaxis específica de la consulta al desarrollador y aumentando la portabilidad de los código fuente del desarrollador.

Algunos de los métodos utilizados con más frecuencia incluyen: select, from, join, where y order. También hay métodos tales como insert, update y delete para la modificación de registros en la base de datos. Por el encadenamiento de estos y otros métodos de llamada, puedes crear casi cualquier consulta sobre tu base de datos sin comprometer la portabilidad del código..

Seleccionar Registros de una Sola Tabla

A continuación hay un ejemplo de creación de una consulta de base de datos utilizando la clase JDatabaseQuery. Con el uso de los métodos select, from, where y order, se pueden crear consultas, flexibles, fácilmente legibles y portables:

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

La consulta también puede ser encadenada para simplificar aún más:

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

El encadenamiento puede ser útil cuando las consultas se convierten en más y más complejas.

La agrupación se puede lograr muy simplemente. La siguiente consulta sería para contar el número de artículos en cada categoría.

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

Un límite se puede ajustar a una consulta con "setLimit". Por ejemplo, en la siguiente consulta, se devolverían hasta 10 registros.

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

Seleccionar Registros de Varias Tablas

Con el uso de los métodos join de JDatabaseQuery, se pueden seleccionar los registros de varias tablas relacionadas. El método genérico "join" toma dos argumentos; "tipo" de unión (inner, outer, left, right) y la condición de unión. En el siguiente ejemplo se da cuenta de como podemos usar todas las palabras clave que queremos utilizar normalmente si estamos escribiendo una consulta SQL nativa, incluyendo la palabra clave AS para asignar un alias a las tablas y la palabra clave ON para la creación de relaciones entre tablas. También ten en cuenta que la tabla de alias se utiliza en todos los métodos que hacen referencia a columnas de la tabla (Por ej.: 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:

• innerJoin()
• leftJoin()
• rightJoin()
• outerJoin()

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

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

Single Value Result

loadResult()

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

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

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

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

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

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

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)

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

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

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

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