mysql - How to get the name of a course's teacher

Question

Could you help me create a query that shows the teacher(s) of a course

Example:

Title of course: Course 1 Teacher: James Anderson

I have this query:

SELECT DISTINCT ldm_user.id,
ldm_user.firstname, 
ldm_user.lastname,
ldm_course.shortname,
ldm_course.fullname,
ldm_role.id as role_id, 
ldm_role_assignments.id

FROM ldm_course,ldm_user,ldm_role,ldm_context,ldm_role_assignments

WHERE ldm_course.fullname = "i-ONS001 Taller de Lectura y Redacción IV" and ldm_role_assignments.id = 4

But this is not returning the name of the teacher as expected.

Answer 1

Your question is incomplete, because the exact query will vary depending on your version of Moodle, whether you're using SQL or MySQL, and also we can't give you a comprehensive answer without knowing your table structures.

However, in Moodle 2.x you can use an API query (PHP) which looks something like this:

$role = $DB->get_record('role', array('shortname' => 'editingteacher'));
$context = get_context_instance(CONTEXT_COURSE, $courseid);
$teachers = get_role_users($role->id, $context);

and then doing a return $teachers; or echo $teachers; to output said results.

Like I said, without knowing your exact system details I can't give you an accurate response and a functioning query so take that with a pinch of salt; you might need to play around with it to get it to work.

Ref: https://moodle.org/mod/forum/discuss.php?d=115636