JDatabase: using the Joomla database with examples

: Joel Lipman; Joomla; 27 January 2013; Hits: 52456

What?
Apologies for posting yet another article on how to access the Joomla Database using the Joomla classes but this is just a quick note for myself as a reference sheet. Plus the official documentation don't have enough examples in my view. Source: http://docs.joomla.org/ for v1.5.x and for v2.5.x

This article applies to Joomla 1.5.x, 2.5.x and 3.x.

for Joomla 2.5.x

Building
Joomla lets you build up the queries which admittedly is cleaner and more organized. Here is a query which shows the building methods:

// Get a db connection.
$db = JFactory::getDbo();
 
// Create a new query object.
$query = $db->getQuery(true);

// build the SQL query
$query->select($db->quoteName(array('p.user_id', 'u.username', 'u.real_name')));
$query->from($db->quoteName('#__user_profiles p'));
$query->join('INNER', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('p.user_id') . ')')
$query->where($db->quoteName('u.real_name') . ' LIKE '. $db->quote('\'%smith%\''));
$query->order('u.real_name ASC');

// Reset the query using our new query object
$db->setQuery($query);

// Load the results as a list of stdClass objects (see later for more options on retrieving data).
$rows = $db->loadObjectList();

// Retrieve each value in the ObjectList
foreach( $rows as $row ) {
	$this_user_id = $row->user_id;
	$this_user_name = $row->username;
	$this_user_realname = $row->real_name;
}

// Get a db connection.
$db = JFactory::getDbo();
// Create a new query object.
$query = $db->getQuery(true);
// build the SQL query
$query->select($db->quoteName(array('p.user_id', 'u.username', 'u.real_name')));
$query->from($db->quoteName('#__user_profiles p'));
$query->join('INNER', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('p.user_id') . ')')
$query->where($db->quoteName('u.real_name') . ' LIKE '. $db->quote('\'%smith%\''));
$query->order('u.real_name ASC');
// Reset the query using our new query object
$db->setQuery($query);
// Load the results as a list of stdClass objects (see later for more options on retrieving data).
$rows = $db->loadObjectList();
// Retrieve each value in the ObjectList
foreach( $rows as $row ) {
$this_user_id = $row->user_id;
$this_user_name = $row->username;
$this_user_realname = $row->real_name;
}

Chaining
A quick note on how to chain, the following two statements are equivalent:

-- note the semi-colons at the end of each line
$query->select($db->quoteName(array('user_id', 'user_name')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('user_id') . '=42');
$query->order('date_modified ASC');

-- note the only semi-colon at the end of the statement
$query->select($db->quoteName(array('user_id', 'user_name')))
      ->from($db->quoteName('#__users'))
      ->where($db->quoteName('user_id') . '=42')
      ->order('date_modified ASC');

-- note the semi-colons at the end of each line
$query->select($db->quoteName(array('user_id', 'user_name')));
$query->from($db->quoteName('#__users'));
$query->where($db->quoteName('user_id') . '=42');
$query->order('date_modified ASC');
-- note the only semi-colon at the end of the statement
$query->select($db->quoteName(array('user_id', 'user_name')))
->from($db->quoteName('#__users'))
->where($db->quoteName('user_id') . '=42')
->order('date_modified ASC');

Single Value Result
This type of query is for when you want just one value from one column and one particular row.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('email')
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$my_value = $db->loadResult();

-- using the data
echo $my_value;    // will equal the retrieved value of "email"

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('email')
->from($db->quoteName('#__my_users'))
->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$my_value = $db->loadResult();
-- using the data
echo $my_value; // will equal the retrieved value of "email"

Single Row Result - loadRow()
This type of query is for when you want any of the values from one resulting row. You will need to know the column order the results are returned in.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadRow();

-- using the data
$this_name     = $row[0];   // the retrieved value of "name"
$this_email    = $row[1];   // the retrieved value of "email"
$this_username = $row[2];   // the retrieved value of "username"

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadRow();
-- using the data
$this_name = $row[0]; // the retrieved value of "name"
$this_email = $row[1]; // the retrieved value of "email"
$this_username = $row[2]; // the retrieved value of "username"

Single Row Result - loadAssoc()
This type of query is for when you want any of the values from one resulting row. Similar to "loadRow()" but you refer to the results using their column name:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadAssoc();

-- using the data
$this_name     = $row['name'];      // the retrieved value of "name"
$this_email    = $row['email'];     // the retrieved value of "email"
$this_username = $row['username'];  // the retrieved value of "username"

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadAssoc();
-- using the data
$this_name = $row['name']; // the retrieved value of "name"
$this_email = $row['email']; // the retrieved value of "email"
$this_username = $row['username']; // the retrieved value of "username"

Single Row Result - loadObject()
This type of query is for when you want any of the values from one resulting row. Similar to "loadRow()" but you refer to the results using their column name:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadObject();

-- using the data
$this_name     = $row->name;      // the retrieved value of "name"
$this_email    = $row->email;     // the retrieved value of "email"
$this_username = $row->username;  // the retrieved value of "username"

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('user_id').'=42');
$db->setQuery($query);
$row = $db->loadObject();
-- using the data
$this_name = $row->name; // the retrieved value of "name"
$this_email = $row->email; // the retrieved value of "email"
$this_username = $row->username; // the retrieved value of "username"

