Data manipulation API

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's life 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

All the functions showed in this page are for use in Moodle 2.0 upwards, where we changed the 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 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:

   global $DB;

The functions

Seeing how many records match a given criteria

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.

Seeing if one record exists

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.

Retrieving a single record

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.

Getting an array of records

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.

Getting a particular field from the database

         o get_field($table, $return, $field1, $value1, $field2=, $value2=, $field3=, $value3=) 
           Get a single value from a table row where all the given fields match the given values.
         o get_field_select($table, $return, $select) 
           Get a single value from a table row where a particular select clause is true.
         o get_field_sql($sql) 
           Get a single value from a table.

     Setting a particular field in the database
         o set_field($table, $newfield, $newvalue, $field1, $value1, $field2=, $value2=, $field3=, $value3=) 
           Set a single field in every table row where all the given fields match the given values.

     Deleting Records
         o delete_records($table, $field1=, $value1=, $field2=, $value2=, $field3=, $value3=) 
           Delete the records from a table where all the given fields match the given values.
         o delete_records_select($table, $select=) 
           Delete one or more records from a table

     Inserting Records
         o insert_record($table, $dataobject, $returnid=true, $primarykey='id') 
           Insert a record into a table and return the "id" field if required

     Updating Records
         o update_record($table, $dataobject) 
           Update a record in a table

For insert_record and update_record (at least) $table is the table name without any prefix. This is added in dmllib.php. --Paul Ritchings 09:09, 10 June 2008 (CDT)

See also

• DDL functions: Where all the functions used to handle DB objects (DDL) are defined.
• DML functions - pre 2.0: (deprecated!) For information valid before Moodle 2.0.