Get list of courses for given student - moodle 2.4

[huck]

Hi All

 

I am having problems running a query to get the courses a student is enrolled for.

 

The format I need is somthing like this:

firstname    - lastname   - course
-------------------------------------------
Joe           Bloke       US232456
Joe           Bloke       US554665
Joe           Bloke       US332098

or like this

 
firstname    - lastname          - course
-------------------------------------------------
Joe              Bloke           US232456
                                 US554665
                                 US332098
 
 

The query I am running is

SELECT usr.firstname, usr.lastname, c.shortname

FROM mdl_course c

INNER JOIN mdl_context cx ON c.id = cx.instanceid

AND cx.contextlevel = '50'

INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid

INNER JOIN mdl_role r ON ra.roleid = r.id

INNER JOIN mdl_user usr ON ra.userid = usr.id

WHERE r.name = 'Learner'

AND usr.firstname = 'G01 Moloko'

ORDER BY usr.firstname, c.shortname

The problem is that I know this student is enrolled for 3 courses, but it only returns 1 course.

 

Any help is welcomed.

 

huck

[maxxd]

There could be any number of reasons this isn't returning what you expect - a little more detail would help. If you run the query in phpMyAdmin or MySQLWorkbench, is it returning 1 row or 3? Is there some php display code - obviously, if there is display code we'd need to see that as well.

[huck]

Querying this with phpmyadmin I get

 

 

	
		
			
				firstname 
			
				lastname 
			
				shortname 
			
				 
		
	
	
		
			
				G01 Moloko
			
				Samuel Madibana
			
				CO119472
		
	

 

 

The complete function called is as follows; but when used in my lib.php file it fails.

 

Look for the line "EVERYTHING BELOW DOES NOT WORK" within the function function print_overview_table2. Otherwise everything above it is fine.

 

NOTE: I originally tried the query in a for loop which I know is bad so just bear with the code, I commented out the foreach to test it and it pushes out an error;

 

 

function print_overview_table2($COURSE, $users, $choosegroup, $fromdate, $todate)
{
    global $DB, $USER;
    //check data
    if(!$choosegroup){
        die('No Records To Display.');
    }
    
     
    //start table
    echo '<br><br><table class="logtable generalbox boxaligncenter" width="95%" border="1" cellspacing="0" cellpadding="5" style="padding:2px;">';
    
    //add report name
    
    $numarray=array();
    array_unshift($numarray, "");
    unset($numarray[0]);
    foreach (range(1,99) as $number)
    {
        $num_padded = sprintf("%02s", $number);
        $alphanum="G" . $num_padded;
        $numarray[]=$alphanum;
    }
    
    echo '<tr><td colspan="6">Admin Report - Group ' . $numarray[$choosegroup] . ' - Learner Progress</td></tr>';
    
    //add report headings
    echo '<tr>
    <th>Learner</th>
    <th>Unit Standard</th>
    <th>F2</th>
    <th>Summ</th>    
    <th>Competent</th>
    <th>Date</th>
    
      </tr>';
    
    
    
    //populate
    
    //foreach($users as $user){
    foreach($users as $key => $user)
    {
        
      
        echo '<tr>';
        
        echo "<td><a href=\"viewdash.php?learner=$user->id&todate=$todate&fromdate=$fromdate\">", $users[$key]->firstname ,' ',$users[$key]->lastname,"      </a></td>";
        
        echo "<td>US</td>";
        echo "<td>Formative2</td>";
        echo "<td>Summative</td>";
        echo "<td>C / NYC</td>";
        echo "<td>completed</td>";
        echo '</tr>';
       
    }
    
    echo '</table><br><br>';
  
    // EVERYTHING BELOW DOES NOT WORK
   
    //$sql = '';
    
    //~ foreach($users as $key => $user)
    //~ {
        
        $sql =
        "SELECT c.shortname, usr.firstname, usr.lastname
        FROM mdl_course c
        INNER JOIN mdl_context cx ON c.id = cx.instanceid
        AND cx.contextlevel = '50'
        INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
        INNER JOIN mdl_role r ON ra.roleid = r.id
        INNER JOIN mdl_user usr ON ra.userid = usr.id
        WHERE r.name = 'Learner'
        AND usr.firstname = '$users[$key]->firstname'
        AND usr.lastname = '$users[$key]->lastname'
        ORDER BY usr.firstname, usr.lastname, c.shortname";
        
        //$result = $DB->get_records_sql($sql);
        
        print "<pre>";
        print_r($sql);
        print "</pre>";
        
        echo $sql;
        //return key($result);
        
        
    //~ }
       //end of foreach
   
}

 

I tried to first check that the $sql string was valid by echoing it to the screen, but this fails as well. So I have not been able to execute the sql query yet.

 

The intent was to iterate through each user and build a list of courses that each user is enrolled for.

[maxxd]

If running the query in phpMyAdmin only returns 1 row when you're expecting 3, you need to re-evaluate your query. Only 1 row is matching the criteria, so something is amiss. Work on the query in phpMyAdmin until you're actually getting back all the results you know you should have, then worry about getting those results from and into php.