using Join with Joomla 3 select statement

I took a while to get this right.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query ->select(array(‘a.id’, ‘a.name’, ‘b.user_id’, ‘b.body’))
->from($db->quoteName(‘#__users’, ‘a’))
->join(‘INNER’, $db->quoteName(‘#__user_notes’, ‘b’) . ‘ ON (‘ . $db->quoteName(‘a.id’) . ‘ = ‘ . $db->quoteName(‘b.user_id’) . ‘)’)
->where($db->quoteName(‘b.body’) . ‘ = \'<p>anx001</p> \”);$db->setQuery($query);
$results = $db->loadObjectList();echo ‘<table border=”1″ cellpadding=”5″>’;
echo ‘<tr><td>ID</td><td>NAME</td></tr>’;foreach ($results as $row) {
echo “<tr><td>{$row->id}</td><td>{$row->name}</td></tr>”;
}
echo ‘</table>’;

The double quotes on the echo in the foreach loop was necessary. If I used single quotes it didn’t display the names I was looking for.

I hope this helps someone else out there….

 

Joomla 3 – sample query of the database

Here is a snippet of code I often need to build a database query in Joomla.

 


<?php

<?php

$db = JFactory::getDbo();
 
$query = $db->getQuery(true);

// Individual fields 
//$query->select($db->quoteName(array('firstname', 'lastname')));
 // or all fields
$query->select('*') ;

$query->from($db->quoteName('#__free_month')); 
$db->setQuery($query);   $results = $db->loadObjectList();

 echo '<ul>';

foreach ($results as $row) {

echo "<li>{$row->firstname} {$row->lastname}</li>";

}

echo '</ul>';


?>