Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
/lib/ -> datalib.php (source)

Differences Between: [Versions 39 and 310] [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

Library of functions for database manipulation. Other main libraries: - weblib.php - functions that produce web output - moodlelib.php - general-purpose Moodle functions

Copyright: 1999 onwards Martin Dougiamas {@link http://moodle.com}
License: http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
File Size: 1919 lines (72 kb)
Included or required:0 times
Referenced: 3 times
Includes or requires: 0 files

Defines 35 functions


Functions that are not part of a class:

get_admin()   X-Ref
Returns $user object of the main admin user

return: stdClass {@link $USER} record from DB, false if not found

get_admins()   X-Ref
Returns list of all admins, using 1 DB query

return: array

search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null)   X-Ref
Search through course users

If $coursid specifies the site course then this function searches
through all undeleted and confirmed users

param: int $courseid The course in question.
param: int $groupid The group in question.
param: string $searchtext The string to search for
param: string $sort A field to sort by
param: array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
return: array

users_search_sql($search, $u = 'u', $searchanywhere = true, array $extrafields = array()   X-Ref
Returns SQL used to search through user table to find users (in a query
which may also join and apply other conditions).

You can combine this SQL with an existing query by adding 'AND $sql' to the
WHERE clause of your query (where $sql is the first element in the array
returned by this function), and merging in the $params array to the parameters
of your query (where $params is the second element). Your query should use
named parameters such as :param, rather than the question mark style.

There are examples of basic usage in the unit test for this function.

param: string $search the text to search for (empty string = find all)
param: string $u the table alias for the user table in the query being
param: bool $searchanywhere If true (default), searches in the middle of
param: array $extrafields Array of extra user fields to include in search
param: array $exclude Array of user ids to exclude (empty = don't exclude)
param: array $includeonly If specified, only returns users that have ids
return: array an array with two elements, a fragment of SQL to go in the

users_order_by_sql($usertablealias = '', $search = null, context $context = null)   X-Ref
This function generates the standard ORDER BY clause for use when generating
lists of users. If you don't have a reason to use a different order, then
you should use this method to generate the order when displaying lists of users.

If the optional $search parameter is passed, then exact matches to the search
will be sorted first. For example, suppose you have two users 'Al Zebra' and
'Alan Aardvark'. The default sort is Alan, then Al. If, however, you search for
'Al', then Al will be listed first. (With two users, this is not a big deal,
but with thousands of users, it is essential.)

The list of fields scanned for exact matches are:
- firstname
- lastname
- $DB->sql_fullname
- those returned by get_extra_user_fields

If named parameters are used (which is the default, and highly recommended),
then the parameter names are like :usersortexactN, where N is an int.

The simplest possible example use is:
list($sort, $params) = users_order_by_sql();
$sql = 'SELECT * FROM {users} ORDER BY ' . $sort;

A more complex example, showing that this sort can be combined with other sorts:
list($sort, $sortparams) = users_order_by_sql('u');
$sql = "SELECT g.id AS groupid, gg.groupingid, u.id AS userid, u.firstname, u.lastname, u.idnumber, u.username
FROM {groups} g
LEFT JOIN {groupings_groups} gg ON g.id = gg.groupid
LEFT JOIN {groups_members} gm ON g.id = gm.groupid
LEFT JOIN {user} u ON gm.userid = u.id
WHERE g.courseid = :courseid $groupwhere $groupingwhere
ORDER BY g.name, $sort";
$params += $sortparams;

An example showing the use of $search:
list($sort, $sortparams) = users_order_by_sql('u', $search, $this->get_context());
$order = ' ORDER BY ' . $sort;
$params += $sortparams;
$availableusers = $DB->get_records_sql($fields . $sql . $order, $params, $page*$perpage, $perpage);

param: string $usertablealias (optional) any table prefix for the {users} table. E.g. 'u'.
param: string $search (optional) a current search string. If given,
param: context $context the context we are in. Use by get_extra_user_fields.
return: array with two elements:

get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',$firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null)   X-Ref
Returns a subset of users

param: bool $get If false then only a count of the records is returned
param: string $search A simple string to search for
param: bool $confirmed A switch to allow/disallow unconfirmed users
param: array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
param: string $sort A SQL snippet for the sorting criteria to use
param: string $firstinitial Users whose first name starts with $firstinitial
param: string $lastinitial Users whose last name starts with $lastinitial
param: string $page The page or records to return
param: string $recordsperpage The number of records to return per page
param: string $fields A comma separated list of fields to be returned from the chosen table.
return: array|int|bool  {@link $USER} records unless get is false in which case the integer count of the records found is returned.

get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,$search='', $firstinitial='', $lastinitial='', $extraselect='',array $extraparams=null, $extracontext = null)   X-Ref
Return filtered (if provided) list of users in site, except guest and deleted users.

param: string $sort An SQL field to sort by
param: string $dir The sort direction ASC|DESC
param: int $page The page or records to return
param: int $recordsperpage The number of records to return per page
param: string $search A simple string to search for
param: string $firstinitial Users whose first name starts with $firstinitial
param: string $lastinitial Users whose last name starts with $lastinitial
param: string $extraselect An additional SQL select statement to append to the query
param: array $extraparams Additional parameters to use for the above $extraselect
param: stdClass $extracontext If specified, will include user 'extra fields'
return: array Array of {@link $USER} records

get_users_confirmed()   X-Ref
Full list of users that have confirmed their accounts.

return: array of unconfirmed users

get_site()   X-Ref
Returns $course object of the top-level site.

return: object A {@link $COURSE} object for the site, exception if not found

get_course($courseid, $clone = true)   X-Ref
Gets a course object from database. If the course id corresponds to an
already-loaded $COURSE or $SITE object, then the loaded object will be used,
saving a database query.

If it reuses an existing object, by default the object will be cloned. This
means you can modify the object safely without affecting other code.

param: int $courseid Course id
param: bool $clone If true (default), makes a clone of the record
return: stdClass A course object

get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*")   X-Ref
Returns list of courses, for whole site, or category

Returns list of courses, for whole site, or category
Important: Using c.* for fields is extremely expensive because
we are using distinct. You almost _NEVER_ need all the fields
in such a large SELECT

Consider using core_course_category::get_courses()
or core_course_category::search_courses() instead since they use caching.

param: string|int $categoryid Either a category id or 'all' for everything
param: string $sort A field and direction to sort by
param: string $fields The additional fields to return (note that "id, category, visible" are always present)
return: array Array of courses

get_courses_search($searchterms, $sort, $page, $recordsperpage, &$totalcount,$requiredcapabilities = array()   X-Ref
A list of courses that match a search

param: array $searchterms An array of search criteria
param: string $sort A field and direction to sort by
param: int $page The page number to get
param: int $recordsperpage The number of records per page
param: int $totalcount Passed in by reference.
param: array $requiredcapabilities Extra list of capabilities used to filter courses
param: array $searchcond additional search conditions, for example ['c.enablecompletion = :p1']
param: array $params named parameters for additional search conditions, for example ['p1' => 1]
return: stdClass[] {@link $COURSE} records

fix_course_sortorder()   X-Ref
Fixes course category and course sortorder, also verifies category and course parents and paths.
(circular references are not fixed)

return: void

_fix_course_cats($children, &$sortorder, $parent, $depth, $path, &$fixcontexts)   X-Ref
Internal recursive category verification function, do not use directly!

param: array $children
param: int $sortorder
param: string $parent
param: int $depth
param: string $path
param: array $fixcontexts
return: bool if changes were made

get_my_remotecourses($userid=0)   X-Ref
List of remote courses that a user has access to via MNET.
Works only on the IDP

param: int @userid The user id to get remote courses for
return: array Array of {@link $COURSE} of course objects

get_my_remotehosts()   X-Ref
List of remote hosts that a user has access to via MNET.
Works on the SP

return: array|bool Array of host objects or false

get_scales_menu($courseid=0)   X-Ref
Returns a menu of all available scales from the site as well as the given course

param: int $courseid The id of the course as found in the 'course' table.
return: array

increment_revision_number($table, $field, $select, array $params = null)   X-Ref
Increment standard revision field.

The revision are based on current time and are incrementing.
There is a protection for runaway revisions, it may not go further than
one hour into future.

The field has to be XMLDB_TYPE_INTEGER with size 10.

param: string $table
param: string $field name of the field containing revision
param: string $select use empty string when updating all records
param: array $params optional select parameters

get_course_mods($courseid)   X-Ref
Just gets a raw list of all modules in a course

param: int $courseid The id of the course as found in the 'course' table.
return: array

get_coursemodule_from_id($modulename, $cmid, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING)   X-Ref
Given an id of a course module, finds the coursemodule description

Please note that this function performs 1-2 DB queries. When possible use cached
course modinfo. For example get_fast_modinfo($courseorid)->get_cm($cmid)
See also {@link cm_info::get_course_module_record()}

param: string $modulename name of module type, eg. resource, assignment,... (optional, slower and less safe if not specified)
param: int $cmid course module id (id in course_modules table)
param: int $courseid optional course id for extra validation
param: bool $sectionnum include relative section number (0,1,2 ...)
param: int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
return: stdClass

get_coursemodule_from_instance($modulename, $instance, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING)   X-Ref
Given an instance number of a module, finds the coursemodule description

Please note that this function performs DB query. When possible use cached course
modinfo. For example get_fast_modinfo($courseorid)->instances[$modulename][$instance]
See also {@link cm_info::get_course_module_record()}

param: string $modulename name of module type, eg. resource, assignment,...
param: int $instance module instance number (id in resource, assignment etc. table)
param: int $courseid optional course id for extra validation
param: bool $sectionnum include relative section number (0,1,2 ...)
param: int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
return: stdClass

get_coursemodules_in_course($modulename, $courseid, $extrafields='')   X-Ref
Returns all course modules of given activity in course

param: string $modulename The module name (forum, quiz, etc.)
param: int $courseid The course id to get modules for
param: string $extrafields extra fields starting with m.
return: array Array of results

get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false)   X-Ref
Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined

Returns an array of all the active instances of a particular
module in given courses, sorted in the order they are defined
in the course. Returns an empty array on any errors.

The returned objects includle the columns cw.section, cm.visible,
cm.groupmode, and cm.groupingid, and are indexed by cm.id.

param: string $modulename The name of the module to get instances for
param: array $courses an array of course objects.
param: int $userid
param: int $includeinvisible
return: array of module instance objects, including some extra fields from the course_modules

get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false)   X-Ref
Returns an array of all the active instances of a particular module in a given course,
sorted in the order they are defined.

Returns an array of all the active instances of a particular
module in a given course, sorted in the order they are defined
in the course. Returns an empty array on any errors.

The returned objects includle the columns cw.section, cm.visible,
cm.groupmode, and cm.groupingid, and are indexed by cm.id.

Simply calls {@link all_instances_in_courses()} with a single provided course

param: string $modulename The name of the module to get instances for
param: object $course The course obect.
param: int $userid
param: int $includeinvisible
return: array of module instance objects, including some extra fields from the course_modules

instance_is_visible($moduletype, $module)   X-Ref
Determine whether a module instance is visible within a course

Given a valid module object with info about the id and course,
and the module's type (eg "forum") returns whether the object
is visible or not according to the 'eye' icon only.

NOTE: This does NOT take into account visibility to a particular user.
To get visibility access for a specific user, use get_fast_modinfo, get a
cm_info object from this, and check the ->uservisible property; or use
the \core_availability\info_module::is_user_visible() static function.

param: $moduletype Name of the module eg 'forum'
param: $module Object which is the instance of the module
return: bool Success

get_log_manager($forcereload = false)   X-Ref
Get instance of log manager.

param: bool $forcereload
return: \core\log\manager

add_to_config_log($name, $oldvalue, $value, $plugin)   X-Ref
Add an entry to the config log table.

These are "action" focussed rather than web server hits,
and provide a way to easily reconstruct changes to Moodle configuration.

param: string  $name     The name of the configuration change action
param: string  $oldvalue The config setting's previous value
param: string  $value    The config setting's new value
param: string  $plugin   Plugin name, for example a filter name when changing filter configuration
return: void

user_accesstime_log($courseid=0)   X-Ref
Store user last access times - called when use enters a course or site

param: int $courseid  empty courseid means site
return: void

print_object($object)   X-Ref
Dumps a given object's information for debugging purposes

When used in a CLI script, the object's information is written to the standard
error output stream. When used in a web script, the object is dumped to a
pre-formatted block with the "notifytiny" CSS class.

param: mixed $object The data to be printed
return: void output is echo'd

xmldb_debug($message, $object)   X-Ref
This function is the official hook inside XMLDB stuff to delegate its debug to one
external function.

Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)

