Search the Community

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

Hi Folks There is a bit of code I am struggling to grasp. It is used to print a range of data based on a custom date range. function print_log_selector_form_range($selectedfromdate='today', $selectedtodate='today', $logformat='showashtml') { global $USER, $CFG; $strftimedate = get_string("strftimedate"); $strftimedaydate = get_string("strftimedaydate"); // Prepare the list of action options. $actions = array( 'view' => get_string('view'), 'add' => get_string('add'), 'update' => get_string('update'), 'delete' => get_string('delete'), '-view' => get_string('allchanges') ); // Get all the possible dates // Note that we are keeping track of real (GMT) time and user time // User time is only used in displays - all calcs and passing is GMT $timenow = time(); // GMT //echo "<br />"; // What day is it now for the user, and when is midnight that day (in GMT). $timemidnight = $today = usergetmidnight($timenow); //echo "<br />"; // Put today up the top of the list $dates = array("$timemidnight" => get_string("today").", ".userdate($timenow, $strftimedate) ); $numdates = 1; while ($timemidnight > $COURSE->startdate and $numdates < 365) { $timemidnight = $timemidnight - 86400; $timenow = $timenow - 86400; $dates["$timemidnight"] = userdate($timenow, $strftimedaydate); $numdates++; } if ($selecteddate == "today") { $selecteddate = $today; } echo "<form class=\"logselectform\" action=\"$CFG->wwwroot/admin/report/mentorprogress/index.php\" method=\"get\">\n"; echo "<div>\n"; echo "<input type=\"hidden\" name=\"chooselog\" value=\"1\" />\n"; // echo html_writer::select($options, $name, $selected, array($nothingvalue=>$nothing), $attributes); //choose_from_menu ($dates, "fromdate", $selectedfromdate, get_string("alldays")); //~ print "<pre>"; //~ print_r($dates); //~ print "</pre>"; echo $selectedfromdate; echo "<br />"; echo $selectedtodate; echo "<br />"; echo html_writer::select($dates, "fromdate",$selectedfromdate, get_string("alldays")); // choose_from_menu ($dates, "todate", $selectedtodate, get_string("alldays")); echo html_writer::select($dates,"todate",$selectedtodate, get_string("alldays")); $logformats = array('showashtml' => get_string('displayonpage'), 'downloadasexcel' => get_string('downloadexcel')); //choose_from_menu ($logformats, 'logformat', $logformat, false); echo html_writer::select($logformats, 'logformat', $logformat, false); echo '<input type="submit" value="'.get_string('gettheselogs').'" />'; echo '</div>'; echo '</form>'; } This function above is in lib.php, and is called from index.php via this function: //echo $fromdate; //echo $todate; print_log_selector_form_range($fromdate, $todate, $logformat); //echo $fromdate; //echo $todate; if($logformat == 'showashtml'){ print_overview_table($users, $fromdate, $todate); } I check the dates before and after this function is called and the dates remain at zero, even after selecting valid date ranges. By printing the array to screen I can see that the $dates array has valid entries. The function "print_overview_table" below "print_log_selector_form_range" is supposed to print the valid range to the screen. function print_overview_table($users, $fromdate, $todate){ //check data if(!$users){ die('No Records To Display.'); } //$users = get_mentor_users($DB, $fromdate, $todate, $sumpcat); //~ print "<pre>"; //~ print_r($users); //~ print "</pre>"; //start table echo '<br><br><table class="logtable generalbox boxaligncenter" width="95%" border="1" cellspacing="0" cellpadding="5" style="padding:2px;">'; //add report name echo '<tr><td colspan="5">Admin Report - Mentor Marking Progress</td></tr>'; //add report headings echo '<tr> <th>Mentor</th> <th>Total Summative marked</th> <th>Competent</th> <th>Not Yet Competent</th> </tr>'; //populate //foreach($users as $user){ foreach($users as $key => $user) { /*[id] => 2 [totalmarked] => 1 [totalcommarked] => 1 [totalsummarked] => 0 [markedcompetent] => 1 [markedincompetent] => 0 * */ echo '<tr>'; //echo "<td><a href=\"viewdash.php?mentor=$user->id&todate=$todate&fromdate=$fromdate\">", get_mentor_name($DB, $users[$key]->id),"</a></td>"; echo "<td><a href=\"viewdash.php?mentor=$user->id&todate=$todate&fromdate=$fromdate\">", $users[$key]->firstname ,' ',$users[$key]->lastname,"</a></td>"; echo "<td>",$users[$key]->totalsummarked,"</td>"; echo "<td>",$users[$key]->markedcompetent,"</td>"; echo "<td>",$users[$key]->markedincompetent,"</td>"; echo '</tr>'; } //end table echo '</table><br><br>'; } When a user submits the date ranges the page goes blank. Can anyone give me some pointers to fix this.

Hi Folks I have been trying to get a sql query to pass between pages in moodle 2.x. Moodle uses object orientation. The problem I have is the processed query return $DB->get_records($sql) will not pass back to index.php without a horrible hack. First: There are two files, index.php and lib.php (has functions in it) index.php calls lib.php with this line: $users = get_mentor_users($fromdate, $todate); lib.php executes this function: function get_mentor_users($fromdate = 0, $todate = 0){ $sql = "SELECT DISTINCT u.id , u.firstname, u.lastname FROM mdl_role_assignments ra, mdl_user u, mdl_course c, mdl_context cxt WHERE ra.userid = u.id AND ra.contextid = cxt.id AND cxt.contextlevel =50 AND ( roleid =3 ) AND u.firstname LIKE '%mentor%'"; if($datesql = generate_sql_range($fromdate, $todate, ', mdl_grade_grades.timemodified')) { $sql .= " AND $datesql"; } return $DB->get_records_sql($sql); } Now for the perplexing part. index.php throws out a blank screen. I rewrote this function to pass the sql string from lib.php to index.php then for index.php to do the sql query locally and then it works fine. Like so: index.php $sql2 = get_mentor_users($fromdate, $todate); $users = array(); $users = $DB->get_records_sql($sql2); But then accessing the array gets kinda ugly. Then in lib.php only the unprocessed sql string is returned back to index.php. function get_mentor_users($fromdate = 0, $todate = 0){ $sql = "SELECT DISTINCT u.id , u.firstname, u.lastname FROM mdl_role_assignments ra, mdl_user u, mdl_course c, mdl_context cxt WHERE ra.userid = u.id AND ra.contextid = cxt.id AND cxt.contextlevel =50 AND ( roleid =3 ) AND u.firstname LIKE '%mentor%'"; if($datesql = generate_sql_range($fromdate, $todate, ', mdl_grade_grades.timemodified')) { $sql .= " AND $datesql"; } return $sql; } The output of the array below: Array ( [12] => stdClass Object ( [id] => 12 [firstname] => Mentor Peter [lastname] => Swananipoel [totalmarked] => [totalcommarked] => 0 [totalsummarked] => 0 [markedcompetent] => 0 [markedincompetent] => 0 ) [14] => stdClass Object ( [id] => 14 [firstname] => Mentor Garfield [lastname] => Robertson [totalmarked] => [totalcommarked] => 0 [totalsummarked] => 0 [markedcompetent] => 0 [markedincompetent] => 0 ) [15] => stdClass Object ( [id] => 15 [firstname] => Mentor Bert [lastname] => Cat [totalmarked] => [totalcommarked] => 0 [totalsummarked] => 0 [markedcompetent] => 0 [markedincompetent] => 0 ) ) How can I pass this array between pages without the shenanigans? Thanks