Multiple Row Results - loadRowList()
This type of query is for when you want a cycle through a list of rows and then retrieve the values each row holds.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadRowList();

-- using the data
foreach( $rows as $row ) {
	$this_user_name = $row[0];       // the retrieved value of "name"
	$this_user_email = $row[1];      // the retrieved value of "email"
	$this_user_username = $row[2];   // the retrieved value of "username"
}

-- for a specific value of a row
$a_user_email = $rows[2][1];

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadRowList();
-- using the data
foreach( $rows as $row ) {
$this_user_name = $row[0]; // the retrieved value of "name"
$this_user_email = $row[1]; // the retrieved value of "email"
$this_user_username = $row[2]; // the retrieved value of "username"
}
-- for a specific value of a row
$a_user_email = $rows[2][1];

Multiple Row Results - loadAssocList()
This type of query is for when you want a cycle through a list of rows and then retrieve the values each row holds.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadAssocList();

-- using the data
foreach( $rows as $row ) {
	$this_user_name = $row['name'];           // the retrieved value of "name"
	$this_user_email = $row['email'];         // the retrieved value of "email"
	$this_user_username = $row['username'];   // the retrieved value of "username"
}

-- for a specific value of a row
$a_user_email = $rows[2]['email'];

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadAssocList();
-- using the data
foreach( $rows as $row ) {
$this_user_name = $row['name']; // the retrieved value of "name"
$this_user_email = $row['email']; // the retrieved value of "email"
$this_user_username = $row['username']; // the retrieved value of "username"
}
-- for a specific value of a row
$a_user_email = $rows[2]['email'];

Multiple Row Results - loadObjectList()
This type of query is for when you want a cycle through a list of rows and then retrieve the values each row holds.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadObjectList();

-- using the data
foreach( $rows as $row ) {
	$this_user_name = $row->name;           // the retrieved value of "name"
	$this_user_email = $row->email;         // the retrieved value of "email"
	$this_user_username = $row->username;   // the retrieved value of "username"
}

-- for a specific value of a row
$a_user_email = $rows[2]->email;

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
$rows = $db->loadObjectList();
-- using the data
foreach( $rows as $row ) {
$this_user_name = $row->name; // the retrieved value of "name"
$this_user_email = $row->email; // the retrieved value of "email"
$this_user_username = $row->username; // the retrieved value of "username"
}
-- for a specific value of a row
$a_user_email = $rows[2]->email;

Get a Row Count - getNumRows()
Note that in Joomla, you have to run the count before using the data!

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
      ->from($db->quoteName('#__my_users'))
      ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);

// get the count
$my_count = $db->getNumRows();

// retrieve the data
$rows = $db->loadObjectList();

echo $my_count;

