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


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


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"


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"


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"


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"


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


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'];


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;


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


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


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


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


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


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





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

LoadRow / LoadAssoc
$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

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>";
-- }

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>";
-- }

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

Add comment


Send

Comments   

0 Tim Friday, 15th January 2016, 5:53 pm
Your instructions seem simpler than Joomla.org's. But there is something that's confusing me both here and there. Why do you use, for example, 'p.user_id' and 'u.username' (for examples)? I understand when you join tables that you need to reference the table name - like 'users.user_id' . That would make sense. But in many examples as is here, there are just single variables before the column name like a, b, p, etc. And I' know there's no tables in Joomla named a, b, or p.

Thanks in advance for the clarification!
+6 Scott N Friday, 27th February 2015, 1:36 pm
Wow, thank you so much, Joomla documents are written for those who can read Joomla documents. :lol: Your examples have single handedly helped me to create my first component. Truly a million thanks for taking the time to share with us.

We're "standing on the shoulders of giants" thanks to you.
+3 Joel L Saturday, 28th February 2015, 11:05 am
High Praise, Thx Scott! I still use this for myself, nice to know it was of some use to you.
+6 Oddy Thursday, 26th February 2015, 8:24 am
Clearly represented jfactory querys.
This helped me a lot. Thank you very much. :-)
+5 ced1870 Thursday, 11th December 2014, 1:53 pm
Hi
thank you for sharing, you shall put your examples on the WIKI
docs.joomla.org/.../

so that all people can use your great notes :)
CEd
+5 passerby Monday, 3rd June 2013, 11:23 pm
Thanks, just what I was looking for :)