Data manipulation API: Difference between revisions
m (removed "===Getting an ") |
|||
Line 11: | Line 11: | ||
* The $DB global object is an instance of the moodle_database class, which is defined in [http://cvs.moodle.org/moodle/lib/dml/moodle_database.php?view=markup moodle_database.php] | * The $DB global object is an instance of the moodle_database class, which is defined in [http://cvs.moodle.org/moodle/lib/dml/moodle_database.php?view=markup moodle_database.php] | ||
* All the $table parameters in the functions are meant to be the table name ''without'' prefixes. | * All the $table parameters in the functions are meant to be the table name ''without'' prefixes. | ||
<code php>$user = $DB->get_record('user', array('id'=>'1');</code> | <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. | * 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> | <code php>$user = $DB->get_record_sql('SELECT * FROM {user} WHERE id = ?', array(1));</code> | ||
Line 56: | Line 56: | ||
</code> | </code> | ||
===Getting an | |||
The following methods return data as key/value pairs in an associative array. | The following methods return data as key/value pairs in an associative array. | ||
<code php> | <code php> | ||
Line 66: | Line 67: | ||
</code> | </code> | ||
===Seeing how many records match a given | ===Seeing how many records match a given criterion=== | ||
<code php> | <code php> | ||
o $DB->count_records($table, array $conditions=null) | o $DB->count_records($table, array $conditions=null) | ||
Line 73: | Line 74: | ||
/// Count the records in a table which match a particular WHERE clause. | /// Count the records in a table which match a particular WHERE clause. | ||
o $DB->count_records_sql($sql, array $params=null) | o $DB->count_records_sql($sql, array $params=null) | ||
/// Get the result of | /// Get the result of an SQL SELECT COUNT(...) query. | ||
</code> | </code> | ||
Line 94: | Line 95: | ||
///Get all records where foo = bar and jon = doe | ///Get all records where foo = bar and jon = doe | ||
$result = $DB->get_records($table,array('foo' => 'bar | $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 all records where foo = bar, but only return the fields foo,bar,jon,doe | ||
Line 102: | Line 103: | ||
=====moodle_database::get_records_select()===== | =====moodle_database::get_records_select()===== | ||
Get a number of records as an array of objects which match a particular WHERE clause. | 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. | ||
<code php> | <code php> | ||
///Get all records where jon = 'doe' and bob is not = 'tom' | ///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. | ///The 'select' parameter is (if not empty) is dropped directly into the WHERE clause without alteration. | ||
$table = 'foo'; | $table = 'foo'; | ||
$select = | $select = "jon = 'doe' AND bob <> 'tom'"; //is put into the where clause | ||
$result = $DB->get_records_select($table,$select); | $result = $DB->get_records_select($table,$select); | ||
</code> | </code> | ||
Line 115: | Line 116: | ||
<code php> | <code php> | ||
///Get all records from 'table' where foo = bar | ///Get all records from 'table' where foo = bar | ||
$result = $DB->get_records_sql('SELECT * FROM table WHERE foo = ?', array('bar')); | $result = $DB->get_records_sql('SELECT * FROM {table} WHERE foo = ?', array('bar')); | ||
///Get all records from 'table' where foo = 'bar' and bob = 'tom' | ///Get all records from 'table' where foo = 'bar' and bob = 'tom' | ||
///This is somewhat similar to how Drupal makes its queries | ///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' )); | $result = $DB->get_records_sql('SELECT * FROM {table} WHERE foo = ? AND bob = ?', array( 'bar' , 'tom' )); | ||
</code> | </code> | ||
Line 148: | Line 149: | ||
$table = 'foo'; ///name of table | $table = 'foo'; ///name of table | ||
$conditions = array('foo'=>'bar'); ///the name of the field (key) and the desired value | $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 | $fields = 'id, tacos'; ///list of fields to return | ||
$result = $DB->get_records_menu($table,$conditions)); | $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 | ||
</code> | </code> | ||
Line 170: | Line 172: | ||
$table = 'foo'; | $table = 'foo'; | ||
$select = 'jon = "doe" AND bob != "tom" '; //is put into the where clause | $select = 'jon = "doe" AND bob != "tom" '; //is put into the where clause | ||
$params = null; | |||
$fields = 'id, tacos';//return these fields | $fields = 'id, tacos';//return these fields | ||
$result = $DB->get_records_select_menu($table,$select, | $sort = 'id'; //field or fields you want to sort the result by | ||
$result = $DB->get_records_select_menu($table,$select,$params,$sort,$fields); | |||
</code> | </code> | ||
Line 308: | Line 312: | ||
o $DB->get_recordset_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') | 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. | /// 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> | |||
if ($rs->valid()) { | |||
// The recordset contains records. | |||
} | |||
</code> | </code> | ||
Line 371: | Line 385: | ||
/// Returns the correct CEIL expression applied to fieldname. | /// Returns the correct CEIL expression applied to fieldname. | ||
o $DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ') | o $DB->sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ') | ||
/// Returns the proper SQL to do LIKE. | /// Returns the proper SQL to do LIKE. For example: | ||
$DB->get_records_sql('SELECT ... WHERE '.$DB->sql_like('idnumber', ':idnum').' ... ', array( 'idnum' => 'foo')); | |||
o $DB->sql_length($fieldname) | o $DB->sql_length($fieldname) | ||
/// Returns the SQL text to be used to calculate the length in characters of one expression. | /// Returns the SQL text to be used to calculate the length in characters of one expression. | ||
Line 412: | Line 428: | ||
==See also== | ==See also== | ||
* [[Core APIs]] | |||
* [[DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong | * [[DML exceptions|DML exceptions]]: New DML code is throwing exceptions instead of returning false if anything goes wrong | ||
* [[DML drivers|DML drivers]]: Database drivers for new DML layer | * [[DML drivers|DML drivers]]: Database drivers for new DML layer | ||
Line 422: | Line 439: | ||
[[Category:DB]] | [[Category:DB]] | ||
[[Category:XMLDB]] | [[Category:XMLDB]] | ||
[[Category:API]] |
Revision as of 16:56, 7 January 2013
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!
Main info
Important note: All the functions shown on 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. For a detailed reference of changes, see the migration docs.
- 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:
global $DB;
- The $DB global object is an instance of the moodle_database class, which is defined in moodle_database.php
- All the $table parameters in the functions are meant to be the table name without prefixes.
$user = $DB->get_record('user', array('id'=>'1'));
- When using the xxx_sql() functions, table names must be enclosed between curly braces.
$user = $DB->get_record_sql('SELECT * FROM {user} WHERE id = ?', array(1));
- All the $conditions parameters in the functions are arrays of fieldname=>fieldvalue elements.
$user = $DB->get_record('user', array('firstname'=>'Martin', 'lastname'=>'Dougiamas'));
- 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.
/// 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'));
The functions
Getting a single record
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.
Getting an 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. 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!)
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_list($table, $field, array $values, $sort=, $fields='*', $limitfrom=, $limitnum=)
/// Get a number of records as an array of objects where one field match one list of values.
===Getting an
The following methods return data as key/value pairs in an associative array.
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.
Seeing how many records match a given criterion
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 an 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.
Examples
moodle_database::get_records()
Get a number of records as an array of objects where all the given conditions met.
///Get all records where foo = bar
$result = $DB->get_records($table,array('foo'=>'bar'));
///Get all records where foo = bar and jon = doe
$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
$result = $DB->get_records($table,array('foo'=>'bar'),null,'foo,bar,jon,doe');
///The previous example would cause data issues unless the 'foo' field happens to have unique values.
moodle_database::get_records_select()
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.
///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);
moodle_database::get_records_sql()
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.
///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'
///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' ));
moodle_database::get_records_list()
Get a number of records as an array of objects where one field match one list of values.
///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'
///Only returning the fields 'id', 'test' and 'taco'
$result = $DB->get_records_list($table, 'foo', array( 'bar', 'elephant', 'moodle'), null, 'id,test,taco');
Get the first two columns from a number of records as an associative array where all the given conditions met.
You can choose the two fields or leave the parameter blank and the method will return the first two columns of the table.
Returns an associative array.
///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));
///Get all records from table 'foo' where column 'foo' is equal to the value 'bar'
///Returning the values from the columns 'id' and 'tacos'
$table = 'foo'; ///name of table
$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
The result of this last example will look something like:
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
///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_menu($table,$select);
$table = 'foo';
$select = 'jon = "doe" AND bob != "tom" '; //is put into the where clause
$params = null;
$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);
The result of this last example will look something like:
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
Get the first two columns from a number of records as an associative array using a SQL statement.
///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);
///Get all records from table foo where bar = 6
$sql = 'SELECT id,tacos FROM foo WHERE bar = ?';
$params = array(6);
$result = $DB->get_records_sql_menu($sql,$params);
The result of this last example will look something like:
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
Getting a particular field value from one record
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.
Getting a particular field value from various records
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.
Setting a particular field in the database
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.
Deleting Records
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.
Inserting 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.
o $DB->insert_record($table, $dataobject, $returnid=true, $bulk=false)
/// Insert a record into a table and return the "id" field if required.
Example(s)
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
Updating Records
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.
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.
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).
Here is the 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(); // Don't forget to close the recordset!
And this is the list of available functions (100% paired with the get_records_xxx() above):
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.
Unlike get_record functions, you cannot use $rs == true or !empty($rs) to determine if any records were found. Recordsets implement the standard PHP Iterator interface (http://uk.php.net/manual/en/class.iterator.php)
So,
if ($rs->valid()) {
// The recordset contains records.
}
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.
- 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 is 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(s)
global $DB;
try {
$transaction = $DB->start_delegated_transaction();
// Insert a record
$DB->insert('foo', $object);
$DB->insert('bar', $otherobject);
// Assuming the both inserts work, we get to the following line.
$transaction->allow_commit();
} catch(Exception $e) {
$transaction->rollback($e);
}
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 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_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'));
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->sql_empty()
/// Returns the empty string char used by every supported DB.
See also
- 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