-- If you try to retrieve the data before doing the count, you should get the following error:
Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
in libraries\joomla\database\database\mysql.php on line 344

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('name', 'email', 'username'))
->from($db->quoteName('#__my_users'))
->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\''));
$db->setQuery($query);
// get the count
$my_count = $db->getNumRows();
// retrieve the data
$rows = $db->loadObjectList();
echo $my_count;
-- If you try to retrieve the data before doing the count, you should get the following error:
Warning: mysql_num_rows(): 80 is not a valid MySQL result resource
in libraries\joomla\database\database\mysql.php on line 344

INSERT using JDatabaseQuery
Note: for Joomla v3.x, use db->execute() instead of db->query()

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert($db->quoteName('#__my_users'))
      ->columns(array('name', 'username'))
      ->values(implode(',', array($db->quote('Joe'), $db->quote('jlipman')) ));
$db->setQuery($query);
$result = $db->query();

-- retrieve last inserted ID
$last_inserted_id = $db->insertid();

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert($db->quoteName('#__my_users'))
->columns(array('name', 'username'))
->values(implode(',', array($db->quote('Joe'), $db->quote('jlipman')) ));
$db->setQuery($query);
$result = $db->query();
-- retrieve last inserted ID
$last_inserted_id = $db->insertid();

UPDATE using JDatabaseQuery
Note: for Joomla v3.x, use db->execute() instead of db->query()

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update($db->quoteName('#__my_users'))
      ->set(array($db->quoteName('name') . '=\'Joël\'', $db->quoteName('username') . '=\'joel.lipman\''))
      ->where(array($db->quoteName('user_id') . '=42'));
$db->setQuery($query);
$result = $db->query();

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update($db->quoteName('#__my_users'))
->set(array($db->quoteName('name') . '=\'Joël\'', $db->quoteName('username') . '=\'joel.lipman\''))
->where(array($db->quoteName('user_id') . '=42'));
$db->setQuery($query);
$result = $db->query();

INSERT using JDatabaseDriver
Here's an alternative method to add a record to a table in Joomla:

// Create and populate an object.
$user_record = new stdClass();
$user_record->name = 'Joel';
$user_record->username = 'jlipman';

// Insert the object into the user table.
$result = JFactory::getDbo()->insertObject('#__users', $user_record);

// equivalent to INSERT INTO #__users (name, username) VALUES ('Joel', 'jlipman')

// Create and populate an object.
$user_record = new stdClass();
$user_record->name = 'Joel';
$user_record->username = 'jlipman';
// Insert the object into the user table.
$result = JFactory::getDbo()->insertObject('#__users', $user_record);
// equivalent to INSERT INTO #__users (name, username) VALUES ('Joel', 'jlipman')

UPDATE using JDatabaseDriver
Here's an alternative method to modify a record in a table in Joomla:

// Create and populate an object.
$user_record = new stdClass();

// Must be a valid primary key value.
$user_record->id = 42;
$user_record->name = 'Joel';
$user_record->username = 'jlipman';

// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__users', $user_record, 'id');

// equivalent to UPDATE #__users SET name='Joel', username='jlipman' WHERE id=42

// Create and populate an object.
$user_record = new stdClass();
// Must be a valid primary key value.
$user_record->id = 42;
$user_record->name = 'Joel';
$user_record->username = 'jlipman';
// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__users', $user_record, 'id');
// equivalent to UPDATE #__users SET name='Joel', username='jlipman' WHERE id=42

DELETE
Note: for Joomla v3.x, use db->execute() instead of db->query()

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->delete($db->quoteName('#__my_users'))
      ->where(array($db->quoteName('user_id') . '=42'));
$db->setQuery($query);
$result = $db->query();

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->delete($db->quoteName('#__my_users'))
->where(array($db->quoteName('user_id') . '=42'));
$db->setQuery($query);
$result = $db->query();

for Joomla 1.5.x

Basic Query - 1 result

$db = JFactory::getDBO();
$query = "
  SELECT ".$db->nameQuote('field_name')."
    FROM ".$db->nameQuote('#__my_table')."
    WHERE ".$db->nameQuote('some_name')." = ".$db->quote($some_value);
