Working with Elxis database

Database in Elxis CMS is globally accessible via the $database object. The underlying library for handling the different database types that Elxis supports is ADOdb but there is no need to study ADOdb to work with Elxis Database. Just write SQL native queries and ADOdb will automatically make them compatible to any database type! In this mini guide we will show you by example some very common database related tasks.

General usage

Each transaction with Elxis database can be splitted in three steps:
1. We write the query and store it in a PHP variable for later usage.
Example: $query = "SELECT * FROM #__users";
2. We use a special function named "setQuery" which will prepare the query to be executed by our database.
Example: $database->setQuery($query);
3. We execute the query (and get the results if needed).
Example: $database->query();

Notes
a. The table prefix in our query should be written as "#__". setQuery function will convert it to the user selected database prefix (usually "elx_") automatically.
b. If we wish to get back a specific number of results we should use:
$database->setQuery($query, '#__', limit, limit_start);
Example: $database->setQuery($query, '#__', 10, 0);
You should NEVER use LIMIT or anything similar directly in your queries as this will break cross-database support!
c. If our query is short we can put it directly in setQuery function and skip step 1:
Example: $database->setQuery("SELECT * FROM #__users ORDER BY name ASC", '#__', 5, 0);

Get single result

This is the simplest query. To retrieve a single result from a row:
$query = "SELECT username FROM #__users WHERE id='62'";
$database->setQuery($query, '#__', 1, 0);
$result = $database->loadResult();
The above will return admin or false if no result is found.

Get a list of results

Similar as above but now we want to get a list of single results:
$query = "SELECT username FROM #__users";
$database->setQuery($query);
$rows = $database->loadResultArray();
The above will return an array of usernames or false if no result is found.

Get a single row

To retrieve a single row from the database:
$query = "SELECT * FROM #__users WHERE id='62'";
$database->setQuery($query, '#__', 1, 0);
$row = $database->loadRow();
The above will return a single result as an array of all table fields or false if no result is found.

Get a list of rows

To retrieve a list of rows from the database:
$query = "SELECT * FROM #__users";
$database->setQuery($query, '#__', 10, 0);
$rows = $database->loadRowList();
The above will return a list of rows as a multi-diamensional array or false if no result is found.

Get a single row as object

To retrieve a single row as an object from the database:
$query = "SELECT * FROM #__users WHERE id='62'";
$database->setQuery($query, '#__', 1, 0);
$database->loadObject($row);
The above will return a single result as an object of all table fields or false if no result is found.

The object elements are accessible like this: $row->name, $row->username, etc.

Get a list of rows as objects

To retrieve a list of rows as objects from the database:
$query = "SELECT * FROM #__users";
$database->setQuery($query, '#__', 10, 0);
$rows = $database->loadObjectList();
The above will return an array of objects or false if no result is found.

Loop through results
for ($i=0; $i<count($rows); $i++) {
$row = $rows[$i];
//row fields are accessible like this: $row->username, $row->name, etc.
}

Update database

$query = "UPDATE #__users SET name='George Papas', avatar='mypic.png' WHERE id='34'";
$database->setQuery($query);
$database->query();

Delete rows from database

$database->setQuery("DELETE FROM #__users WHERE id='78'");
$database->query();

Get random records from the database

In order not to break cross-database support we will use the ADOdb's "random" variable which is set automatically by ADOdb during Elxis startup.
$query = "SELECT * FROM #__users ORDER BY ".$database->_resource->random;
$database->setQuery($query, '#__', 10, 0);
$rows = $database->loadObjectList();
Note: $database->_resource allows us to directly access ADOdb.

Count rows

$database->setQuery("SELECT COUNT(id) FROM #__users");
$counter = intval($database->loadResult());
The above will return an integer or 0 if no result is found.