Select data from multiple tables with different name and columns

Question

I have this problem that I can't resolve. In CMS Joomla I need to select data from table #__adsmanager_ads column ad_healine and from table #__modules column title.
Then check if ad_headline == title load module (that equal to ad_healine)

I use this code for single table select:

<?php
 Get a db connection.
$db = JFactory::getDbo();
 // Create a new query object.
$query = $db->getQuery(true);
$query
    ->select($db->quoteName(array('ad_headline')))
    ->from($db->quoteName('#__adsmanager_ads'));

    $db->setQuery($query);
    $results = $db->loadObjectList();
    $objectoarr = json_decode(json_encode($results),true); // Convert stdClass object to array
    print_r($objectoarr);     
  ?>

========================================================================= Next i write code below and problem is almost solver

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query 
    ->select (array('a.ad_headline', 'b.title')) //Select columns from database
    ->from ($db->quoteName('#__adsmanager_ads','a'))
    ->join('LEFT', $db->quoteName('#__modules', 'b') . ' ON (' . $db->quoteName('a.ad_headline') . '=' . $db->quoteName('b.title').')');
$db->setQuery($query);
$result = $db->loadObjectList();
$objectoarr = json_decode(json_encode($result),true); // Convert stdClass object to array

foreach ($objectoarr as $adhealine){
        if ($adhealine[ad_headline] === $adhealine[title])
        {
            $document = &JFactory::getDocument();
            $renderer   = $document->loadRenderer('modules');
            $position   = 'inside-ad';
            $options   = array('style' => 'raw');
            echo $renderer->render($position, $options, null); 
        }
}
?>

But problem is that all modules set to position inside-ad loading there. But I need load only module which's name equivalent to ads name.

How can i solve this problem? Thank in advance

Answer 1

You want to use a join in your query to pull back just the records where the module title is the same as the ad headline, so, start by reading this Joomla Docs page and the MySQL page. Then you will need to create a query something like:

$query->from('#__adsmanager_ads AS ads');
$query->select('ads.ad_headline')
      ->join(
             'LEFT',
             $db->quoteName('#__modules') . ' ON m.title = ads.ad_headline'
             )

N.B. _Please note I've just typed this into the browser, so it's probably got mistakes in it.