$db->setQuery($query);
$result = $db->loadResult();

-- $result = value of field_name

$db = JFactory::getDBO();
$query = "
SELECT ".$db->nameQuote('field_name')."
FROM ".$db->nameQuote('#__my_table')."
WHERE ".$db->nameQuote('some_name')." = ".$db->quote($some_value);
$db->setQuery($query);
$result = $db->loadResult();
-- $result = value of field_name

LoadRow / LoadAssoc

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$row = $db->loadRow(); // $row = $db->loadAssoc();

-- $row['username']

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$row = $db->loadRow(); // $row = $db->loadAssoc();
-- $row['username']

loadObject

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$row = $db->loadObject();

-- $row->username

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$row = $db->loadObject();
-- $row->username

loadRowList / loadAssocList

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$rows = $db->loadRowList();  // $db->loadAssocList();

-- $rows['0']['username']

-- foreach( $rows as $row ) {
--	$this_user = $row['username'];
--	$user_list_string.= "<li>$this_user</li>";
-- }

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$rows = $db->loadRowList(); // $db->loadAssocList();
-- $rows['0']['username']
-- foreach( $rows as $row ) {
-- $this_user = $row['username'];
-- $user_list_string.= "<li>$this_user</li>";
-- }

loadObjectList

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$rows = $db->loadObjectList();

-- $rows['2']->username

-- foreach( $rows as $row ){
--	$this_user = $row->username;
--	$user_list_string.= "<li>$this_user</li>";
-- }

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$rows = $db->loadObjectList();
-- $rows['2']->username
-- foreach( $rows as $row ){
-- $this_user = $row->username;
-- $user_list_string.= "<li>$this_user</li>";
-- }

getNumRows

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$db->query();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();

-- eg. 3
-- NOTE: Load row list after get num rows or PHP warning:
-- Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
-- in \joomla\database\database\mysql.php on line 344

$query="SELECT username FROM users WHERE name='me'";
$db->setQuery($query);
$db->query();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();
-- eg. 3
-- NOTE: Load row list after get num rows or PHP warning:
-- Warning: mysql_num_rows(): 80 is not a valid MySQL result resource
-- in \joomla\database\database\mysql.php on line 344

Category: Joomla :: Article: 477

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Registered Users Cannot Login - Super Users Can (12342) Joel Lipman (Joomla)
Applies to Joomla CMS v2.5.14 What? This article is a note on how I fixed an issue on a client site. The website had a...
Warning: Save failed. Another menu item has the same alias (10623) Joel Lipman (Joomla)
What? A quick article for Joomla CMS version 3.x on resolving a menu item alias conflict: Warning Save failed with the following...
ReCaptcha alternative: Integrate PlayThru in JComments 2.3 for Joomla 2.5 (111569) Joel Lipman (Joomla)
Applies to: Joomla CMS v2.5.11 (Joomla.org) jComments v2.3.0 (JoomlaTune.com) PlayThru v1.1.8 (AreYouAHuman.com) What? This article is...
Hide Menu Item from Registered and Special (7290) Joel Lipman (Joomla)
Hide Menu Item from Registered and Special Refer to this link if you want to clean out a front-end admin interface...
Joomla CMS: Register to Read More (5987) Joel Lipman (Joomla)
What? This article is a quick note on how to change your Joomla CMS "Read More" to "Register to Read More" all without...
Docman - Uploaded a document and can't see it (5907) Joel Lipman (Joomla)
As with all Joomla articles and files, you need to check that they have been published and approved. Sometimes you...
JComments errors in PHP 7: plgContentJComments and JCommentsACL (19627) Joel Lipman (Joomla)
What? During a site upgrade, the commenting extension for Joomla called JComments would return errors when using PHP...

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate

Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:

Donate to Joel Lipman via PayPal

Bitcoin:

Donate to Joel Lipman with Bitcoin

bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:

Donate to Joel Lipman with Ethereum

0xb038962F3809b425D661EF5D22294Cf45E02FebF