PHP list plugin, show results of Query in status message

[SteveRL]

So I'm trying to use a PHP list plugin to run a select query which returns a single column (of email addresses). I want to somehow display the results of the select query to the user. I thought I would be able to put the results into the status message but I'm likely doing something wrong. Is this even possible? In a perfect world, it would display the results comma separated, ready for copy/paste into an email app. Thanks!

// This will find all the email addresses of the student in the selected course
$db = FabrikWorker::getDbo();

$query = $db->getQuery(true);

$schedule_id = $db->Quote('{sis_adm_course_schedule___schedule_id}');

$query = "
SELECT sis_adm_student.email FROM sis_adm_student
RIGHT JOIN sis_adm_enrollment ON sis_adm_enrollment.student_id = sis_adm_student.student_id
RIGHT JOIN sis_adm_course_schedule ON sis_adm_course_schedule.schedule_id = sis_adm_enrollment.schedule_id
WHERE sis_adm_course_schedule.schedule_id = $schedule_id
AND sis_adm_enrollment.status = 'Enrolled';
";


$db->setQuery($query);

// Run the query
$found = (int) $db->execute();

// Load a single row
$row = $db->LoadObject();

$statusMsg = $row;

 

[troester]

Here how to access the ids of the selected records and some sample code for selecting and post-processing
http://fabrikar.com/forums/index.php?wiki/php-list-plugin/
http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select

 

[SteveRL]

I was able to finally accomplish this after giving up several times. This is the working code I came up with:

// This will find all the email addresses of the students in the selected course
$db = FabrikWorker::getDbo();
$ids = JRequest::getVar( 'ids', array(), 'method', 'array' );
foreach ($ids AS $id) {
$row = $model->getRow($id);

$schedule_id = $row->sis_adm_course_schedule___schedule_id; 
$title = $row->sis_adm_course_schedule___title;
$start_date = $row->sis_adm_course_schedule___start_date;
}

// Create a new query object.
$query = $db->getQuery(true);

$query
    ->select(array('email', 'first_name', 'last_name'))
    ->from('sis_adm_student')
    ->join('RIGHT','sis_adm_enrollment ON sis_adm_enrollment.student_id = sis_adm_student.student_id')
    ->join('RIGHT','sis_adm_course_schedule ON sis_adm_course_schedule.schedule_id = sis_adm_enrollment.schedule_id')
    ->where('sis_adm_course_schedule.schedule_id = '. (int) $schedule_id)
    ->where('sis_adm_enrollment.status = ' . $db->quote('Enrolled'))
;


// Assign the query to the db
$db->setQuery($query);

// Load the results as an array of objects.
$rows = $db->loadObjectList();

$list = array();
foreach ($rows as $row)
{
      $list[] = $row->first_name .  " " . $row->last_name . " &lt;" . $row->email . "&gt;,</br>";
}

$emails = implode( "", $list );


$db->setQuery($query);

// Generate the status message with the list of emails
$statusMsg = "Copy and paste the below email list into an email editor to contact students enrolled in the following course:<b>$title</b> with a start date of <b>$start_date</b><br>Be advised that only student with an enrollment status of <b>'Enrolled'</b> are returned. <br><br>$emails";