Friday, March 27, 2015
   
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 | +2 Reply | Reply with quote | Quote
ced1870
# ced1870 Thu, 11th December 2014
Hi
thank you for sharing, you shall put your examples on the WIKI
https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

so that all people can use your great notes :)
CEd
Like | Dislike | +2 Reply | Reply with quote | Quote
Oddy
# Oddy Thu, 26th February 2015
Clearly represented jfactory querys.
This helped me a lot. Thank you very much. :-)
Like | Dislike | +2 Reply | Reply with quote | Quote
Scott N
# Scott Fri, 27th February 2015
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.
Like | Dislike | +1 Reply | Reply with quote | Quote
# Joel Sat, 28th February 2015
High Praise, Thx Scott! I still use this for myself, nice to know it was of some use to you.
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 25-Mar-15
      Hi Rishikesh, I use a database on a webserver with all the latitudes and longitudes of the points of ...
      Joel L.  
    • Tue 24-Mar-15
      At the starting asa I run the app. I want to show list of garages available near by my current location.
      Rishikesh  
    • Mon 23-Mar-15
      Ok!! The problem is in version of the app and SDK. Thank you, resolved :lol: .
      LucasR  
    • Sat 21-Mar-15
      It occurs with Eclipse. Close Eclipse and again open your project freshly and you will see your code ...
      Rizwan
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Mon 23-Mar-15
      Worked like a charm first time! :lol: Getting bored of the spam comments. This should stop them. :P
      Graham