param: string $message string contains the error message
param: object $object object XMLDB object that fired the debug

user_can_create_courses()   X-Ref

return: boolean Whether the user can create courses in any category in the system.

update_field_with_unique_index($table, $field, array $newvalues,array $otherconditions, $unusedvalue = -1)   X-Ref
This method can update the values in mulitple database rows for a colum with
a unique index, without violating that constraint.

Suppose we have a table with a unique index on (otherid, sortorder), and
for a particular value of otherid, we want to change all the sort orders.
You have to do this carefully or you will violate the unique index at some time.
This method takes care of the details for you.

Note that, it is the responsibility of the caller to make sure that the
requested rename is legal. For example, if you ask for [1 => 2, 2 => 2]
then you will get a unique key violation error from the database.

param: string $table The database table to modify.
param: string $field the field that contains the values we are going to change.
param: array $newvalues oldvalue => newvalue how to change the values.
param: array $otherconditions array fieldname => requestedvalue extra WHERE clause
param: int $unusedvalue (defaults to -1) a value that is never used in $ordercol.

decompose_update_into_safe_changes(array $newvalues, $unusedvalue)   X-Ref
Helper used by {@link update_field_with_unique_index()}. Given a desired
set of changes, break them down into single udpates that can be done one at
a time without breaking any unique index constraints.

