Moodle v3.7
I am trying to write utility which needs to know which resources (URLs, files, etc.) and activities are in each section of a course.
I see the mdl_resource table, which has an id and a course. So, finding out the course that a resource is connected to seems easy enough. However, I do not see what connects a resource with the section where it resides. It must be in there (as Moodle knows it).
Can anyone tell me where I can find this information?
The link between an activity and the course section is maintained via the course module record (see https://docs.moodle.org/dev/Course_module for more details).
In summary, you need to look in mdl_modules to find the 'id' the 'resource' activity type (the moduleid).
Then you need to look in mdl_course_modules to find the record with the field 'module' matching the moduleid AND the 'instance' field matching the id from mdl_resource.
In there, you will find the 'section' field (I don't have a live example in front of me now, off the top of my head that is the 'id' of the mdl_course_sections record that represents the section, rather than the 'section number' for the section).
mdl_course_sections also has a 'sequence' field, which is an ordered list of the mdl_course_modules records that appear in that section.
In almost all situations, what you actually want to use is get_fast_modinfo(), that returns a mod_info object, in which you can find various functions for looping through the course and getting the list of activities in each section.