Print

JDatabase: using the Joomla database with examples

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:
copyraw 
// 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;
}
  1.  // Get a db connection. 
  2.  $db = JFactory::getDbo(); 
  3.   
  4.  // Create a new query object. 
  5.  $query = $db->getQuery(true); 
  6.   
  7.  // build the SQL query 
  8.  $query->select($db->quoteName(array('p.user_id', 'u.username', 'u.real_name'))); 
  9.  $query->from($db->quoteName('#__user_profiles p')); 
  10.  $query->join('INNER', $db->quoteName('#__users', 'u') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('p.user_id') . ')') 
  11.  $query->where($db->quoteName('u.real_name') . ' LIKE '. $db->quote('\'%smith%\'')); 
  12.  $query->order('u.real_name ASC'); 
  13.   
  14.  // Reset the query using our new query object 
  15.  $db->setQuery($query); 
  16.   
  17.  // Load the results as a list of stdClass objects (see later for more options on retrieving data). 
  18.  $rows = $db->loadObjectList(); 
  19.   
  20.  // Retrieve each value in the ObjectList 
  21.  foreach( $rows as $row ) { 
  22.      $this_user_id = $row->user_id; 
  23.      $this_user_name = $row->username; 
  24.      $this_user_realname = $row->real_name; 
  25.  } 


Chaining
A quick note on how to chain, the following two statements are equivalent:
copyraw 
-- 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');
  1.  -- note the semi-colons at the end of each line 
  2.  $query->select($db->quoteName(array('user_id', 'user_name'))); 
  3.  $query->from($db->quoteName('#__users')); 
  4.  $query->where($db->quoteName('user_id') . '=42'); 
  5.  $query->order('date_modified ASC'); 
  6.   
  7.  -- note the only semi-colon at the end of the statement 
  8.  $query->select($db->quoteName(array('user_id', 'user_name'))) 
  9.        ->from($db->quoteName('#__users')) 
  10.        ->where($db->quoteName('user_id') . '=42') 
  11.        ->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.
copyraw 
$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"
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select('email') 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('user_id').'=42'); 
  6.  $db->setQuery($query); 
  7.  $my_value = $db->loadResult(); 
  8.   
  9.  -- using the data 
  10.  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.
copyraw 
$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"
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('user_id').'=42'); 
  6.  $db->setQuery($query); 
  7.  $row = $db->loadRow(); 
  8.   
  9.  -- using the data 
  10.  $this_name     = $row[0];   // the retrieved value of "name" 
  11.  $this_email    = $row[1];   // the retrieved value of "email" 
  12.  $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:
copyraw 
$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"
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('user_id').'=42'); 
  6.  $db->setQuery($query); 
  7.  $row = $db->loadAssoc(); 
  8.   
  9.  -- using the data 
  10.  $this_name     = $row['name'];      // the retrieved value of "name" 
  11.  $this_email    = $row['email'];     // the retrieved value of "email" 
  12.  $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:
copyraw 
$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"
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('user_id').'=42'); 
  6.  $db->setQuery($query); 
  7.  $row = $db->loadObject(); 
  8.   
  9.  -- using the data 
  10.  $this_name     = $row->name;      // the retrieved value of "name" 
  11.  $this_email    = $row->email;     // the retrieved value of "email" 
  12.  $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.
copyraw 
$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];
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\'')); 
  6.  $db->setQuery($query); 
  7.  $rows = $db->loadRowList(); 
  8.   
  9.  -- using the data 
  10.  foreach( $rows as $row ) { 
  11.      $this_user_name = $row[0];       // the retrieved value of "name" 
  12.      $this_user_email = $row[1];      // the retrieved value of "email" 
  13.      $this_user_username = $row[2];   // the retrieved value of "username" 
  14.  } 
  15.   
  16.  -- for a specific value of a row 
  17.  $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.
copyraw 
$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'];
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\'')); 
  6.  $db->setQuery($query); 
  7.  $rows = $db->loadAssocList(); 
  8.   
  9.  -- using the data 
  10.  foreach( $rows as $row ) { 
  11.      $this_user_name = $row['name'];           // the retrieved value of "name" 
  12.      $this_user_email = $row['email'];         // the retrieved value of "email" 
  13.      $this_user_username = $row['username'];   // the retrieved value of "username" 
  14.  } 
  15.   
  16.  -- for a specific value of a row 
  17.  $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.
copyraw 
$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;
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\'')); 
  6.  $db->setQuery($query); 
  7.  $rows = $db->loadObjectList(); 
  8.   
  9.  -- using the data 
  10.  foreach( $rows as $row ) { 
  11.      $this_user_name = $row->name;           // the retrieved value of "name" 
  12.      $this_user_email = $row->email;         // the retrieved value of "email" 
  13.      $this_user_username = $row->username;   // the retrieved value of "username" 
  14.  } 
  15.   
  16.  -- for a specific value of a row 
  17.  $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!