Suppose the input is array(1 => 2, 2 => 1) and -1. Then the output will be
array (array(1, -1), array(2, 1), array(-1, 2)). This function solves this
problem in the general case, not just for simple swaps. The unit tests give
more examples.

Note that, it is the responsibility of the caller to make sure that the
requested rename is legal. For example, if you ask for something impossible
like array(1 => 2, 2 => 2) then the results are undefined. (You will probably
get a unique key violation error from the database later.)

param: array $newvalues The desired re-ordering.
param: int $unusedvalue A value that is not currently used.
return: array A safe way to perform the re-order. An array of two-element

get_safe_orderby(array $orderbymap, string $orderbykey, string $direction = '', bool $useprefix = true)   X-Ref
Prepare a safe ORDER BY statement from user interactable requests.

This allows safe user specified sorting (ORDER BY), by abstracting the SQL from the value being requested by the user.
A standard string (and optional direction) can be specified, which will be mapped to a predefined allow list of SQL ordering.
The mapping can optionally include a 'default', which will be used if the key provided is invalid.

Example usage:
-If $orderbymap = [
'courseid' => 'c.id',
'somecustomvalue'=> 'c.startdate, c.shortname',
'default' => 'c.fullname',
]
-A value from the map array's keys can be passed in by a user interaction (eg web service) along with an optional direction.
-get_safe_orderby($orderbymap, 'courseid', 'DESC') would return: ORDER BY c.id DESC
-get_safe_orderby($orderbymap, 'somecustomvalue') would return: ORDER BY c.startdate, c.shortname
-get_safe_orderby($orderbymap, 'invalidblah', 'DESC') would return: ORDER BY c.fullname DESC
-If no default key was specified in $orderbymap, the invalidblah example above would return empty string.

