3 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: September 27   (RSS)

Hey Guys.

I'm using Report Builder to display the activity of agents, and the part of the SQL below displays the agents.  However this displays the "num" and I want to display the "label".  In php I would saug $record['agentsNum:label'].  How do I do that here?

SELECT
agentsNum as 'Agent',
nlb_subscribersNum as 'Contact',
DATE_FORMAT(<?php echo $TABLE_PREFIX ?>notes.updatedDate, '%M %Y') as 'Date'
FROM <?php echo $TABLE_PREFIX ?>notes
GROUP BY Agent
ORDER BY Contact

Cheers,



Tim Forrest

Toledoh Enterprises

www.toledoh.com.au

Hi Tim,

Those ":label" fields are specially generated by the getRecords() function, so it's not quite so simple to get it from a straight MySQL query. Since you're using a field called "agentsNum" I assume it's being populated from an "agents" table, which is good as it means that we can use a JOIN to get the label. That would look something like this:

SELECT
<?php echo $TABLE_PREFIX ?>agents.label as 'Agent',
nlb_subscribersNum as 'Contact',
DATE_FORMAT(<?php echo $TABLE_PREFIX ?>notes.updatedDate, '%M %Y') as 'Date'
FROM <?php echo $TABLE_PREFIX ?>notes
LEFT JOIN <?php echo $TABLE_PREFIX ?>agents ON <?php echo $TABLE_PREFIX ?>notes.agentsNum = <?php echo $TABLE_PREFIX ?>agents.num
GROUP BY Agent
ORDER BY Contact

"agents" will need to match your actual Agents table name, and ".label" and ".num" will need to match their respective fields in the Agents table. You can read more about JOIN statements here: https://www.guru99.com/joins.html

Let me know if that does the trick, or if you have any other questions.

Thanks!

Daniel
PHP Programmer
interactivetools.com