View source

<p class="note">'''Important note:''' This article contains information valid for versions before Moodle 2.0, so should be considered '''deprecated'''. If you want to see up to date information, please go to [[Development:DML functions|DML functions]] instead.</p>

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 ==

== The functions ==

=== Seeing if any records exist match a given criteria ===

          o record_exists($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') 
            Test whether a record exists in a table where all the given fields match the given values.
          o record_exists_select($table, $select='') 
            Test whether any records exists in a table which match a particular WHERE clause.
          o record_exists_sql($sql) 
            Test whether a SQL SELECT statement returns any records.

=== Seeing how many records match a given criteria ===

          o count_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') 
            Count the records in a table where all the given fields match the given values.
          o count_records_select($table, $select='', $countitem='COUNT(*)') 
            Count the records in a table which match a particular WHERE clause.
          o count_records_sql($sql) 
            Get the result of a SQL SELECT COUNT(...) query.

=== Retrieving a single record ===

          o get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='', $fields='*') 
            Get a single record as an object
          o get_record_select($table, $select='', $fields='*') 
            Gets one record from a table, as an object
          o get_record_sql($sql, $expectmultiple=false, $nolimit=false) 
            Get a single record as an object using an SQL statement


=== Getting an array of records ===

          o get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') 
            Get a number of records as an array of objects.
          o get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') 
            Get a number of records as an array of objects.
          o get_records_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') 
            Get a number of records as an array of objects.
          o get_records_sql($sql, $limitfrom='', $limitnum='') 
            Get a number of records as an array of objects.
          o get_records_menu($table, $field='', $value='', $sort='', $fields='*') 
            Get the first two columns from a number of records as an associative array.
          o get_records_select_menu($table, $select='', $sort='', $fields='*') 
            Get the first two columns from a number of records as an associative array.
          o get_records_sql_menu($sql) 
            Get the first two columns from a number of records as an associative array.


=== 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

==See also==

* [[Development:DDL functions]]: Up to date information about DDL functions.
* [[Development:DML functions]]: Up to date information about DML functions.
* [[Development:DDL functions - pre 2.0]]: '''(deprecated!)''' Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined .
* [[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.

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