Difference between revisions of "Data manipulation API"

Jump to: navigation, search
(SQL compatibility functions)
m (get_in_or_equal: Add missing API methods sql_regex_supported, sql_regex and sql_intersect)
 
(7 intermediate revisions by 6 users not shown)
Line 3: Line 3:
 
Where possible, tricks and examples will be documented here in order to make developers' lives a bit easier. Of course, feel free to clarify, complete and add more information to  this documentation. It will be welcome, absolutely!
 
Where possible, tricks and examples will be documented here in order to make developers' lives a bit easier. Of course, feel free to clarify, complete and add more information to  this documentation. It will be welcome, absolutely!
  
== Main info ==
+
== General concepts ==
  
'''Important note:''' All the functions shown on this page are for use in '''Moodle 2.0 upwards''', where we changed the [[DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[DML functions - pre 2.0|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|migration docs]].
+
=== DB object ===
  
* All the function calls on this page are public methods of the $DB global object, so you'll need to "import" it within your functions (not needed in global scripts) with one simple:
+
* The data manipulation API is exposed via public methods of the <tt>$DB</tt> object.
<code php>global $DB;</code>
+
* Moodle core takes care of setting up the connection to the database according to values specified in the main config.php file.
* The $DB global object is an instance of the moodle_database class, which is defined in [http://git.moodle.org/gw?p=moodle.git;a=blob;f=lib/dml/moodle_database.php;h=2a6676c84e7c77b0534f18a13ba584f58a8ed024;hb=refs/heads/master moodle_database.php]
+
* The $DB global object is an instance of the <tt>moodle_database</tt> class. It is instantiated automatically during the bootstrap setup, i.e. as a part of including the main config.php file.
* All the $table parameters in the functions are meant to be the table name ''without'' prefixes.
+
* The DB object is available in the global scope right after including the config.php file:
<code php>$user = $DB->get_record('user', array('id'=>'1'));</code>
 
* When using the xxx_sql() functions, table names must be enclosed between curly braces.
 
<code php>$user = $DB->get_record_sql('SELECT * FROM {user} WHERE id = ?', array(1));</code>
 
* All the $conditions parameters in the functions are arrays of fieldname=>fieldvalue elements.
 
<code php>$user = $DB->get_record('user', array('firstname'=>'Martin', 'lastname'=>'Dougiamas'));</code>
 
* All the $params parameters in the functions are arrays of values used to fill placeholders in SQL statements. Both the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
 
 
<code php>
 
<code php>
/// Question mark placeholders:
+
<?php
  $DB->get_record_sql('SELECT * FROM {user} WHERE firstname = ? AND lastname = ?',
+
 
                      array('Martin', 'Dougiamas'));
+
require(__DIR__.'/../../../config.php');
  
/// Named placeholders:
+
// You can access the database via the $DB method calls here.
  $DB->get_record_sql('SELECT * FROM {user} WHERE firstname = :firstname AND lastname = :lastname',
 
                      array('firstname'=>'Martin', 'lastname'=>'Dougiamas'));
 
 
</code>
 
</code>
 +
* To make the DB object available in your local scope, such as within a function:
 +
<code php>
 +
<?php
  
== The functions ==
+
defined('MOODLE_INTERNAL') || die();
  
===Getting a single record===
+
function my_function_making_use_of_database() {
 +
    global $DB;
  
<code php>
+
    // You can access the database via the $DB method calls here.
o $DB->get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING)
+
}
  /// Get a single database record as an object where all the given conditions met.
 
  /// @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
 
  ///                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
 
  ///                        MUST_EXIST means throw exception if no record or multiple records found
 
o $DB->get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING)
 
  /// Get a single database record as an object which match a particular WHERE clause.
 
o $DB->get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING)
 
  /// Get a single database record as an object using a SQL statement.
 
 
</code>
 
</code>
  
===Getting an hashed array of records===
+
=== Table prefix ===
Each of the following methods return an array of objects. The array is indexed by the first column of the fields returned by the query. Thus to assure consistent data, it appears to be best practice to ensure that your query include an "id column" as the first field. (When developing custom tables, be sure to make id your first column for this reason!)
+
 
 +
* Most Moodle installations use a prefix for all the database tables, such as <tt>mdl_</tt>. This prefix is NOT to be used in the code in the code itself.
 +
* All the $table parameters in the functions are meant to be the table name without prefixes:
 +
<code php>$user = $DB->get_record('user', ['id' => '1']);</code>
 +
* In custom SQL queries, table names must be enclosed between curly braces. They will be then automatically converted to the real prefixed table name. There is no need to access <tt>$CFG->prefix</tt>
 +
<code php>$user = $DB->get_record_sql('SELECT COUNT(*) FROM {user} WHERE deleted = 1 OR suspended = 1;');</code>
 +
 
 +
=== Conditions ===
 +
 
 +
* All the $conditions parameters in the functions are arrays of fieldname=>fieldvalue elements.
 +
* They all must be fulfilled - i.e. logical <tt>AND</tt> is used to populate the actual <tt>WHERE</tt> statement.
 +
<code php>$user = $DB->get_record('user', ['firstname' => 'Martin', 'lastname' => 'Dougiamas']);</code>
 +
 
 +
=== Placeholders ===
 +
 
 +
* All the $params parameters in the functions are arrays of values used to fill placeholders in SQL statements.
 +
* Placeholders help to avoid problems with SQL-injection and/or invalid quotes in SQL queries. They facilitate secure and cross-db compatible code.
 +
* Two types of placeholders are supported - question marks (<tt>SQL_PARAMS_QM</tt>) and named placeholders (<tt>SQL_PARAMS_NAMED</tt>).
 +
* Named params '''must be unique''' even if the value passed is the same. If you need to pass the same value multiple times, you need to have multiple distinct named parameters.
 
<code php>
 
<code php>
o $DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
+
// Example of using question mark placeholders.
  /// Get a number of records as an array of objects where all the given conditions met.
+
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = ? AND lastname = ?',  
o $DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)  
+
    ['Martin', 'Dougiamas']);
  /// Get a number of records as an array of objects which match a particular WHERE clause.
+
 
o $DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)
+
// Example of using named placeholders.
  /// Get a number of records as an array of objects using a SQL statement.
+
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = :firstname AND lastname = :lastname',
o $DB->get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')  
+
    ['firstname' => 'Martin', 'lastname' => 'Dougiamas']);
  /// Get a number of records as an array of objects where one field match one list of values.
 
 
</code>
 
</code>
  
===Getting data as key/value pairs in an associative array===
+
=== Strictness ===
<code php>
+
 
o $DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
+
Some methods accept the <tt>$strictness</tt> parameter affecting the method behaviour. Supported modes are specified using the constants:
  /// Get the first two columns from a number of records as an associative array where all the given conditions met.
+
 
o $DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
+
* <tt>MUST_EXIST</tt> - In this mode, the requested record must exist and must be unique. An exception will be thrown if no record is found or multiple matching records are found.
  /// Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
+
* <tt>IGNORE_MISSING</tt> - In this mode, a missing record is not an error. False boolean is returned if the requested record is not found. If more records are found, a debugging message is displayed.
o $DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)
+
* <tt>IGNORE_MULTIPLE</tt> - This is not a recommended mode. The function will silently ignore multiple records found and will return just the first one of them.
  /// Get the first two columns from a number of records as an associative array using a SQL statement.
