Thursday, November 27, 2014
   
Text Size
Login

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:

  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:

  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.

  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.

  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:

  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:

  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.

  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.

  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.

  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!

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

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

  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:

  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:

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

  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

  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

  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

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

loadRowList / loadAssocList

  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

  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

  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 

Comments   

passerby
# passerby Mon, 3rd June 2013
Thanks, just what I was looking for :)
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts