SQL result in html block / search courses filtered by teacher

SQL result in html block / search courses filtered by teacher

by Richárd Major -
Number of replies: 3

Hey,

So the goal i have is to make my own html block. Its only purpose would be to list all the courses full name where the searched user is a teacher. This would help my fellow editors, who wants to filter by teacher so they can find the problem more faster and easily.

There would be an input search bar where you can type a name, and after a submit pressed, it would list out the courses' full name.

I'm really a newbie when it comes to moodle development, so its a bit rough for me, started my own, and successfully printed out an sql select but its a bit more complicated.

Example of the end product:

- Search for a teachers name 

- Get a list for the courses he/she has, and where he/she has teacher role.

- Able to click on the listed course name to navigate to the searched course.

Environment: 3.9. Moodle

Theme : Moove

Picture of the basic concept is attached. I hope its understandable.

SQL what i used and worked with, only its a bit too much information (easily fixed), i just need to make it work search by the input.

/////////////////////////////////////////////////////////////////////
SELECT distinct c.id, c.fullname, u.username, u.firstname, u.lastname
FROM mdl_course as c, mdl_role_assignments AS ra, mdl_user AS u, mdl_context AS ct
WHERE c.id = ct.instanceid AND ra.roleid =3 AND ra.userid = u.id AND ct.id = ra.contextid;
//////////////////////////////////////////////////////////////////////////////////////////

Any help is very well appreciated,

Sincerely, 

Richard Major

Attachment basicconcept.png
Average of ratings: -
In reply to Richárd Major

Re: SQL result in html block / search courses filtered by teacher

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

what you looking for is probably there :
https://docs.moodle.org/dev/Data_manipulation_API#get_recordset_select

for example with exact firsname and lastname :
$sql = "
SELECT distinct c.id, c.fullname, u.username, u.firstname, u.lastname
FROM mdl_course as c, mdl_role_assignments AS ra, mdl_user AS u, mdl_context AS ct
WHERE c.id = ct.instanceid AND ra.roleid =3 AND ra.userid = u.id AND ct.id = ra.contextid
AND u.firstname = :param1 AND u.lastname = :param2
";

$params = array('param1' => $firstname, 'param2' => $lastname);
$result = $DB->get_records_sql($sql, $params);
Average of ratings: Useful (1)
In reply to Dominique Palumbo

Tárgy: Re: SQL result in html block / search courses filtered by teacher

by Richárd Major -
Hey,

Thank you very much for the help. I've searched through the API for some answers, finally managed to get the list that i wanted, only it seems it gets the first record only, and it stops :/

My code:

/////////////////////////////////////////////////////////////////////////////////
$coursestring = '';
$sql ="SELECT c.fullname, c.id FROM {course} as c, {role_assignments} as ra, {user} as u, {context} as ct WHERE c.id=ct.instanceid AND ra.roleid=3 AND ra.userid=u.id AND ct.id=ra.contextid AND u.lastname='$this->teacher_lastname' AND u.firstname='$this->teacher_firstname'";

$courses = $DB->get_record_sql($sql);

if (!empty($_POST['last_name'] && !empty($_POST['first_name']))) {
$this->content->text .= "

" . $_POST['last_name'] . " " . $_POST['first_name'] . " kurzusa(i):

";
foreach ($courses as $course) {
$coursestring .= " " . $course . "";
}

/////////////////////////////////////////////////////////////////////////////////

I've switch over using 2 input field for first name and last name seperately, until i find a solution for the records problem (only showing the first record).
Average of ratings: -
In reply to Richárd Major

Re: Tárgy: Re: SQL result in html block / search courses filtered by teacher

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi, I'm sorry, I missed that they've a question !!!

get_records_sql

I hope you already fixed it. you missed the s !


Average of ratings: -