+
 
</code>
+
== Getting a single record ==
 +
 
 +
=== get_record ===
 +
Return a single database record as an object where all the given conditions are met.
 +
<code php>$DB->get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING)</code>
 +
 
 +
=== get_record_select ===
 +
Return a single database record as an object where the given conditions are used in the WHERE clause.
 +
<code php>$DB->get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING)</code>
 +
 
 +
=== get_record_sql ===
 +
Return a single database record as an object using a custom SELECT query.
 +
<code php>$DB->get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING)</code>
 +
 
 +
== Getting a hashed array of records ==
 +
Each of the following methods return an array of objects. The array is indexed by the first column of the fields returned by the query. To assure consistency, it is a good practice to ensure that your query include an "id column" as the first field. When designing custom tables, make <tt>id</tt> their first column and primary key.
 +
 
 +
=== get_records ===
 +
Return a list of records as an array of objects where all the given conditions are met.
 +
<code php>$DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
  
===Seeing how many records match a given criterion===
+
=== get_records_select ===
<code php>
+
Return a list of records as an array of objects where the given conditions are used in the WHERE clause.
o $DB->count_records($table, array $conditions=null)
+
<code php>$DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
  /// Count the records in a table where all the given conditions met.
 
o $DB->count_records_select($table, $select, array $params=null, $countitem="COUNT('x')")
 
  /// Count the records in a table which match a particular WHERE clause.
 
o $DB->count_records_sql($sql, array $params=null)  
 
  /// Get the result of an SQL SELECT COUNT(...) query.
 
</code>
 
  
===Seeing if one record exists===
+
=== get_records_sql ===
<code php>
+
Return a list of records as an array of objects using a custom SELECT query.
o $DB->record_exists($table, array $conditions=null)
+
<code php>$DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)</code>
  /// Test whether a record exists in a table where all the given conditions met.
 
o $DB->record_exists_select($table, $select, array $params=null)
 
  /// Test whether any records exists in a table which match a particular WHERE clause.
 
o $DB->record_exists_sql($sql, array $params=null)  
 
  /// Test whether a SQL SELECT statement returns any records.
 
</code>
 
  
====Examples====
+
=== get_records_list ===
=====moodle_database::get_records()=====
+
Return a list of records as an array of objects where the given field matches one of the possible values.
Get a number of records as an array of objects where all the given conditions met.
+
<code php>$DB->get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')</code>
<code php>
 
///Get all records where foo = bar
 
$result = $DB->get_records($table,array('foo'=>'bar'));
 
  
///Get all records where foo = bar and jon = doe
+
== Getting data as key/value pairs in an associative array ==
$result = $DB->get_records($table,array('foo' => 'bar' , 'jon' => 'doe'));
 
  
///Get all records where foo = bar, but only return the fields foo,bar,jon,doe
+
=== get_records_menu ===
$result = $DB->get_records($table,array('foo'=>'bar'),null,'foo,bar,jon,doe');
+
Return the first two columns from a list of records as an associative array where all the given conditions are met.
///The previous example would cause data issues unless the 'foo' field happens to have unique values.
+
<code php>$DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
</code>
 
  
=====moodle_database::get_records_select()=====
+
=== get_records_select_menu ===
Get a number of records as an array of objects which match a particular WHERE clause. Note that the array keys will be the id of the object so you must not rely on the first item having a key of 0.
+
Return the first two columns from a list of records as an associative array where the given conditions are used in the WHERE clause.
<code php>
+
<code php>$DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
///Get all records where jon = 'doe' and bob is not = 'tom'
 
///The 'select' parameter is (if not empty) is dropped directly into the WHERE clause without alteration.
 
$table = 'foo';
 
$select = "jon = 'doe' AND bob <> 'tom'"; //is put into the where clause
 
$result = $DB->get_records_select($table,$select);
 
</code>
 
  
=====moodle_database::get_records_sql()=====
+
=== get_records_sql_menu ===
Get a number of records as an array of objects using a SQL statement. Defined as an abstract function in moodle_database, this method is implemented per database type.
+
Return the first two columns from a number of records as an associative array using a custom SELECT query.
<code php>
+
<code php>$DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)</code>
///Get all records from 'table' where foo = bar
 
$result = $DB->get_records_sql('SELECT * FROM {table} WHERE foo = ?', array('bar'));
 
  
///Get all records from 'table' where foo = 'bar' and bob = 'tom'
+
== Counting records that match the given criteria ==
///This is somewhat similar to how Drupal makes its queries
 
$result = $DB->get_records_sql('SELECT * FROM {table} WHERE foo = ? AND bob = ?', array( 'bar' , 'tom' ));
 
</code>
 
  
=====moodle_database::get_records_list()=====
+
=== count_records ===
Get a number of records as an array of objects where one field match one list of values.
+
Count the records in a table where all the given conditions are met.
<code php>
+
<code php>$DB->count_records($table, array $conditions=null) </code>
///Get all records where the values('bar', 'elephant', 'moodle') are found in the field 'foo'
 
$result = $DB->get_records_list($table, 'foo', array( 'bar', 'elephant', 'moodle'));
 
  
///Get all records where the values('bar', 'elephant', 'moodle') are found in the field 'foo'
+
=== count_records_select ===
///Only returning the fields 'id', 'test' and 'taco'
+
Count the records in a table where the given conditions are used in the WHERE clause.
$result = $DB->get_records_list($table, 'foo', array( 'bar', 'elephant', 'moodle'), null, 'id,test,taco');
+
<code php>$DB->count_records_select($table, $select, array $params=null, $countitem="COUNT('x')")</code>
</code>
 
  
=====moodle_database::get_records_menu()=====
+
=== count_records_sql ===
Get the first two columns from a number of records as an associative array where all the given conditions met.  
+
Counting the records using a custom SELECT COUNT(...) query.
You can choose the two fields or leave the parameter blank and the method will return the first two columns of the table.
+
<code php>$DB->count_records_sql($sql, array $params=null)</code>
Returns an associative array.  
 
<code php>
 
///Get all records from table 'foo' where column 'foo' is equal to the value 'bar'
 
$table = 'foo'; ///name of table
 
$conditions = array('foo'=>'bar'); ///the name of the field (key) and the desired value
 
  
$result = $DB->get_records_menu($table,$conditions));
+
== Checking if a given record exists ==
  
///Get all records from table 'foo' where column 'foo' is equal to the value 'bar'
+
=== record_exists ===
///Returning the values from the columns 'id' and 'tacos'
+
Test whether a record exists in a table where all the given conditions are met.
$table = 'foo'; ///name of table
+
<code php>$DB->record_exists($table, array $conditions=null)</code>
$conditions = array('foo'=>'bar'); ///the name of the field (key) and the desired value
 
$sort = 'id'; //field or fields you want to sort the result by
 
$fields = 'id, tacos'; ///list of fields to return
 
  
$result = $DB->get_records_menu($table,$conditions,$sort,$fields))//If you do not specify $fields, the first two columns of the table will be returned
+
=== record_exists_select ===
 +
Test whether any records exists in a table where the given conditions are used in the WHERE clause.
 +
<code php>$DB->record_exists_select($table, $select, array $params=null)</code>
  
