David Mudrak (talk | contribs) mNo edit summary |
David Mudrak (talk | contribs) mNo edit summary |
||
Line 72: | Line 72: | ||
=== get_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> | <code php>$DB->get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING)</code> | ||
=== get_record_select === | === 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> | <code php>$DB->get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING)</code> | ||
=== get_record_sql === | === 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> | <code php>$DB->get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING)</code> | ||
Line 87: | Line 87: | ||
=== get_records === | === 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> | <code php>$DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | ||
=== get_records_select === | === get_records_select === | ||
Return a list of records as an array of objects where the given conditions are used in the WHERE clause. | |||
<code php>$DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | <code php>$DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | ||
=== get_records_sql === | === get_records_sql === | ||
Return a list of records as an array of objects using a custom SELECT query. | |||
<code php>$DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)</code> | <code php>$DB->get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)</code> | ||
=== get_records_list === | === get_records_list === | ||
Return a list of records as an array of objects where the given field matches one of the possible values. | |||
<code php>$DB->get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')</code> | <code php>$DB->get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom='', $limitnum='')</code> | ||
Line 105: | Line 105: | ||
=== get_records_menu === | === get_records_menu === | ||
Return the first two columns from a list of records as an associative array where all the given conditions are met. | |||
<code php>$DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | <code php>$DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | ||
=== get_records_select_menu === | === 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. | |||
<code php>$DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | <code php>$DB->get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)</code> | ||
=== get_records_sql_menu === | === get_records_sql_menu === | ||
Return the first two columns from a number of records as an associative array using a custom SELECT query. | |||
<code php>$DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)</code> | <code php>$DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)</code> | ||
Line 158: | Line 158: | ||
<code php>$DB->get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING)</code> | <code php>$DB->get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING)</code> | ||
== Getting field values from multiple records == | |||
<code php> | === get_fieldset_select === | ||
Return values of the given field as an array where the given conditions are used in the WHERE clause. | |||
<code php>$DB->get_fieldset_select($table, $return, $select, array $params=null)</code> | |||
=== get_fieldset_sql === | |||
</code> | 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 == | |||
=== 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> | |||
=== set_field_select === | |||
Set a single field in every table record where the given conditions are used in the WHERE clause. | |||
<code php>$DB->set_field_select($table, $newfield, $newvalue, $select, array $params=null)</code> | |||
== Deleting 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> | |||
=== delete_records_select === | |||
Delete records from the table where the given conditions are used in the WHERE clause. | |||
<code php>$DB->delete_records_select($table, $select, array $params=null)</code> | |||
=== | == Inserting records == | ||
=== | === insert_record == | ||
<code php> | Insert the given data object into the table and return the "id" of the newly created record. | ||
<code php>$DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)</code> | |||
</code> | |||
=== | === 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. | |||
<code php> | <code php>$DB->insert_records($table, $dataobjects)</code> | ||
</code> | |||
=== 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. | |||
<code php>$DB->update_record($table, $dataobject, $bulk=false)</code> | |||
$ | |||
</code> | |||
=== | == Executing a custom query == | ||
If you need to perform a | === 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! | |||
<code php>$DB->execute($sql, array $params=null)</code> | |||
== 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: | |||
<code php> | <code php> | ||
Line 266: | Line 227: | ||
// Do whatever you want with this record | // Do whatever you want with this record | ||
} | } | ||
$rs->close(); | $rs->close(); | ||
</code> | </code> | ||
Unlike get_record functions, you cannot | 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> | <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. | ||
* 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 it is not possible to rollback in external systems. | ||
Line 313: | Line 274: | ||
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 === | |||
<code php> | <code php> | ||
Line 319: | Line 280: | ||
try { | try { | ||
$transaction = $DB->start_delegated_transaction(); | $transaction = $DB->start_delegated_transaction(); | ||
$DB->insert_record('foo', $object); | $DB->insert_record('foo', $object); | ||
$DB->insert_record('bar', $otherobject); | $DB->insert_record('bar', $otherobject); | ||
Line 325: | Line 285: | ||
// 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); |
Revision as of 13:12, 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!
General concepts
Global DB
- 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 is prefix is NOT used in the code.
- 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.
$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 and named placeholders.
- 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
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)
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)
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);
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);
}
SQL compatibility functions
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.
o $DB->sql_bitand($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise AND
/// operation between 2 integers.
o $DB->sql_bitnot($int1)
/// Returns the SQL text to be used in order to perform one bitwise NOT
/// operation with 1 integer.
o $DB->sql_bitor($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise OR
/// operation between 2 integers.
o $DB->sql_bitxor($int1, $int2)
/// Returns the SQL text to be used in order to perform one bitwise XOR
/// operation between 2 integers.
o $DB->sql_null_from_clause()
/// Returns the FROM clause required by some DBs in all SELECT statements.
o $DB->sql_ceil($fieldname)
/// Returns the correct CEIL expression applied to fieldname.
o $DB->sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) (available since Moodle 3.2)
/// Returns the proper SQL to perform cross-db varchar comparisons when case-sensitiveness is mission-critical.
o $DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE. For example:
$DB->get_records_sql('SELECT ... WHERE '.$DB->sql_like('idnumber', ':idnum').' ... ', array( 'idnum' => '%foo%'));
/// 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
o $DB->sql_length($fieldname)
/// Returns the SQL text to be used to calculate the length in characters of one expression.
o $DB->sql_modulo($int1, $int2)
/// 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->get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false)
/// Constructs 'IN()' or '=' sql fragment
Debug fuctions
If you execute
$DB->set_debug(true)
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
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.
As another advantage, the code is shorter and easier to read.
Replace:
$course = $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST);
With:
$course = get_course($courseid);
get_courses
If you want to get all the current courses in your Moodle, use get_courses() without parameter:
$courses = get_courses();
See also
- SQL coding style
- Core APIs
- DML exceptions: New DML code is throwing exceptions instead of returning false if anything goes wrong
- DML drivers: Database drivers for new DML layer
- DML functions - pre 2.0: (deprecated!) For information valid before Moodle 2.0.
- DDL functions: Where all the functions used to handle DB objects (DDL) are defined.
- DB layer 2.0 examples: To see some code examples using various DML functions.
- DB layer 2.0 migration docs: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
- DTL functions: Exporting, importing and moving of data stored in SQL databases