Database Access

Moodle 2 stores a handle to its database object in the global var $DB. Use $DB to perform all database related activities.

Take a look at lib/dml/moodle_database.php and lib/dml/mysqli_native_moodle_database.php to see what functions are available.

In the background, Moodle uses the ADOdb Database Abstraction Library, which is documented here. Please note that they also mention an extension which “provides up to 100% speedup by replacing parts of ADOdb with C code”.

Please note that Moodle 2 now also sanitizes all data before an insert or update (something you had to do yourself in Moodle versions ⇐ 1.9).

You can use the standard Moodle database abstraction layer to connect to a foreign database. Here's a simple example. Assumption: the foreign database is the same type (e.g. MySQL).

global $source_db, $DB, $CFG;

$db_class = get_class($DB);
$source_db = new $db_class(); // instantiate a new object of the same class as the original $DB
$source_db->connect($CFG->source_dbhost, $CFG->source_dbuser, $CFG->source_dbpass, $CFG->source_dbname, false);

Here, we've used the $CFG configuration object (from config.php) to store the settings for the foreign database.

Following this example, you can now access the foreign the database using the $source_db handle.

WARNING: In my current version of Moodle 2 (2.0.2, build 20110309), not all DB functions seem to work with an external database. Example: get_records_select does not seem to work properly. Fortunately, you can always revert to using sql all the way: get_records_sql.

Use $DB→execute($your_sql) to execute any arbitrary sql string. Well, the string cannot be entirely arbitrary SQL. Moodle uses 'parameters' in most DB related functions, which let you insert user derived data in a save way (to prevent sql injection attacks). For each and every method, $DB→fix_sql_params() is called. This means that even when you call $DB→execute without params (as a 2nd argument), the check is still performed. Here's the problem: $DB→fix_sql_params() is going to complain if you happen to have a “parameter” in your sql code. Example:

$sql = "INSERT INTO mdl_mod_myblog (label) VALUES ('Oops:this label inadvertently contains a parameter')";

Here, :this is seen as a parameter (presumably because the colon was not followed by a white space character). Now, if you try executing this sql, you'll encounter an exception:

$DB->execute($sql);

This yields: “ERROR: Incorrect number of query parameters. Expected 1, got 0.”

The only solution is to check your sql and replace all occurrences of a colon followed by a non-space character, with a colon and a white space character.

Generally speaking, this format seems to be the most robust:

if (! ($records = $DB->get_records_select("compass_elements",
                "category_id = ? AND label = ? ",
                array('category_id' => $category_id, 'label' => $label))) ) 
  return false;

In other words: don't use quotes around strings, do use question marks as place holders.