</code>
+
=== record_exists_sql ===
The result of this last example will look something like:
+
Test whether the given SELECT query would return any record.
<code php>
+
<code php>$DB->record_exists_sql($sql, array $params=null)</code>
/// The value of the id field  is 909 and the value of the 'tacos' column is 6
 
array(1) { [909]=6 }
 
</code>
 
  
=====moodle_database::get_records_select_menu()=====
+
== Getting a particular field value from one record ==
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
 
<code php>
 
///Get all records where jon = 'doe' and bob is not = 'tom'
 
///The 'select' parameter is (if not empty) is dropped directly into the WHERE clause without alteration.
 
$table = 'foo';
 
$select = 'jon = ? AND bob <> ? '; //is put into the where clause
 
$result = $DB->get_records_select_menu($table, $select, array('doe', 'tom'));
 
  
$table = 'foo';
+
=== get_field ===
$select = 'jon = ? AND bob <> ? '; //is put into the where clause
+
Get a single field value from a table record where all the given conditions are met.
$params = array('doe', 'tom');
+
<code php>$DB->get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING)</code>
$fields = 'id, tacos';//return these fields
 
$sort = 'id'; //field or fields you want to sort the result by
 
$result = $DB->get_records_select_menu($table,$select,$params,$sort,$fields);
 
</code>
 
  
The result of this last example will look something like:
+
=== get_field_select ===
<code php>
+
Get a single field value from a table record where the given conditions are used in the WHERE clause.
/// The value of the id field  is 909 and the value of the 'tacos' column is 6
+
<code php>$DB->get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING)</code>
array(1) { [909]=6 }
 
</code>
 
  
=====moodle_database::get_records_sql_menu()=====
+
=== get_field_sql ===
Get the first two columns from a number of records as an associative array using a SQL statement.
+
Get a single field value (first field) using a custom SELECT query.
<code php>
+
<code php>$DB->get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING)</code>
///Get all records from table foo where bar = 6
 
$sql = 'SELECT * FROM foo WHERE bar = ?';
 
$params = array(6);
 
  
$result = $DB->get_records_sql_menu($sql,$params);
+
== Getting field values from multiple records ==
  
///Get all records from table foo where bar = 6
+
=== get_fieldset_select ===
$sql = 'SELECT id,tacos FROM foo WHERE bar = ?';
+
Return values of the given field as an array where the given conditions are used in the WHERE clause.
$params = array(6);
+
<code php>$DB->get_fieldset_select($table, $return, $select, array $params=null)</code>
  
$result = $DB->get_records_sql_menu($sql,$params);
+
=== get_fieldset_sql ===
 +
Return values of the first column as an array using a custom SELECT field FROM ... query.
 +
<code php>$DB->get_fieldset_sql($sql, array $params=null)</code>
  
 +
== Setting a field value ==
  
</code>
+
=== set_field ===
 +
Set a single field in every record where all the given conditions are met.
 +
<code php>$DB->set_field($table, $newfield, $newvalue, array $conditions=null)</code>
  
The result of this last example will look something like:
+
=== set_field_select ===
<code php>
+
Set a single field in every table record where the given conditions are used in the WHERE clause.
/// The value of the id field  is 909 and the value of the 'tacos' column is 6
+
<code php>$DB->set_field_select($table, $newfield, $newvalue, $select, array $params=null)</code>
array(1) { [909]=6 }
 
</code>
 
  
===Getting a particular field value from one record===
+
== Deleting records ==
<code php>
 
o $DB->get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING)
 
  /// Get a single field value from a table record where all the given conditions met.
 
  /// @param int $strictness
 
  ///  IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
 
  ///  IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
 
  ///  MUST_EXIST means throw exception if no record or multiple records found
 
o $DB->get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING)
 
  /// Get a single field value from a table record which match a particular WHERE clause.
 
o $DB->get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING)
 
  /// Get a single field value (first field) using a SQL statement.
 
</code>
 
  
===Getting a particular field value from various records===
+
=== delete_records ===
 +
Delete records from the table where all the given conditions are met.
 +
<code php>$DB->delete_records($table, array $conditions=null)</code>
  
<code php>
+
=== delete_records_select ===
o $DB->get_fieldset_select($table, $return, $select, array $params=null)
+
Delete records from the table where the given conditions are used in the WHERE clause.
  /// Selects records and return values of chosen field as an array which match a particular WHERE clause.
+
<code php>$DB->delete_records_select($table, $select, array $params=null)</code>
o $DB->get_fieldset_sql($sql, array $params=null)
 
  /// Selects records and return values (first field) as an array using a SQL statement.
 
</code>
 
  
===Setting a particular field in the database===
+
== Inserting records ==
<code php>
 
o $DB->set_field($table, $newfield, $newvalue, array $conditions=null)
 
  /// Set a single field in every table record where all the given conditions met.
 
o $DB->set_field_select($table, $newfield, $newvalue, $select, array $params=null)
 
  /// Set a single field in every table record which match a particular WHERE clause.
 
</code>
 
  
===Deleting Records===
+
=== insert_record ===
<code php>
+
Insert the given data object into the table and return the "id" of the newly created record.
o $DB->delete_records($table, array $conditions=null)
+
<code php>$DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)</code>
  /// Delete the records from a table where all the given conditions met.
 
o $DB->delete_records_select($table, $select, array $params=null)
 
  /// Delete one or more records from a table which match a particular WHERE clause.
 
</code>
 
  
===Inserting Records===
+
=== insert_records ===
The method to insert records is called aptly enough, insert_record(). The method accepts 4 parameters, but the fourth, "bulk", in most implementations is unused. Note, you cannot specify the record id using this method. If you need to specify an id use $DB->insert_record_raw() with $customsequence set to true or use $DB->execute().
+
{{Moodle_2.7}}Insert multiple records into the table as fast as possible. Records are inserted in the given order, but the operation is not atomic. Use transactions if necessary.
<code php>
+
<code php>$DB->insert_records($table, $dataobjects)</code>
  $DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)  
 
  /// Insert a record into a table and return the "id" field if required.
 
</code>
 
  
Starting with Moodle 2.7, you can do bulk record inserts using the following method call: {{Moodle_2.7}}
+
=== insert_record_raw ===
<code php>
+
For rare cases when you also need to specify the ID of the record to be inserted.
/**
 
* Insert multiple records into database as fast as possible.
 
*
 
* Order of inserts is maintained, but the operation is not atomic, use transactions if necessary.
 
*
 
* This method is intended for inserting of large number of small objects, do not use for huge objects with text or binary fields.
 
*
 
* @param string $table  The database table to be inserted into
 
* @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
 
* @return void does not return new record ids
 
*/
 
  $DB->insert_records($table, $dataobjects)
 
</code>
 
  
====Example(s)====
+
== Updating records ==
<code php>
 
$record = new stdClass();
 
$record->name        = 'overview';
 
$record->displayorder = '10000';
 
$lastinsertid = $DB->insert_record('quiz_report', $record, false);
 
</code>
 
  
<code php>
+
=== update_record ===
$record1 = new stdClass();
+
Update a record in the table. The data object must have the property "id" set.
$record1->name        = 'overview';
+
<code php>$DB->update_record($table, $dataobject, $bulk=false)</code>
$record1->displayorder = '10000';
 
$record2 = new stdClass();
 
$record2->name        = 'overview';
 
$record2->displayorder = '10000';
 
$records = array($record1, $record2);
 
$lastinsertid = $DB->insert_records('quiz_report', $records);
 
</code>
 
  
===Updating Records===
+
== Executing a custom query ==
<code php>
 
o $DB->update_record($table, $dataobject, $bulk=false)
 
  /// Update a record in a table.
 
  ///
 
  /// $dataobject is an object containing needed data
 
  /// Relies on $dataobject having a variable "id" to
 
  /// specify the record to update
 
  ///
 
  /// @param string $table The database table to be checked against.
 
  /// @param object $dataobject An object with contents equal to fieldname=>fieldvalue.
 
  ///        Must have an entry for 'id' to map to the table specified.
 
  /// @param bool $bulk true means repeated updates expected
 
  /// @return bool true
 
  /// @throws dml_exception if an error occurs.
 
</code>
 
  
If you need to perform a more complex update using arbitrary SQL, you can use the 'execute' method. Only use this when nothing more specific will work
+
=== execute ===
<code php>
+
* If you need to perform a complex update using arbitrary SQL, you can use the low level "execute" method. Only use this when no specialised method exists.
o $DB->execute($sql, array $parms=null)
+
* Do NOT use this to make changes in database structure, use database_manager methods instead!
  /// Executes a general sql query. Should be used only when no other method suitable.
+
<code php>$DB->execute($sql, array $params=null)</code>
  /// Do NOT use this to make changes in db structure, use database_manager methods instead!
 
  /// @param string $sql query
 
  /// @param array $params query parameters
 
  /// @return bool true
 
  /// @throws dml_exception A DML specific exception is thrown for any errors.
 
</code>
 
  
===Using Recordsets===
+
== Using recordsets ==
  
Where the number of records to be retrieved from DB is high, the '''get_records_xxx()''' functions above are far from optimal, because they load all the records in memory at the same time. Under those circumstances, it is highly recommended to use these '''get_recordset_xxx()''' functions instead, which use one nice mechanism to iterate over all the target records and save a lot of memory.
+
If the number of records to be retrieved from DB is high, the 'get_records_xxx() functions above are far from optimal, because they load all the records into the memory via the returned array. Under those circumstances, it is highly recommended to use these get_recordset_xxx() functions instead. They return an iterator to iterate over all the found records and save a lot of memory.
  
Only one thing is '''absolutely important''': Don't forget to close the recordsets after using them! (This will free up a lot of resources in the RDBMS).
+
It is '''absolutely important''' to not forget to close the returned recordset iterator after using it. This is to free up a lot of resources in the RDBMS.
  
Here is the general way to iterate over records using the '''get_recordset_xxx()''' functions:
+
A general way to iterate over records using the get_recordset_xxx() functions:
  
 
<code php>
 
<code php>
Line 329: Line 227:
 
     // Do whatever you want with this record
 
     // Do whatever you want with this record
 
}
 
}
$rs->close(); // Don't forget to close the recordset!
+
$rs->close();
 
</code>
 
</code>
  
And this is the list of available functions (100% paired with the get_records_xxx() above):
+
Unlike get_record functions, you cannot check if <tt>$rs == true</tt> or <tt>!empty($rs)</tt> to determine if any records were found. Instead, if you need to, you can use:
<code php>
 
o $DB->get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
 
  /// Get a number of records as a moodle_recordset where all the given conditions met.
 
o $DB->get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
 
  /// Get a number of records as a moodle_recordset which match a particular WHERE clause.
 
o $DB->get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
 
  /// Get a number of records as a moodle_recordset using a SQL statement.
 
 
o $DB->get_recordset_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='')
 
  /// Get a number of records as a moodle_recordset where one field matches one list of values.
 
</code>
 
 
 
Unlike get_record functions, you cannot use <tt>$rs == true</tt> or <tt>!empty($rs)</tt> to determine if any records were found.
 
Recordsets implement the standard PHP Iterator interface (http://uk.php.net/manual/en/class.iterator.php)
 
  
So,
 
 
<code php>
 
<code php>
 
if ($rs->valid()) {
 
if ($rs->valid()) {
     // The recordset contains records.
+
     // The recordset contains some records.
 
}
 
}
 
</code>
 
</code>
  
===Delegated transactions===
+
=== get_recordset ===
 +
Return a list of records as a moodle_recordset where all the given conditions are met.
 +
<code php>$DB->get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
 +
 
 +
=== get_recordset_select ===
 +
Return a list of records as a moodle_recordset where the given conditions are used in the WHERE clause.
 +
<code php>$DB->get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code>
 +
 
 +
=== get_recordset_sql ===
 +
Return a list of records as an array of objects using a custom SELECT query.
 +
<code php>$DB->get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)</code>
 +
 
 +
=== get_recordset_list ===
 +
Return a list of records as a moodle_recordset where the given field matches one of the possible values.
 +
<code php>$DB->get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')</code>
 +
 
 +
== Delegated transactions ==
  
 
* Please note some databases do not support transactions (such as the MyISAM MySQL database engine), however all server administrators are strongly encouraged to migrate to databases that support transactions (such as the InnoDB MySQL database engine).
 
* Please note some databases do not support transactions (such as the MyISAM MySQL database engine), however all server administrators are strongly encouraged to migrate to databases that support transactions (such as the InnoDB MySQL database engine).
 
* Previous versions supported only one level of transaction. Since Moodle 2.0, the DML layer emulates delegated transactions that allow nesting of transactions.
 
* Previous versions supported only one level of transaction. Since Moodle 2.0, the DML layer emulates delegated transactions that allow nesting of transactions.
* Transactions should not be used much in Moodle core; they are intended for various plugins such as web services, enrol and auth plugins.
+
* Some subsystems (such as messaging) do not support transactions because it is not possible to rollback in external systems.
* Some subsystems (such as messaging) do not support transactions because is it is not possible to rollback in external systems.
 
  
 
A transaction is started by:
 
A transaction is started by:
Line 372: Line 270:
 
</code>
 
</code>
  
Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> must be used very carefully because it might break compatibility with databases that do not support transactions. Transactions cannot be used as part of expected code flow; they can be used only as an emergency protection of data consistency.
+
Usually a transaction is rolled back when an exception is thrown:
 +
<code php>$transaction->rollback($ex);</code>
 +
 
 +
which must be used very carefully because it might break compatibility with databases that do not support transactions. Transactions cannot be used as part of expected code flow; they can be used only as an emergency protection of data consistency.
  
 
See more details in [[DB layer 2.0 delegated transactions]] or MDL-20625.
 
See more details in [[DB layer 2.0 delegated transactions]] or MDL-20625.
  
====Example(s)====
+
=== Example ===
  
 
<code php>
 
<code php>
Line 382: Line 283:
 
