Using Left Join In Joomla (MySQL)

Wanted to show you how easy it is to use the Left Join function in MySQL inside of Joomla.

First you need to figure out which 2 tables you need to connect.

Table: jos_users

Table: jos_access

We want to get the access and username of each entry in the jos_users table.

Second you want to prepare the query with the LEFT JOIN for the 2 tables that you’d like to connect.

<?php
  // get a database object
  $db = &JFactory::getDBO();
  $query =  ' SELECT u.*, a.*'.
            ' FROM #__ users as u'.
            ' LEFT JOIN #__access as a' .
            ' ON u.id = a.user_id' .
            ' WHERE u.id = 1';
?>

Third we want to set the query.

<?php
  $db->setQuery($query);
?>

Finally we send the query, return the result and print it to the page.

<?php
  $result = $db->loadObject();
  print_r($result);
?>

The result will be in an object oriented list like this:

stdClass Object(
  [id] => 1
  [first_name] => 'caleb'
  [last_name] => 'nance'
  [access] = > 1
  [username] => 'JoomlaFTW'
)

This was an example of returning a single row back, if your query calls for multiple rows to be returned, you are going to want to change the loadObject() to loadObjectList(). What this does is places all of the results in a numbered array but still keeps the object oriented format, which is what I prefer, but to each there own. So let me know what you think, and hope this helped!