Database & APIs

Abstraction: Moodle supports multiple databases (MySQL, PostgreSQL, MSSQL, Oracle). The $DB global (DML API) abstracts the specific SQL syntax, ensuring your code works across all supported databases.

Security: It automatically handles parameter binding, preventing SQL injection.

Extend the moodleform class and define the definition() method.

Use $mform->addElement() to add fields and $mform->addRule() for validation.

Instantiate the form class in your script and call $form->get_data() to retrieve submitted values.

$DB->get_record() returns a single object (stdClass) representing one row. It throws an error if more than one row is found. $DB->get_records() returns an array of objects, indexed by the first field (usually 'id').

Use $transaction = $DB->start_delegated_transaction(); at the start. Perform your DB operations. If everything is successful, call $transaction->allow_commit();. If an exception occurs, the transaction is rolled back automatically.

Every table in Moodle must have an id field as the primary key. It is an auto-incrementing integer. Moodle's DML API relies on this convention for updates and deletes.

Create a standard object (stdClass) with properties matching the table column names. Then call $id = $DB->insert_record('table_name', $record_object);. It returns the new ID.

Create an object that includes the id of the record to update and the fields to change. Call $DB->update_record('table_name', $record_object);.

It allows you to fetch a single record using a custom SQL query. It is useful for complex joins that standard methods can't handle. Always use placeholders (? or :name) for parameters.

Use $DB->get_recordset() instead of get_records(). It returns an iterator (RecordSet) that fetches rows one by one, preventing memory exhaustion when processing thousands of records.

lib/datalib.php contains the core database functions and the DML API implementation. It defines the moodle_database abstract class and its drivers.

Use the database manager: $dbman = $DB->get_manager(); then $dbman->table_exists('table_name');. This is part of the DDL API.

Params are values passed to SQL queries to replace placeholders. They are passed as an array. Moodle sanitizes these values to prevent SQL injection. Example: $DB->get_record('user', ['username' => 'admin']);.

It is a helper function that constructs a SQL IN (...) clause safely. It returns an array containing the SQL fragment and the parameters. Example: list($insql, $inparams) = $DB->get_in_or_equal([1, 2, 3]);.

Different databases handle text comparison differently (e.g., case sensitivity). Use $DB->sql_compare_text('fieldname', 'searchterm') or $DB->sql_like() to generate the correct SQL for the active database driver.

$DB->execute($sql, $params) is used for DDL statements (like CREATE, DROP) or complex updates/deletes that don't fit the standard update_record/delete_records methods. It returns true on success or throws an exception.

Use $DB->delete_records('table_name', ['field' => 'value']);. To delete a specific record by ID, use $DB->delete_records('table_name', ['id' => $id]);.

It returns the number of records matching specific criteria. $DB->count_records('table_name', ['field' => 'value']);. It is more efficient than fetching all records and counting the array.

Use $DB->execute($sql, $params);. This is typically used for UPDATE or DELETE statements with complex logic, but standard DML methods are preferred when possible.

A built-in tool (Site Admin > Development > XMLDB editor) that allows developers to visually create and edit database tables. It generates the install.xml file and the PHP code needed for upgrades.

In install.xml, define a KEY or INDEX. For upgrades, use $dbman->add_key() or $dbman->add_index() within upgrade.php.

A cross-database helper function used when comparing text fields (CLOBs) in SQL conditions, as different databases handle text comparison differently (e.g., Oracle vs MySQL).

A helper function to concatenate strings in SQL in a cross-database compatible way. $DB->sql_concat('firstname', "' '", 'lastname').

Use $DB->get_in_or_equal($array_of_values). It returns an array containing the SQL fragment (e.g., IN (?,?,?)) and the parameters to pass to the query.

It returns an array of values from a single field (column) for records matching a selection. Useful for getting a list of IDs: $ids = $DB->get_fieldset_select('user', 'id', 'deleted = 0');.

Enable 'Debug messages' to DEVELOPER level. Moodle will print SQL errors. You can also use $DB->set_debug(true); temporarily in code to output all executed queries.

A boolean check to see if a record exists. if ($DB->record_exists('user', ['username' => 'guest'])) { ... }. Efficient as it limits the query to 1 row.

Use $DB->sql_like('fieldname', ':param'). It handles case-sensitivity differences and escape characters across databases.

A function that rebuilds the course sortorder. It is sometimes needed after manually manipulating course records in the database (which you should avoid doing).

From the global $USER object: $USER->id. Never query the database to find the current user if the global is available.

A helper function to retrieve a course object by ID. It is a wrapper around $DB->get_record('course', ...) but may include caching or additional checks.

A shortcut to update a single field of a record. $DB->set_field('user', 'confirmed', 1, ['id' => $userid]);.

Returns an associative array where the first field is the key and the second is the value. Useful for populating dropdown menus (select boxes) in forms.

It contains <TABLES>, which contain <TABLE> elements. Tables have <FIELDS> and <KEYS> (Primary/Foreign) and <INDEXES>. It defines the schema in a DB-agnostic XML format.