try {
 
try {
 
     $transaction = $DB->start_delegated_transaction();
 
     $transaction = $DB->start_delegated_transaction();
    // Insert a record
 
 
     $DB->insert_record('foo', $object);
 
     $DB->insert_record('foo', $object);
 
     $DB->insert_record('bar', $otherobject);
 
     $DB->insert_record('bar', $otherobject);
Line 388: Line 288:
 
     // Assuming the both inserts work, we get to the following line.
 
     // Assuming the both inserts work, we get to the following line.
 
     $transaction->allow_commit();
 
     $transaction->allow_commit();
 +
 
} catch(Exception $e) {
 
} catch(Exception $e) {
 
     $transaction->rollback($e);
 
     $transaction->rollback($e);
Line 393: Line 294:
 
</code>
 
</code>
  
===SQL compatibility functions===
+
== Cross-DB compatibility ==
 +
 
 +
Moodle supports several SQL servers (MySQL, PostgreSQL, MS-SQL and Oracle). Each of them have some specific syntax in certain cases. In order to achieve cross-db compatibility of the code, following functions must be used to generate the fragments of the query valid for the actual SQL server.
 +
 
 +
=== sql_bitand ===
 +
Return the SQL text to be used in order to perform a bitwise AND operation between 2 integers.
 +
<code php>$DB->sql_bitand($int1, $int2)</code>
 +
 
 +
=== sql_bitnot ===
 +
Return the SQL text to be used in order to perform a bitwise NOT operation on the given integer.
 +
<code php>$DB->sql_bitnot($int1)</code>
 +
 
 +
=== sql_bitor ===
 +
Return the SQL text to be used in order to perform a bitwise OR operation between 2 integers.
 +
<code php>$DB->sql_bitor($int1, $int2)</code>
 +
 
 +
=== sql_bitxor ===
 +
Return the SQL text to be used in order to perform a bitwise XOR operation between 2 integers.
 +
<code php>$DB->sql_bitxor($int1, $int2)</code>
 +
 +
=== sql_null_from_clause ===
 +
Return an empty FROM clause required by some DBs in all SELECT statements.
 +
<code php>$DB->sql_null_from_clause()</code>
 +
 
 +
=== sql_ceil ===
 +
Return the correct CEIL expression applied to the given fieldname.
 +
<code php>$DB->sql_ceil($fieldname)</code>
 +
 
 +
=== sql_equal ===
 +
{{Moodle 3.2}}Return the query framgment to perform cross-db varchar comparisons when case-sensitiveness is important.
 +
<code php>$DB->sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false)</code>
  
In order have real cross-db compatibility, there are some helper functions used to build SQL fragments based on the DB Moodle is running. Using them we'll avoid conditional queries here and there and have those "incompatibilities" fixed once and for ever.
+
=== sql_like ===
 +
Return the query fragment to perform the LIKE comparison.
 +
<code php>$DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')</code>
 +
 
 +
Example: Searching for records partially matching the given hard-coded literal.
 +
<code php>$DB->get_records_sql('SELECT id,fullname FROM {course} WHERE '.$DB->sql_like('idnumber', ':idnum'), ['idnum' => 'DEMO-%'));</code>
 +
See below if you need to compare with a value submitted by the user.
 +
 
 +
=== sql_like_escape ===
 +
Escape the value submitted by the user so that it can be used for partial comparison and the special characters like '_' or '%' behave as literal characters, not wildcards.
 +
<code php>$DB->sql_like_escape($text, $escapechar = '\\')</code>
 +
 
 +
Example: If you need to perform a partial comparison with a value that has been submitted by the user.
 
<code php>
 
<code php>
o $DB->sql_bitand($int1, $int2)  
+
$search = required_param('search', PARAM_RAW);
  /// Returns the SQL text to be used in order to perform one bitwise AND
+
$DB->get_records_sql('SELECT id,fullname FROM {course} WHERE '.$DB->sql_like('fullname', ':fullname'), ['fullname' => '%'.$DB->sql_like_escape($search).'%']);
  /// operation between 2 integers.
+
</code>
o $DB->sql_bitnot($int1)  
+
 
  /// Returns the SQL text to be used in order to perform one bitwise NOT
+
=== sql_length ===
  /// operation with 1 integer.
+
Return the query fragment to be used to calculate the length of the expression in characters.
o $DB->sql_bitor($int1, $int2)
+
<code php>$DB->sql_length($fieldname)</code>
  /// Returns the SQL text to be used in order to perform one bitwise OR
+
 
  /// operation between 2 integers.
+
=== sql_modulo ===
o $DB->sql_bitxor($int1, $int2)  
+
Return the query fragment to be used to calculate the remainder after division.
  /// Returns the SQL text to be used in order to perform one bitwise XOR
+
<code php>$DB->sql_modulo($int1, $int2)</code>
  /// operation between 2 integers.
+
 
 +
=== sql_position ===
 +
Return the query fragment for searching a string for the location of a substring. If both needle and haystack use placeholders, you must use named placeholders.
 +
<code php>$DB->sql_position($needle, $haystack)</code>
 +
 
 +
=== sql_substr ===
 +
Return the query fragment for extracting a substring from the given expression.
 +
<code php>$DB->sql_substr($expr, $start, $length=false)</code>
 
   
 
   
o $DB->sql_null_from_clause()
+
=== sql_cast_char2int ===
  /// Returns the FROM clause required by some DBs in all SELECT statements.
+
Return the query fragment to cast a CHAR column to INTEGER
+
<code php>$DB->sql_cast_char2int($fieldname, $text=false)</code>
o $DB->sql_ceil($fieldname)
+
 
  /// Returns the correct CEIL expression applied to fieldname.
+
=== sql_cast_char2real ===
o $DB->sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) (available since Moodle 3.2)
+
Return the query fragment to cast a CHAR column to REAL (float) number
  /// Returns the proper SQL to perform cross-db varchar comparisons when case-sensitiveness is mission-critical.
+
<code php>$DB->sql_cast_char2real($fieldname, $text=false)</code>
o $DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
+
 
  /// Returns the proper SQL to do LIKE. For example:
+
=== sql_compare_text ===
  $DB->get_records_sql('SELECT ...  WHERE '.$DB->sql_like('idnumber', ':idnum').' ... ', array( 'idnum' => '%foo%'));
+
Return the query fragment to be used when comparing a TEXT (clob) column with a given string or a VARCHAR field (some RDBMs do not allow for direct comparison).
  /// Note: Apply $DB->sql_like_escape(...) to the param values when its user input from a form. Don't forget to add the % character(s) to the param value
+
<code php>$DB->sql_compare_text($fieldname, $numchars=32)</code>
 +
 
 +
Example:
 +
<code php>
 +
$todogroups = $DB->get_records_sql('SELECT id FROM {group} WHERE ' . $DB->sql_compare_text('description') . ' = ' . $DB->sql_compare_text(':description'), ['description' => 'TODO']);
 +
</code>
 +
 
 +
=== sql_order_by_text ===
 +
Return the query fragment to be used to get records ordered by a TEXT (clob) column. Note this affects the performance badly and should be avoided if possible.
 +
<code php>$DB->sql_order_by_text($fieldname, $numchars=32)</code>
 +
 
 +
=== sql_concat ===
 +
Return the query fragment to concatenate all given paremeters into one string.
 +
<code php>$DB->sql_concat(...)</code>
 +
 
 +
=== sql_concat_join ===
 +
Return the query fragment to concatenate all given elements into one string using the given separator.
 +
<code php>$DB->sql_concat_join($separator="' '", $elements=array())</code>
 +
 
 +
=== sql_fullname ===
 +
Return the query fragment to concatenate the given $firstname and $lastname
 +
<code php>$DB->sql_fullname($first='firstname', $last='lastname')</code>
 +
 
 +
===  sql_isempty ===
 +
Return the query fragment to check if the field is empty
 +
<code php>$DB->sql_isempty($tablename, $fieldname, $nullablefield, $textfield)</code>
 +
 
 +
=== sql_isnotempty ===
 +
Return the query fragment to check if the field is not empty
 +
<code php>$DB->sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield)</code>
  
o $DB->sql_length($fieldname)
+
=== get_in_or_equal ===
  /// Returns the SQL text to be used to calculate the length in characters of one expression.
+
Return the query fragment to check if a value is IN the given list of items (with a fallback to plain equal comparison if there is just one item)
o $DB->sql_modulo($int1, $int2)
+
<code php>$DB->get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false)</code>
  /// Returns the SQL text to be used in order to calculate module - remainder after division
 
o $DB->sql_position($needle, $haystack)
 
  /// Returns the SQL for returning searching one string for the location of another.
 
  /// Note: If using placeholders BOTH in $needle and $haystack, they MUST be named placeholders.
 
o $DB->sql_substr($expr, $start, $length=false)
 
  /// Returns the proper substr() SQL text used to extract substrings from DB.
 
  /// Note: This fuction has changed in Moodle 2.0 and now at least 2 params are mandatory.
 
  /// Note: Now it returns the whole SQL text to be used instead of only the function name.
 
 
o $DB->sql_cast_char2int($fieldname, $text=false)
 
  /// Returns the SQL to be used in order to CAST one CHAR column to INTEGER.
 
o $DB->sql_cast_char2real($fieldname, $text=false)
 
  /// Returns the SQL to be used in order to CAST one CHAR column to REAL number.
 
 
o $DB->sql_compare_text($fieldname, $numchars=32)
 
  /// Returns the SQL text to be used to compare one TEXT (clob) column.
 
  /// with one VARCHAR column.
 
o $DB->sql_order_by_text($fieldname, $numchars=32)
 
  /// Returns the SQL text to be used to order by one TEXT (clob) column.
 
 
o $DB->sql_concat()
 
  /// Returns the proper SQL to do CONCAT between the elements passed.
 
o $DB->sql_concat_join($separator="' '", $elements=array())
 
  /// Returns the proper SQL to do CONCAT between the elements passed using one separator.
 
o $DB->sql_fullname($first='firstname', $last='lastname')
 
  /// Returns the proper SQL to concatenate $firstname and $lastname.
 
 
o $DB->sql_isempty($tablename, $fieldname, $nullablefield, $textfield)
 
  /// Returns the proper SQL to know if one field is empty.
 
o $DB->sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield)
 
  /// Returns the proper SQL to know if one field is not empty.
 
o $DB->sql_empty()
 
  /// Returns the empty string char used by every supported DB.
 
  
o $DB->get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false)
+
Example:
  /// Constructs 'IN()' or '=' sql fragment
+
<code php>
 +
$statuses = ['todo', 'open', 'inprogress', 'intesting'];
 +
list($insql, $inparams) = $DB->get_in_or_equal($statuses);
 +
$sql = "SELECT * FROM {bugtracker_issues} WHERE status $insql";
 +
$bugs = $DB->get_records_sql($sql, $inparams);
 
</code>
 
</code>
  
===Debug fuctions===
+
=== sql_regex_supported ===
 +
Does the current database driver support regex syntax when searching?
 +
<code php>$DB->sql_regex_supported()</code>
 +
 
 +
=== sql_regex ===
 +
Return the query fragment to perform a regex search.
 +
<code php>$DB->sql_regex($positivematch = true, $casesensitive = false)</code>
  
If you execute
+
Example: Searching for Page module instances containing links.
 
<code php>
 
<code php>
$DB->set_debug(true)
+
if ($DB->sql_regex_supported()) {
 +
    $select = 'content ' . $DB->sql_regex() . ' :pattern';
 +
    $params = ['pattern' => "(src|data)\ *=\ *[\\\"\']https?://"]
 +
} else {
 +
    $select = $DB->sql_like('content', ':pattern', false);
 +
    $params = ['pattern' => '%=%http%://%'];
 +
}
 +
 
 +
$pages = $DB->get_records_select('page', $select, $params, 'course', 'id, course, name');
 
</code>
 
</code>
then $DB will outout the SQL of every query executed, along with timing information. This can be useful when debugging your code. Obviously, all such calls should be removed before code is submitted for integration.
 
  
==Special cases==
+
=== sql_intersect ===
 +
{{Moodle 2.8}}Return the query fragment that allows to find intersection of two or more queries
 +
<code php>$DB->sql_intersect($selects, $fields)</code>
  
===get_course===
+
== Debugging ==
  
From Moodle 2.5.1 onwards, you should use the get_course function instead of using get_record('course', ...) if you want to get a course record based on its ID, especially if there is a significant possibility that the course being retrieved is either the current course for the page, or the site course. Those two course records have probably already been loaded, and using this function will save a database query.  
+
=== set_debug ===
 +
You can enable a debugging mode to make $DB output the SQL of every executed query, along with some timing information. This can be useful when debugging your code. Obviously, all such calls should be removed before code is submitted for integration.
 +
<code php>$DB->set_debug(true)</code>
  
As another advantage, the code is shorter and easier to read.
+
== Special cases ==
  
Replace:
+
=== get_course ===
  
    $course = $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST);
+
From Moodle 2.5.1 onwards, you should use the get_course function instead of using get_record('course', ...) if you want to get a course record based on its ID, especially if there is a significant possibility that the course being retrieved is either the current course for the page, or the site course. Those two course records have probably already been loaded, and using this function will save a database query.
  
With:
+
Additionally, the code is shorter and easier to read.
 
    $course = get_course($courseid);
 
  
===get_courses===
+
=== get_courses ===
  
 
If you want to get all the current courses in your Moodle, use get_courses() without parameter:
 
If you want to get all the current courses in your Moodle, use get_courses() without parameter:
Line 493: Line 458:
 
     $courses = get_courses();
 
     $courses = get_courses();
  
 
+
== See also ==
==See also==
 
  
 
* [[SQL coding style]]
 
* [[SQL coding style]]

Latest revision as of 15:55, 15 March 2019

Moodle 2.0

This page describes the functions available to access data in the Moodle database. You should exclusively use these functions in order to retrieve or modify database content because these functions provide a high level of abstraction and guarantee that your database manipulation will work against different RDBMSes.

Where possible, tricks and examples will be documented here in order to make developers' lives a bit easier. Of course, feel free to clarify, complete and add more information to this documentation. It will be welcome, absolutely!

Contents

General concepts

DB object

  • The data manipulation API is exposed via public methods of the $DB object.
  • Moodle core takes care of setting up the connection to the database according to values specified in the main config.php file.
  • The $DB global object is an instance of the moodle_database class. It is instantiated automatically during the bootstrap setup, i.e. as a part of including the main config.php file.
  • The DB object is available in the global scope right after including the config.php file:
<?php
 
require(__DIR__.'/../../../config.php');
 
// You can access the database via the $DB method calls here.
  • To make the DB object available in your local scope, such as within a function:
<?php
 
defined('MOODLE_INTERNAL') || die();
 
function my_function_making_use_of_database() {
    global $DB;
 
    // You can access the database via the $DB method calls here.
}

Table prefix

  • Most Moodle installations use a prefix for all the database tables, such as mdl_. This prefix is NOT to be used in the code in the code itself.
  • All the $table parameters in the functions are meant to be the table name without prefixes:
