Help with LeftJoin and grouping results from joined table

2 posts by 2 authors in: Forums > CMS Builder
Last Post: January 4, 2022   (RSS)

By JeffC - November 21, 2021 - edited: November 21, 2021

Hi

I have a table called 'campaign_item'
I have a related table called 'campaign_item_status' that contains a dropdown field for 'title' and field called 'campaign_itemNum' to link it to the campaign_item. 
A campaign_item can have several statuses.

The output I have is this:

Campaign Item 1       |      Status 1
Campaign Item 1       |      Status 2
Campaign Item 1       |      Status 3
Campaign Item 2       |     
Campaign Item 3       |      Status 1
Campaign Item 3       |      Status 2

The output I would like is this:

Campaign Item 1       |      Status 1, Status 2, Status 3
Campaign Item 2       |     
Campaign Item 3       |      Status 1, Status 2   

In my header I have this:

// load record from 'campaign_item'
list($campaign_itemRecords, $campaign_itemMetaData) = getRecords(array(
'tableName' => 'campaign_item',
'loadUploads' => false,
'allowSearch' => false,	
 'leftJoin' => array(
 'campaign_item_status' => 'ON campaign_item.num = campaign_item_status.campaign_itemNum',	  
 )
));

And in my body I have this:

<?php foreach ($campaign_itemRecords as $record): ?>
<tr>
<td><?php echo htmlencode($record['title']) ?><td>
<td><?php echo htmlencode($record['campaign_item_status.title']) ?><td>
</tr>
<?php endforeach ?>

I think I might be on the right track, and have managed to group the statuses in the developer console with MySql. My code is below. But 12 hours in, and I can't join the pieces together.  

SELECT cms_campaign_item_status.title, GROUP_CONCAT(title) title
FROM cms_campaign_item_status
GROUP BY cms_campaign_item_status.campaign_itemNum
Jeff