Using the Joomla Database Object

Official Documentation:

The query API

Joomla always has a connection to the DB open so you may as well use it. It provides a basic query builder syntax and you can also execute raw queries if your query's a bit weird.

Things to note

  • The db object has a 'quoteName' method for table and column names and a 'quote' method for values.
  • The quote methods have aliases: 'qn' and 'q'.
  • You can use #__ instead of your joomla db prefix_
$db = JFactory::getDbo();
$query = $db->getQuery(true);

// Selecting Examples

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

// Associate Array Result
$query->select('*');
$query->from($db->qn('#__my_table'));
$query->where($db->qn('some_cat')." = 5");
$db->setQuery($query);
$result = $db->loadAssocList();

// Array of Objects Result with a Join FTW
$query->select('*');
$query->from($db->qn('#__my_table','a'));
$query->join('INNER', $db->qn('#__users', 'b') . ' ON (' . $db->qn('a.created_by') . ' = ' . $db->qn('b.id') . ')');
$query->where($db->qn('some_cat') . ' = 5');
$query->order($db->qn('a.created') . ' DESC');
$db->setQuery($query);
$result = $db->loadObjectList();

// To get your whole query as an sql statement call
echo $query->__toString();;

// Use limit 0 for no limit if you want an offset without a limit. Offset will default to 0.
$query->setLimit($limit,$offset);

eg.
$query->setLimit(20); // get the first 20 records

// Both Insert and Update have a short cut method which requires you populate an object.

// Inserting
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->profile_value='Inserting a record using insertObject()';
$profile->ordering=1;
$result = $db->insertObject('#__user_profiles', $profile);

// Updating
$object = new stdClass();
$object->id = 1; // Must be a valid primary key value.
$object->title = 'My Custom Record';
$object->description = 'A custom record being updated in the database.';

// Note that we're chaining straight onto the factory call so you can execute in 1 line - wooh.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id');

// Deleting - here we delete a bunch of records using some wherein. 
$pks_string = implode(',',$delete_pks);
$query = $db->getQuery(true);
$query->delete($db->quoteName($table));
// WARNING: SQLi danger - make sure you've cast these to ints already!!!
$query->where($db->quoteName($pk) . ' IN (' . $pks_string . ')');
$db->setQuery($query);
$result = $db->execute();