$user = $DB->get_record('user', ['id' => '1']);
  • In custom SQL queries, table names must be enclosed between curly braces. They will be then automatically converted to the real prefixed table name. There is no need to access $CFG->prefix
$user = $DB->get_record_sql('SELECT COUNT(*) FROM {user} WHERE deleted = 1 OR suspended = 1;');

Conditions

  • All the $conditions parameters in the functions are arrays of fieldname=>fieldvalue elements.
  • They all must be fulfilled - i.e. logical AND is used to populate the actual WHERE statement.
$user = $DB->get_record('user', ['firstname' => 'Martin', 'lastname' => 'Dougiamas']);

Placeholders

  • All the $params parameters in the functions are arrays of values used to fill placeholders in SQL statements.
  • Placeholders help to avoid problems with SQL-injection and/or invalid quotes in SQL queries. They facilitate secure and cross-db compatible code.
  • Two types of placeholders are supported - question marks (SQL_PARAMS_QM) and named placeholders (SQL_PARAMS_NAMED).
  • Named params must be unique even if the value passed is the same. If you need to pass the same value multiple times, you need to have multiple distinct named parameters.
// Example of using question mark placeholders.
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = ? AND lastname = ?', 
    ['Martin', 'Dougiamas']);
 
// Example of using named placeholders.
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = :firstname AND lastname = :lastname',
    ['firstname' => 'Martin', 'lastname' => 'Dougiamas']);

Strictness

Some methods accept the $strictness parameter affecting the method behaviour. Supported modes are specified using the constants:

  • MUST_EXIST - In this mode, the requested record must exist and must be unique. An exception will be thrown if no record is found or multiple matching records are found.
  • IGNORE_MISSING - In this mode, a missing record is not an error. False boolean is returned if the requested record is not found. If more records are found, a debugging message is displayed.
  • IGNORE_MULTIPLE - This is not a recommended mode. The function will silently ignore multiple records found and will return just the first one of them.

Getting a single record

get_record

Return a single database record as an object where all the given conditions are met.

$DB->get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING)

get_record_select

Return a single database record as an object where the given conditions are used in the WHERE clause.

$DB->get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING)

get_record_sql

Return a single database record as an object using a custom SELECT query.

$DB->get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING)

Getting a hashed array of records

Each of the following methods return an array of objects. The array is indexed by the first column of the fields returned by the query. To assure consistency, it is a good practice to ensure that your query include an "id column" as the first field. When designing custom tables, make id their first column and primary key.

get_records

Return a list of records as an array of objects where all the given conditions are met.

$DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_records_select

Return a list of records as an array of objects where the given conditions are used in the WHERE clause.

$DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_records_sql

Return a list of records as an array of objects using a custom SELECT query.

$DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)

get_records_list

Return a list of records as an array of objects where the given field matches one of the possible values.

$DB->get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')

Getting data as key/value pairs in an associative array

get_records_menu

Return the first two columns from a list of records as an associative array where all the given conditions are met.

$DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_records_select_menu

Return the first two columns from a list of records as an associative array where the given conditions are used in the WHERE clause.

$DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_records_sql_menu

Return the first two columns from a number of records as an associative array using a custom SELECT query.

$DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)

Counting records that match the given criteria

count_records

Count the records in a table where all the given conditions are met.

$DB->count_records($table, array $conditions=null)

count_records_select

Count the records in a table where the given conditions are used in the WHERE clause.

$DB->count_records_select($table, $select, array $params=null, $countitem="COUNT('x')")

count_records_sql

Counting the records using a custom SELECT COUNT(...) query.

$DB->count_records_sql($sql, array $params=null)

Checking if a given record exists

record_exists

Test whether a record exists in a table where all the given conditions are met.

$DB->record_exists($table, array $conditions=null)

record_exists_select

Test whether any records exists in a table where the given conditions are used in the WHERE clause.

$DB->record_exists_select($table, $select, array $params=null)

record_exists_sql

Test whether the given SELECT query would return any record.

$DB->record_exists_sql($sql, array $params=null)

Getting a particular field value from one record

get_field

Get a single field value from a table record where all the given conditions are met.

$DB->get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING)

get_field_select

Get a single field value from a table record where the given conditions are used in the WHERE clause.

$DB->get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING)

get_field_sql

Get a single field value (first field) using a custom SELECT query.

$DB->get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING)

Getting field values from multiple records

get_fieldset_select

Return values of the given field as an array where the given conditions are used in the WHERE clause.

$DB->get_fieldset_select($table, $return, $select, array $params=null)

get_fieldset_sql

Return values of the first column as an array using a custom SELECT field FROM ... query.

$DB->get_fieldset_sql($sql, array $params=null)

Setting a field value

set_field

Set a single field in every record where all the given conditions are met.

$DB->set_field($table, $newfield, $newvalue, array $conditions=null)

set_field_select

Set a single field in every table record where the given conditions are used in the WHERE clause.

$DB->set_field_select($table, $newfield, $newvalue, $select, array $params=null)

Deleting records

delete_records

Delete records from the table where all the given conditions are met.

$DB->delete_records($table, array $conditions=null)

delete_records_select

Delete records from the table where the given conditions are used in the WHERE clause.

$DB->delete_records_select($table, $select, array $params=null)

Inserting records

insert_record

Insert the given data object into the table and return the "id" of the newly created record.

$DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)

insert_records

Moodle 2.7 Insert multiple records into the table as fast as possible. Records are inserted in the given order, but the operation is not atomic. Use transactions if necessary.

$DB->insert_records($table, $dataobjects)

insert_record_raw

For rare cases when you also need to specify the ID of the record to be inserted.

Updating records

update_record

Update a record in the table. The data object must have the property "id" set.

$DB->update_record($table, $dataobject, $bulk=false)

Executing a custom query

execute

  • If you need to perform a complex update using arbitrary SQL, you can use the low level "execute" method. Only use this when no specialised method exists.
  • Do NOT use this to make changes in database structure, use database_manager methods instead!
$DB->execute($sql, array $params=null)

Using recordsets

If the number of records to be retrieved from DB is high, the 'get_records_xxx() functions above are far from optimal, because they load all the records into the memory via the returned array. Under those circumstances, it is highly recommended to use these get_recordset_xxx() functions instead. They return an iterator to iterate over all the found records and save a lot of memory.

It is absolutely important to not forget to close the returned recordset iterator after using it. This is to free up a lot of resources in the RDBMS.

A general way to iterate over records using the get_recordset_xxx() functions:

$rs = $DB->get_recordset(....) {
foreach ($rs as $record) {
    // Do whatever you want with this record
}
$rs->close();

Unlike get_record functions, you cannot check if $rs == true or !empty($rs) to determine if any records were found. Instead, if you need to, you can use:

if ($rs->valid()) {
    // The recordset contains some records.
}

get_recordset

Return a list of records as a moodle_recordset where all the given conditions are met.

$DB->get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_recordset_select

Return a list of records as a moodle_recordset where the given conditions are used in the WHERE clause.

$DB->get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)

get_recordset_sql

Return a list of records as an array of objects using a custom SELECT query.

$DB->get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)

get_recordset_list

Return a list of records as a moodle_recordset where the given field matches one of the possible values.

$DB->get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')