copyraw 
$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
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->select(array('name', 'email', 'username')) 
  4.        ->from($db->quoteName('#__my_users')) 
  5.        ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\'')); 
  6.  $db->setQuery($query); 
  7.   
  8.  // get the count 
  9.  $my_count = $db->getNumRows(); 
  10.   
  11.  // retrieve the data 
  12.  $rows = $db->loadObjectList(); 
  13.   
  14.  echo $my_count; 
  15.   
  16.  -- If you try to retrieve the data before doing the count, you should get the following error: 
  17.  Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
  18.  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()
copyraw 
$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();
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->insert($db->quoteName('#__my_users')) 
  4.        ->columns(array('name', 'username')) 
  5.        ->values(implode(',', array($db->quote('Joe'), $db->quote('jlipman')) )); 
  6.  $db->setQuery($query); 
  7.  $result = $db->query(); 
  8.   
  9.  -- retrieve last inserted ID 
  10.  $last_inserted_id = $db->insertid(); 


UPDATE using JDatabaseQuery
Note: for Joomla v3.x, use db->execute() instead of db->query()
copyraw 
$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();
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->update($db->quoteName('#__my_users')) 
  4.        ->set(array($db->quoteName('name') . '=\'Joël\'', $db->quoteName('username') . '=\'joel.lipman\'')) 
  5.        ->where(array($db->quoteName('user_id') . '=42')); 
  6.  $db->setQuery($query); 
  7.  $result = $db->query(); 


INSERT using JDatabaseDriver
Here's an alternative method to add a record to a table in Joomla:
copyraw 
// 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')
  1.  // Create and populate an object. 
  2.  $user_record = new stdClass(); 
  3.  $user_record->name = 'Joel'; 
  4.  $user_record->username = 'jlipman'; 
  5.   
  6.  // Insert the object into the user table. 
  7.  $result = JFactory::getDbo()->insertObject('#__users', $user_record); 
  8.   
  9.  // 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:
copyraw 
// 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
  1.  // Create and populate an object. 
  2.  $user_record = new stdClass(); 
  3.   
  4.  // Must be a valid primary key value. 
  5.  $user_record->id = 42; 
  6.  $user_record->name = 'Joel'; 
  7.  $user_record->username = 'jlipman'; 
  8.   
  9.  // Update their details in the users table using id as the primary key. 
  10.  $result = JFactory::getDbo()->updateObject('#__users', $user_record, 'id'); 
  11.   
  12.  // 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()
copyraw 
$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();
  1.  $db = JFactory::getDbo(); 
  2.  $query = $db->getQuery(true); 
  3.  $query->delete($db->quoteName('#__my_users')) 
  4.        ->where(array($db->quoteName('user_id') . '=42')); 
  5.  $db->setQuery($query); 
  6.  $result = $db->query(); 





for Joomla 1.5.x

Basic Query - 1 result
copyraw 
$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
  1.  $db = JFactory::getDBO(); 
  2.  $query = " 
  3.    SELECT ".$db->nameQuote('field_name')." 
  4.      FROM ".$db->nameQuote('#__my_table')." 
  5.      WHERE ".$db->nameQuote('some_name')." = ".$db->quote($some_value); 
  6.  $db->setQuery($query); 
  7.  $result = $db->loadResult(); 
  8.   
  9.  -- $result = value of field_name 

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

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

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

-- $row->username
  1.  $query="SELECT username FROM users WHERE name='me'"; 
  2.  $db->setQuery($query); 
  3.  $row = $db->loadObject(); 
  4.   
  5.  -- $row->username 

loadRowList / loadAssocList
copyraw 
$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>";
-- }
  1.  $query="SELECT username FROM users WHERE name='me'"; 
  2.  $db->setQuery($query); 
  3.  $rows = $db->loadRowList();  // $db->loadAssocList(); 
  4.   
  5.  -- $rows['0']['username'] 
  6.   
  7.  -- foreach( $rows as $row ) { 
  8.  --    $this_user = $row['username']; 
  9.  --    $user_list_string.= "<li>$this_user</li>"; 
  10.  -- } 

loadObjectList
copyraw 
$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>";
-- }
  1.  $query="SELECT username FROM users WHERE name='me'"; 
  2.  $db->setQuery($query); 
  3.  $rows = $db->loadObjectList(); 
  4.   
  5.  -- $rows['2']->username 
  6.   
  7.  -- foreach( $rows as $row ){ 
  8.  --    $this_user = $row->username; 
  9.  --    $user_list_string.= "<li>$this_user</li>"; 
  10.  -- } 

getNumRows
copyraw 
$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
  1.  $query="SELECT username FROM users WHERE name='me'"; 
  2.  $db->setQuery($query); 
  3.  $db->query(); 
  4.  $num_rows = $db->getNumRows(); 
  5.  print_r($num_rows); 
  6.  $result = $db->loadRowList(); 
  7.   
  8.  -- eg. 3 
  9.  -- NOTE: Load row list after get num rows or PHP warning: 
  10.  -- Warning: mysql_num_rows(): 80 is not a valid MySQL result resource 
  11.  -- in \joomla\database\database\mysql.php on line 344 

Category: Joomla :: Article: 477