MySQL, PHP, and Moodle Schema

The plan for this class. Make sure you have everything you need to get started. Get excited! 

An introduction to the Moodle database schema. How to read a database diagram. Foreign key relationships between the user, role, and role assignments table.

Navigating tables, data, and field structure. Moodle table prefixes. How data is stored. The SQL tab.

Installing a custom plugin. Accessing the plugin within Moodle. Opening the plugin for editing.

Moodle's user list. Adding a user. Viewing the user data in the database. Primary keys. Explanation of datatypes.

Basic query syntax. Examples of several simple queries! SELECT, FROM and WHERE clauses. Using LIKE in the WHERE clause.

Decoding several lines of PHP code. Writing a get_records_sql() function call and displaying the results. Recreating queries from the previous video.

Reviewing the default roles. Making role assignments. Viewing role assignments in the database. The role_assignments table is a "Lookup table".

A systematic approach to finding relevant data. Using "IN" to match multiple values in the WHERE clause.

The global $DB object. Syntax and use of the get_record_select() function. Recreating the queries from the previous video.

Querying for multiple records with the get_records_select() function. Looping through multiple records using the foreach() function.

Viewing and editing capabilities in Moodle. The role_capability table, and its relationship with the role and context tables.

Querying for role capabilities. Basic syntax for a nested query. A nested query with four levels. Using FROM_UNIXTIME() to format a Unix timestamp.

Showing all capabilities for a specific role. Showing all capabilities for a specific user. Using the date() function to format a Unix timestamp.

Explaining role contexts. Finding out the contexts where a role may be assigned in Moodle. The role_context_levels table. The hierarchy of contexts in Moodle.

Querying to see the contexts where a role may be assigned. How to JOIN a table in the FROM clause. Querying for users and role assignments with a JOINed query.

Showing the context types where a role may be assigned. Showing user role assignments including the context level for the assignments. Returning a single field with the get_field_select() function. Checking to see that a function returned a result using  the if() and empty() functions.

Making role assignments in the front page context. Renaming a category and making a role assignment in the category context. Making a course and assigning a role in the course context. Viewing these role assignments in the role_assignments table.

The difference between LEFT and INNER JOINs. Querying for user, role, context and capability information all in one query.

Using the require_capability() function to exclude access to a Moodle page. Using the has_capability() function to exclude access to part of a Moodle page. Also, just a tiny bit about the $PAGE global: the $context variable.

Understanding what these four tabs are for, and correlating them with the role_allow_assign, role_allow_override, role_allow_switch and role_allow_view tables. Relationship between these four tables and the role table.

Querying for a user, their role, and all roles they can assign, override, switch to, and view.

Displaying all of the abilities given to a specific user through the Role Allow Assignment, Allow Override, Allow Switch and Allow View tabs. Querying for the number of records that match a given condition using the count_records_select() function.

Finding a role assignment record in Moodle's "Logs" report. Finding that same record in the logstore_standard_log table in phpMyAdmin. The relationships between the logstore_standard_log table and the user, role and context tables.

Querying for all role assignments, the user who made each role assignment, the user receiving the role assignment, and the time each assignment was made. Limiting your result set using LIMIT. Sorting your query results.

Showing all role assignments, who made the assignment, who they assigned the role to, and the date of the assignment. A better way to handle large results with the get_recordset_select() function.