Delegated transactions

  • Please note some databases do not support transactions (such as the MyISAM MySQL database engine), however all server administrators are strongly encouraged to migrate to databases that support transactions (such as the InnoDB MySQL database engine).
  • Previous versions supported only one level of transaction. Since Moodle 2.0, the DML layer emulates delegated transactions that allow nesting of transactions.
  • Some subsystems (such as messaging) do not support transactions because it is not possible to rollback in external systems.

A transaction is started by:

$transaction = $DB->start_delegated_transaction();

and finished by:

$transaction->allow_commit();

Usually a transaction is rolled back when an exception is thrown:

$transaction->rollback($ex);

which must be used very carefully because it might break compatibility with databases that do not support transactions. Transactions cannot be used as part of expected code flow; they can be used only as an emergency protection of data consistency.

See more details in DB layer 2.0 delegated transactions or MDL-20625.

Example

global $DB;
try {
     $transaction = $DB->start_delegated_transaction();
     $DB->insert_record('foo', $object);
     $DB->insert_record('bar', $otherobject);
 
     // Assuming the both inserts work, we get to the following line.
     $transaction->allow_commit();
 
} catch(Exception $e) {
     $transaction->rollback($e);
}

Cross-DB compatibility

Moodle supports several SQL servers (MySQL, PostgreSQL, MS-SQL and Oracle). Each of them have some specific syntax in certain cases. In order to achieve cross-db compatibility of the code, following functions must be used to generate the fragments of the query valid for the actual SQL server.

sql_bitand

Return the SQL text to be used in order to perform a bitwise AND operation between 2 integers.

$DB->sql_bitand($int1, $int2)

sql_bitnot

Return the SQL text to be used in order to perform a bitwise NOT operation on the given integer.

$DB->sql_bitnot($int1)

sql_bitor

Return the SQL text to be used in order to perform a bitwise OR operation between 2 integers.

$DB->sql_bitor($int1, $int2)

sql_bitxor

Return the SQL text to be used in order to perform a bitwise XOR operation between 2 integers.

$DB->sql_bitxor($int1, $int2)

sql_null_from_clause

Return an empty FROM clause required by some DBs in all SELECT statements.

$DB->sql_null_from_clause()

sql_ceil

Return the correct CEIL expression applied to the given fieldname.

$DB->sql_ceil($fieldname)

sql_equal

Moodle 3.2 Return the query framgment to perform cross-db varchar comparisons when case-sensitiveness is important.

$DB->sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false)

sql_like

Return the query fragment to perform the LIKE comparison.

$DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')

Example: Searching for records partially matching the given hard-coded literal.

$DB->get_records_sql('SELECT id,fullname FROM {course} WHERE '.$DB->sql_like('idnumber', ':idnum'), ['idnum' => 'DEMO-%'));

See below if you need to compare with a value submitted by the user.

sql_like_escape

Escape the value submitted by the user so that it can be used for partial comparison and the special characters like '_' or '%' behave as literal characters, not wildcards.

$DB->sql_like_escape($text, $escapechar = '\\')

Example: If you need to perform a partial comparison with a value that has been submitted by the user.

$search = required_param('search', PARAM_RAW);
$DB->get_records_sql('SELECT id,fullname FROM {course} WHERE '.$DB->sql_like('fullname', ':fullname'), ['fullname' => '%'.$DB->sql_like_escape($search).'%']);

sql_length

Return the query fragment to be used to calculate the length of the expression in characters.

$DB->sql_length($fieldname)

sql_modulo

Return the query fragment to be used to calculate the remainder after division.

$DB->sql_modulo($int1, $int2)

sql_position

Return the query fragment for searching a string for the location of a substring. If both needle and haystack use placeholders, you must use named placeholders.

$DB->sql_position($needle, $haystack)

sql_substr

Return the query fragment for extracting a substring from the given expression.

$DB->sql_substr($expr, $start, $length=false)

sql_cast_char2int

Return the query fragment to cast a CHAR column to INTEGER

$DB->sql_cast_char2int($fieldname, $text=false)

sql_cast_char2real

Return the query fragment to cast a CHAR column to REAL (float) number

$DB->sql_cast_char2real($fieldname, $text=false)

sql_compare_text

Return the query fragment to be used when comparing a TEXT (clob) column with a given string or a VARCHAR field (some RDBMs do not allow for direct comparison).

$DB->sql_compare_text($fieldname, $numchars=32)

Example:

$todogroups = $DB->get_records_sql('SELECT id FROM {group} WHERE ' . $DB->sql_compare_text('description') . ' = ' . $DB->sql_compare_text(':description'), ['description' => 'TODO']);

sql_order_by_text

Return the query fragment to be used to get records ordered by a TEXT (clob) column. Note this affects the performance badly and should be avoided if possible.

$DB->sql_order_by_text($fieldname, $numchars=32)

sql_concat

Return the query fragment to concatenate all given paremeters into one string.

$DB->sql_concat(...)

sql_concat_join

Return the query fragment to concatenate all given elements into one string using the given separator.

$DB->sql_concat_join($separator="' '", $elements=array())

sql_fullname

Return the query fragment to concatenate the given $firstname and $lastname

$DB->sql_fullname($first='firstname', $last='lastname')

sql_isempty

Return the query fragment to check if the field is empty

$DB->sql_isempty($tablename, $fieldname, $nullablefield, $textfield)

sql_isnotempty

Return the query fragment to check if the field is not empty

$DB->sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield)

get_in_or_equal

Return the query fragment to check if a value is IN the given list of items (with a fallback to plain equal comparison if there is just one item)

$DB->get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false)

Example:

$statuses = ['todo', 'open', 'inprogress', 'intesting'];
list($insql, $inparams) = $DB->get_in_or_equal($statuses);
$sql = "SELECT * FROM {bugtracker_issues} WHERE status $insql";
$bugs = $DB->get_records_sql($sql, $inparams);

sql_regex_supported

Does the current database driver support regex syntax when searching?

$DB->sql_regex_supported()

sql_regex

Return the query fragment to perform a regex search.

$DB->sql_regex($positivematch = true, $casesensitive = false)

Example: Searching for Page module instances containing links.

if ($DB->sql_regex_supported()) {
    $select = 'content ' . $DB->sql_regex() . ' :pattern';
    $params = ['pattern' => "(src|data)\ *=\ *[\\\"\']https?://"]
} else {
    $select = $DB->sql_like('content', ':pattern', false);
    $params = ['pattern' => '%=%http%://%'];
}
 
$pages = $DB->get_records_select('page', $select, $params, 'course', 'id, course, name');

sql_intersect

Moodle 2.8 Return the query fragment that allows to find intersection of two or more queries

$DB->sql_intersect($selects, $fields)

Debugging

set_debug

You can enable a debugging mode to make $DB output the SQL of every executed query, along with some timing information. This can be useful when debugging your code. Obviously, all such calls should be removed before code is submitted for integration.

$DB->set_debug(true)

Special cases

get_course

From Moodle 2.5.1 onwards, you should use the get_course function instead of using get_record('course', ...) if you want to get a course record based on its ID, especially if there is a significant possibility that the course being retrieved is either the current course for the page, or the site course. Those two course records have probably already been loaded, and using this function will save a database query.

Additionally, the code is shorter and easier to read.

get_courses

If you want to get all the current courses in your Moodle, use get_courses() without parameter:

   $courses = get_courses();

See also