Development:DML functions

2008-11-29T23:20:35Z

Simgrimm:

{{Moodle_2.0}}In this page you'll access to the available functions under Moodle to be able to access to DB data. You should use '''exclusively''' these functions in order to retrieve or modify DB contents because these functions provide an high level of abstraction and guarantee that your DB manipulation will work against different RDBMS.

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 info to all this documentation. It will be welcome, absolutely!

== Main info ==

'''Important note:''' All the functions showed in this page are for use in '''Moodle 2.0 upwards''', where we changed the [[Development:DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[Development:DML functions - pre 2.0|DML functions - pre 2.0]] page.

* All the function calls in 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:
<code php>global $DB;</code>
* All the $table parameters in the functions are mean to be the table name '''without'' prefixes.
<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 question mark and named placeholders can be used.
<code php>
/// Question mark placeholders:
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = ? AND lastname = ?',
array('Martin', 'Dougiamas'));

/// Named placeholders:
$DB->get_record_sql('SELECT * FROM {user} WHERE firstname = :firstname AND lastname = :lastname',
array('firstname'=>'Martin', 'lastname'=>'Dougiamas'));
</code>

== The functions ==

===Seeing how many records match a given criteria===
<code php>
o $DB->count_records($table, array $conditions=null)
/// 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 a SQL SELECT COUNT(...) query.
</code>

===Seeing if one record exists===
<code php>
o $DB->record_exists($table, array $conditions=null)
/// 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>

===Retrieving a single record===
<code php>
o $DB->get_record($table, array $conditions, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object where all the given conditions met.
o $DB->get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object which match a particular WHERE clause.
o $DB->get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false)
/// Get a single database record as an object using a SQL statement.
</code>

===Getting an array of records===
<code php>
o $DB->get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// Get a number of records as an array of objects where all the given conditions met.
o $DB->get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// 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)
/// Get a number of records as an array of objects using a SQL statement.

o $DB->get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0)
/// 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)
/// Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
o $DB->get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0)
/// Get the first two columns from a number of records as an associative array using a SQL statement.

o $DB->get_records_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='')
/// Get a number of records as an array of objects where one field match one list of values.
</code>

===Getting a particular field value from one record===
<code php>
o $DB->get_field($table, $return, array $conditions)
/// Get a single field value from a table record where all the given conditions met.
o $DB->get_field_select($table, $return, $select, array $params=null)
/// Get a single field value from a table record which match a particular WHERE clause.
o $DB->get_field_sql($sql, array $params=null)
/// Get a single field value (first field) using a SQL statement.
</code>

===Getting a particular field value from various records===

<code php>
o $DB->get_fieldset_select($table, $return, $select, array $params=null)
/// Selects records and return values of chosen field as an array which match a particular WHERE clause.
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===
<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===
<code php>
o $DB->delete_records($table, array $conditions=null)
/// 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===
<code php>
o $DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)
/// Insert a record into a table and return the "id" field if required.
</code>

===Updating Records===
<code php>
o $DB->update_record($table, $dataobject, $bulk=false)
/// Update a record in a table.
</code>

===Using Recordsets===

While the number of records to be retrieved from DB is high, the '''get_records_xxx()''' functions above are far from optimal, because they use to load all the records in memory at the same time. Under those circumstances, it's highly recommended to use this '''get_recordset_xxx()''' functions instead, using one nice mechanism to iterate over all the target records saving a lot of memory.

Only one thing is '''absolutely important'''. Don't forget to close the record sets after using them! (that will freed a lot of resources in the RDBMS).

Here it's the general way to iterate over records using the '''get_recordset_xxx()''' functions:

<code php>
if ($rs = $DB->get_recordset(....) {
foreach ($rs as $record) {
/// Do whatever you want with this record
}
$rs->close(); /// Don't forget to close the recordset!
}
</code>

And this is the list of available functions (100% paired with the get_records_xxx() above):
<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 match one list of values.
</code>

===Helper 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, having those "incompatibilities" fixed once and for ever.
<code php>
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_substr()
/// Returns the proper substr() function for each DB.
o $DB->sql_ilike()
/// Returns the proper SQL to do LIKE in a case-insensitive way.

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.
</code>

==See also==

* [[Development:DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong
* [[Development:DML drivers|DML drivers]]: Database drivers for new DML layer
* [[Development:DML functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[Development:DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[Development:DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[Development:DB layer 2.0 migration docs|DB layer 2.0 migration docs]]: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
* [[Development:DTL functions|DTL functions]]: Exporting, importing and moving of data stored in sql databases

[[Category:DB]]
[[Category:XMLDB]]

MoodleDocs - User contributions [en]

Development:DML functions