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

idfirst_namelast_name
1calebnance
2marshallmathers
3anthonykiedis

Table: jos_access

iduser_idaccessusername
111JoomlaFTW
220Eminem
330RHCP

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] => sldr2223
)

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!

Load Comments