param: array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'.
param: string $orderbykey A string to be mapped to a key in $orderbymap.
param: string $direction Optional ORDER BY direction (ASC/DESC, case insensitive).
param: bool $useprefix Whether ORDER BY is prefixed to the output (true by default). This should not be modified in most cases.
return: string The ORDER BY statement, or empty string if $orderbykey is invalid and no default is mapped.

get_safe_orderby_multiple(array $orderbymap, array $orderbykeys, array $directions = [])   X-Ref
Prepare a safe ORDER BY statement from user interactable requests using multiple values.

This allows safe user specified sorting (ORDER BY) similar to get_safe_orderby(), but supports multiple keys and directions.
This is useful in cases where combinations of columns are needed and/or each item requires a specified direction (ASC/DESC).
The mapping can optionally include a 'default', which will be used if the key provided is invalid.

Example usage:
-If $orderbymap = [
'courseid' => 'c.id',
'fullname'=> 'c.fullname',
'default' => 'c.startdate',
]
-An array of values from the map's keys can be passed in by a user interaction (eg web service), with optional directions.
-get_safe_orderby($orderbymap, ['courseid', 'fullname'], ['DESC', 'ASC']) would return: ORDER BY c.id DESC, c.fullname ASC
-get_safe_orderby($orderbymap, ['courseid', 'invalidblah'], ['aaa', 'DESC']) would return: ORDER BY c.id, c.startdate DESC
-If no default key was specified in $orderbymap, the invalidblah example above would return: ORDER BY c.id

param: array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'.
param: array $orderbykeys An array of strings to be mapped to keys in $orderbymap.
param: array $directions Optional array of ORDER BY direction (ASC/DESC, case insensitive).
return: string The ORDER BY statement, or empty string if $orderbykeys contains no valid items and no default is mapped.