MoodleDocs - User contributions [en]

Creating a web service and a web service function

2011-10-22T17:57:53Z

Fp2011: /* Define the web service description */ associative arrays vs objects, null values

{{Moodle_2.0}}

= Simple example =
Have a look to the [https://github.com/moodlehq/moodle-local_wstemplate web service template]. It is a simple hello_world function.

= Complex example=
We will write a web service function ''create_groups($groups)''. This function create a group into a Moodle course. - actually the function already exists into Moodle. You can browse [https://github.com/moodle/moodle/blob/master/group/externallib.php Github repository] to have a look to the latest code.

== Write the function specification documentation ==

=== functional ===
''create_groups($groups)'' will take a list of group object as parameters and it will return the same groups with their newly created id. If ever one group creation fails, the function will throw an exception, and no creation will happen.

=== technical ===
* '''the core functions''': for our case we will use ''groups_create_group()'' from [http://cvs.moodle.org/moodle/group/ /group/lib.php]. Of course not every core function exists and in some other cases you could have to write them.
* '''the parameter types''': for our case we'll send a list of object. This object are groups, with id/name/courseid.
* '''the returned value types''': for our case we want to send back a list of group object with their id.
* '''the user capabilities''': for our case the user will need the ''moodle/course:managegroups'' capability.

== Write a simple test client ==

Help yourself with [the demo clients https://github.com/moodlehq/sample-ws-clients]. It is essential to start to write the web service test client before the actual function. You could discover use cases that you didn't think of when writing the specifications. See also [[Creating_a_web_service_client]]

== Declare the web service function ==
Web service functions are declared into ''db/services.php'' of each component. In our case ''create_groups()'' is a core external function, so we will add the function to [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php].

<code php>
$functions = array(
'moodle_group_create_groups' => array( //web service function name
'classname' => 'moodle_group_external', //class containing the external function
'methodname' => 'create_groups', //external function name
'classpath' => 'group/externallib.php', //file containing the class/external function
'description' => 'Creates new groups.', //human readable description of the web service function
'type' => 'write', //database rights of the web service function (read, write)
),
);
</code>

Here you should ask yourself what is the difference between a web service function and an external function. Web service function could be considered as a redirection to the external function. The web service function name is the name that is served by web service server to the client. By this way the client doesn't know about the external functions.

The web service function name is arbitrary, but it must be globally unique so we highly recommend using the component name as prefix (and "moodle" for core functions).

== Define the web service description ==
Some web service protocol need a full description of the web service function parameters and return value. For example we need to generate a full WSDL for SOAP if we expect a JAVA/.Net client to call our function. This WSDL need a complete and fully detailed description of the parameters. 
Moreover we implemented a web service parameters validation process that also requires a full description of these parameters. 
In conclusion, you must write these descriptions. They are not applied to the web service function but the external function, remember that the web service function is just a kind of redirection. 

Descriptions are usually located in a class inside a file named ''externallib.php'' in the component folder. You probably remember that we referenced this ''moodle_group_external'' class into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]. Let's take a minute to talk about the [http://cvs.moodle.org/moodle/group/ /group/externallib.php] that we need to create.

This file will contain all external functions. They are all related to each other. In our case the externallib.php file will contain all ''group'' related functions. For each external function, we will have:
* the external function, calling a core function.
* a function describing the parameters of the external function
* a function describing the return values of the external function

Now we'll have look to the description of the ''create_group($groups)'' parameters. All parameter descriptions are included in a function called ''externalfunctionname_parameters()''. In our case it will be ''create_groups_parameters()'' :

<code php>
require_once("$CFG->libdir/externallib.php");

class moodle_group_external extends external_api {

/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
)
);
}
</code>

Ok you are probably already here :) We have to understand this function very well. There are different approaches to understand this function, here is my personal one. 
The function ''xxx_parameters()'' always returns an ''external_function_parameters'' object. This object is initialized with an associative array. This array contains the parameter descriptions. If the external function has no parameters, this array will be empty (but present). If the external function has three parameters, it will have three elements, the keys being the parameters names.
Each of its element is a parameter description. A parameter can be a list (external_multiple_structure), an object (external_single_structure) or a primary type (external_value).

Our create_groups() function expects one parameter named ''groups'', so we will first write:

<code php>
/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => ...

)
);
}
</code>

This ''groups'' parameter is a list of group. So we will write :

<code php>
'groups' => new external_multiple_structure(
...
)
</code>

An external_multiple_structure object (list) can be initialized with external_single_structure (object), external_value (primary type) or another external_multiple_structure (list). You will respectively obtain a list of a unique object type, a list of a unique primary type, or a list of a list. These last case being probably bad design. 
In our case we want a list of group, a group being a object. Have a look on how would describe a group :

<code php>
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
</code>

From this code you can guess that an external_single_structure is initialized with an associative array containing the object attibutes. 
Thus a list of group will be defined :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
)
</code>

Each group attribute is a primary type. ''courseid'' is an integer. ''name'' is a string, ''description'' is a string, and ''enrolmentkey'' is also a string. To define a primary type we use the ''external_value'' object. This ''external_value'' is initialized with a non associative array containing three elements:
# the primary type
# a human readable description
# is the parameter optional (if the parameter is mandatory we don't need this third element)

For example for the ''courseid'' and ''name'' attribut we would define:

<code php>
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
</code>

So our list of object description will be :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
</code>

Now you should be able to understand this parameter description function. You also should be able to deduce how to write the return value description function.

Return value function always returns one primary or complex value, so we don't need to return external_function_parameters object. In our case we want to return the exact same list of groups. The only change being that we want the group to have an id attribute.

<code php>
public static function create_groups_returns() {
return new external_multiple_structure(
new external_single_structure(
array(
'id' => new external_value(PARAM_INT, 'group record id'),
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
);
}
</code>

Note that ''external_single_structure'' describes an associative array. If your function returns an object, you must first convert it to an array.

The returned value cannot be null, and if it is an array, none of its values can be null. If your function returns for example a list of database records with $DB->get_records(), you must check each field of each row to replace null values, and convert each row to an associative array.

Here ends the most difficult part of the web service infrastructure implementation. Congratulation!

=== Mandatory, Optional or Default ===
An attribute can be defined MANDATORY, OPTIONAL, or DEFAULT.

<code php>
'yearofstudy' => new external_value(PARAM_INT, 'year of study',VALUE_DEFAULT, 1979),
</code>

The third parameter VALUE_DEFAULT indicates that 'yearofstudy' is set to a default value if the attribute is not supply. When you set VALUE_DEFAULT in the description, you must also write a default value as fourth parameter. Here the default value of 'yearofstudy' is 1979.

The three values can be understood like this :
* VALUE_REQUIRED - if the parameter is not supplied, then the server throws an exception
* VALUE_OPTIONAL - if the parameter is not supplied, then the param has no value
* VALUE_DEFAULT - if the parameter is not supplied, then the default value is used

== Define the external function ==
We declared our web service function and we defined the external function parameters and return values. We will now implement the external function. Let's have a full look to the external function.

<code php>
/**
* Create groups
* @param array $groups array of group description arrays (with keys groupname and courseid)
* @return array of newly created groups
*/
public static function create_groups($groups) { //Don't forget to set it as static
global $CFG, $DB;
require_once("$CFG->dirroot/group/lib.php");

$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));

$transaction = $DB->start_delegated_transaction();

$groups = array();

foreach ($params['groups'] as $group) {
$group = (object)$group;

if (trim($group->name) == '') {
throw new invalid_parameter_exception('Invalid group name');
}
if ($DB->get_record('groups', array('courseid'=>$group->courseid, 'name'=>$group->name))) {
throw new invalid_parameter_exception('Group with the same name already exists in the course');
}

// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);

// finally create the group
$group->id = groups_create_group($group, false);
$groups[] = (array)$group;
}

$transaction->allow_commit();

return $groups;
}
</code>

First let's have a look at
<code php>
$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));
</code>
This ''validate_parameters'' function validates the external function parameters against the description. It will return an exception if some required parameters are missing, if parameters are not well-formed, and check the parameters validity. The array('groups'=>$groups) take the expected parameters by the description function.

A very important point: the parameters of your external function (''$groups'' into ''public static function create_groups('''$groups''') {'') should be in the exact same order as the ones into your description function ('''return new external_function_parameters( array( ''''groups'''' => ... )'''). I agree that our example function doesn't give us a good example :)

Then we have the following line
<code php>
$transaction = $DB->start_delegated_transaction();
</code>
Our function has for goal to create multiple group. The function is calling multiple time the '''groups_create_group()''' core function. If ever one function fail and return an exception, we do not want to commit any change. It's why we declare this line that suspend any commit till we write the following line
<code php>
$transaction->allow_commit();
</code>

You will also notice that our function throws exception. It is automatically handle by Moodle web service servers.
<code php>
throw new invalid_parameter_exception('Group with the same name already exists in the course');
</code>

And yet again another important point: the context checking and capability checking happens into the external function. In a long term, in Moodle 2.0, all context/capability checks will disappear from the core functions and will be located into the external functions
<code php>
/// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);
</code>

Last point, the return values will be parsed by the Moodle web service servers. Only the required values describe into the function description will be returned. An error will be returned if the external function doesn't return some required values.

== Add the web service function into a service ==
In some specific case we could want to set a service by default, so the Moodle administrator doesn't need to set a new one. We can do that by writing the following code into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]:

<code php>
$services = array(
'groupservice' => array( //the name of the web service
'functions' => array ('moodle_add_groups', 'moodle_create_groups'), //web service functions of this service
'requiredcapability' => 'some/capability:specified', //if set, the web service user need this capability to access
//any function of this service
'restrictedusers' = >1, //if enabled, the Moodle administrator must link some user to this service
//into the administration
'enabled'=>0, //if enabled, the service can be reachable on a default installation
)
);
</code>

In this code we created a service called ''groupservice''. This service contains two functions ''moodle_add_groups''/''moodle_create_groups''. 
This possibility to preset a service exists to facilitate the administrator life. He can easily enable a bunch of function. 
However it is not possible for an administrator to add/remove any function from preset service. For security purpose, it is not a recommended to preset a service.

==See also==
* [[Web services]]
* [[External services security]]
* [[External services description]]
* [[Creating_a_web_service_client]]
* [https://docs.moodle.org/en/Web_Services Web services]
* [http://moodle.org/mod/forum/discuss.php?d=169078 Using Webservices with Zend Rest Client]

[[Category:Web Services]]

Creating a web service and a web service function

2011-10-22T11:41:20Z

Fp2011: /* Define the web service description */

{{Moodle_2.0}}

= Simple example =
Have a look to the [https://github.com/moodlehq/moodle-local_wstemplate web service template]. It is a simple hello_world function.

= Complex example=
We will write a web service function ''create_groups($groups)''. This function create a group into a Moodle course. - actually the function already exists into Moodle. You can browse [https://github.com/moodle/moodle/blob/master/group/externallib.php Github repository] to have a look to the latest code.

== Write the function specification documentation ==

=== functional ===
''create_groups($groups)'' will take a list of group object as parameters and it will return the same groups with their newly created id. If ever one group creation fails, the function will throw an exception, and no creation will happen.

=== technical ===
* '''the core functions''': for our case we will use ''groups_create_group()'' from [http://cvs.moodle.org/moodle/group/ /group/lib.php]. Of course not every core function exists and in some other cases you could have to write them.
* '''the parameter types''': for our case we'll send a list of object. This object are groups, with id/name/courseid.
* '''the returned value types''': for our case we want to send back a list of group object with their id.
* '''the user capabilities''': for our case the user will need the ''moodle/course:managegroups'' capability.

== Write a simple test client ==

Help yourself with [the demo clients https://github.com/moodlehq/sample-ws-clients]. It is essential to start to write the web service test client before the actual function. You could discover use cases that you didn't think of when writing the specifications. See also [[Creating_a_web_service_client]]

== Declare the web service function ==
Web service functions are declared into ''db/services.php'' of each component. In our case ''create_groups()'' is a core external function, so we will add the function to [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php].

<code php>
$functions = array(
'moodle_group_create_groups' => array( //web service function name
'classname' => 'moodle_group_external', //class containing the external function
'methodname' => 'create_groups', //external function name
'classpath' => 'group/externallib.php', //file containing the class/external function
'description' => 'Creates new groups.', //human readable description of the web service function
'type' => 'write', //database rights of the web service function (read, write)
),
);
</code>

Here you should ask yourself what is the difference between a web service function and an external function. Web service function could be considered as a redirection to the external function. The web service function name is the name that is served by web service server to the client. By this way the client doesn't know about the external functions.

The web service function name is arbitrary, but it must be globally unique so we highly recommend using the component name as prefix (and "moodle" for core functions).

== Define the web service description ==
Some web service protocol need a full description of the web service function parameters and return value. For example we need to generate a full WSDL for SOAP if we expect a JAVA/.Net client to call our function. This WSDL need a complete and fully detailed description of the parameters. 
Moreover we implemented a web service parameters validation process that also requires a full description of these parameters. 
In conclusion, you must write these descriptions. They are not applied to the web service function but the external function, remember that the web service function is just a kind of redirection. 

Descriptions are usually located in a class inside a file named ''externallib.php'' in the component folder. You probably remember that we referenced this ''moodle_group_external'' class into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]. Let's take a minute to talk about the [http://cvs.moodle.org/moodle/group/ /group/externallib.php] that we need to create.

This file will contain all external functions. They are all related to each other. In our case the externallib.php file will contain all ''group'' related functions. For each external function, we will have:
* the external function, calling a core function.
* a function describing the parameters of the external function
* a function describing the return values of the external function

Now we'll have look to the description of the ''create_group($groups)'' parameters. All parameter descriptions are included in a function called ''externalfunctionname_parameters()''. In our case it will be ''create_groups_parameters()'' :

<code php>
require_once("$CFG->libdir/externallib.php");

class moodle_group_external extends external_api {

/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
)
);
}
</code>

Ok you are probably already here :) We have to understand this function very well. There are different approaches to understand this function, here is my personal one. 
The function ''xxx_parameters()'' always returns an ''external_function_parameters'' object. This object is initialized with an associative array. This array contains the parameter descriptions. If the external function has no parameters, this array will be empty (but present). If the external function has three parameters, it will have three elements, the keys being the parameters names.
Each of its element is a parameter description. A parameter can be a list (external_multiple_structure), an object (external_single_structure) or a primary type (external_value).

Our create_groups() function expects one parameter named ''groups'', so we will first write:

<code php>
/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => ...

)
);
}
</code>

This ''groups'' parameter is a list of group. So we will write :

<code php>
'groups' => new external_multiple_structure(
...
)
</code>

An external_multiple_structure object (list) can be initialized with external_single_structure (object), external_value (primary type) or another external_multiple_structure (list). You will respectively obtain a list of a unique object type, a list of a unique primary type, or a list of a list. These last case being probably bad design. 
In our case we want a list of group, a group being a object. Have a look on how would describe a group :

<code php>
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
</code>

From this code you can guess that an external_single_structure is initialized with an associative array containing the object attibutes. 
Thus a list of group will be defined :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
)
</code>

Each group attribute is a primary type. ''courseid'' is an integer. ''name'' is a string, ''description'' is a string, and ''enrolmentkey'' is also a string. To define a primary type we use the ''external_value'' object. This ''external_value'' is initialized with a non associative array containing three elements:
# the primary type
# a human readable description
# is the parameter optional (if the parameter is mandatory we don't need this third element)

For example for the ''courseid'' and ''name'' attribut we would define:

<code php>
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
</code>

So our list of object description will be :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
</code>

Now you should be able to understand this parameter description function. You also should be able to deduce how to write the return value description function.

Return value function always returns one primary or complex value, so we don't need to return external_function_parameters object. In our case we want to return the exact same list of groups. The only change being that we want the group to have an id attribute.

<code php>
public static function create_groups_returns() {
return new external_multiple_structure(
new external_single_structure(
array(
'id' => new external_value(PARAM_INT, 'group record id'),
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
);
}
</code>

Here ends the most difficult part of the web service infrastructure implementation. Congratulation!

=== Mandatory, Optional or Default ===
An attribute can be defined MANDATORY, OPTIONAL, or DEFAULT.

<code php>
'yearofstudy' => new external_value(PARAM_INT, 'year of study',VALUE_DEFAULT, 1979),
</code>

The third parameter VALUE_DEFAULT indicates that 'yearofstudy' is set to a default value if the attribute is not supply. When you set VALUE_DEFAULT in the description, you must also write a default value as fourth parameter. Here the default value of 'yearofstudy' is 1979.

The three values can be understood like this :
* VALUE_REQUIRED - if the parameter is not supplied, then the server throws an exception
* VALUE_OPTIONAL - if the parameter is not supplied, then the param has no value
* VALUE_DEFAULT - if the parameter is not supplied, then the default value is used

== Define the external function ==
We declared our web service function and we defined the external function parameters and return values. We will now implement the external function. Let's have a full look to the external function.

<code php>
/**
* Create groups
* @param array $groups array of group description arrays (with keys groupname and courseid)
* @return array of newly created groups
*/
public static function create_groups($groups) { //Don't forget to set it as static
global $CFG, $DB;
require_once("$CFG->dirroot/group/lib.php");

$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));

$transaction = $DB->start_delegated_transaction();

$groups = array();

foreach ($params['groups'] as $group) {
$group = (object)$group;

if (trim($group->name) == '') {
throw new invalid_parameter_exception('Invalid group name');
}
if ($DB->get_record('groups', array('courseid'=>$group->courseid, 'name'=>$group->name))) {
throw new invalid_parameter_exception('Group with the same name already exists in the course');
}

// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);

// finally create the group
$group->id = groups_create_group($group, false);
$groups[] = (array)$group;
}

$transaction->allow_commit();

return $groups;
}
</code>

First let's have a look at
<code php>
$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));
</code>
This ''validate_parameters'' function validates the external function parameters against the description. It will return an exception if some required parameters are missing, if parameters are not well-formed, and check the parameters validity. The array('groups'=>$groups) take the expected parameters by the description function.

A very important point: the parameters of your external function (''$groups'' into ''public static function create_groups('''$groups''') {'') should be in the exact same order as the ones into your description function ('''return new external_function_parameters( array( ''''groups'''' => ... )'''). I agree that our example function doesn't give us a good example :)

Then we have the following line
<code php>
$transaction = $DB->start_delegated_transaction();
</code>
Our function has for goal to create multiple group. The function is calling multiple time the '''groups_create_group()''' core function. If ever one function fail and return an exception, we do not want to commit any change. It's why we declare this line that suspend any commit till we write the following line
<code php>
$transaction->allow_commit();
</code>

You will also notice that our function throws exception. It is automatically handle by Moodle web service servers.
<code php>
throw new invalid_parameter_exception('Group with the same name already exists in the course');
</code>

And yet again another important point: the context checking and capability checking happens into the external function. In a long term, in Moodle 2.0, all context/capability checks will disappear from the core functions and will be located into the external functions
<code php>
/// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);
</code>

Last point, the return values will be parsed by the Moodle web service servers. Only the required values describe into the function description will be returned. An error will be returned if the external function doesn't return some required values.

== Add the web service function into a service ==
In some specific case we could want to set a service by default, so the Moodle administrator doesn't need to set a new one. We can do that by writing the following code into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]:

<code php>
$services = array(
'groupservice' => array( //the name of the web service
'functions' => array ('moodle_add_groups', 'moodle_create_groups'), //web service functions of this service
'requiredcapability' => 'some/capability:specified', //if set, the web service user need this capability to access
//any function of this service
'restrictedusers' = >1, //if enabled, the Moodle administrator must link some user to this service
//into the administration
'enabled'=>0, //if enabled, the service can be reachable on a default installation
)
);
</code>

In this code we created a service called ''groupservice''. This service contains two functions ''moodle_add_groups''/''moodle_create_groups''. 
This possibility to preset a service exists to facilitate the administrator life. He can easily enable a bunch of function. 
However it is not possible for an administrator to add/remove any function from preset service. For security purpose, it is not a recommended to preset a service.

==See also==
* [[Web services]]
* [[External services security]]
* [[External services description]]
* [[Creating_a_web_service_client]]
* [https://docs.moodle.org/en/Web_Services Web services]
* [http://moodle.org/mod/forum/discuss.php?d=169078 Using Webservices with Zend Rest Client]

[[Category:Web Services]]

Creating a web service and a web service function

2011-10-13T21:40:24Z

Fp2011: /* See also */

{{Moodle_2.0}}

= Simple example =
Have a look to the [https://github.com/moodlehq/moodle-local_wstemplate web service template]. It is a simple hello_world function.

= Complex example=
We will write a web service function ''create_groups($groups)''. This function create a group into a Moodle course. - actually the function already exists into Moodle. You can browse [https://github.com/moodle/moodle/blob/master/group/externallib.php Github repository] to have a look to the latest code.

== Write the function specification documentation ==

=== functional ===
''create_groups($groups)'' will take a list of group object as parameters and it will return the same groups with their newly created id. If ever one group creation fails, the function will throw an exception, and no creation will happen.

=== technical ===
* '''the core functions''': for our case we will use ''groups_create_group()'' from [http://cvs.moodle.org/moodle/group/ /group/lib.php]. Of course not every core function exists and in some other cases you could have to write them.
* '''the parameter types''': for our case we'll send a list of object. This object are groups, with id/name/courseid.
* '''the returned value types''': for our case we want to send back a list of group object with their id.
* '''the user capabilities''': for our case the user will need the ''moodle/course:managegroups'' capability.

== Write a simple test client ==

Help yourself with [the demo clients https://github.com/moodlehq/sample-ws-clients]. It is essential to start to write the web service test client before the actual function. You could discover use cases that you didn't think of when writing the specifications. See also [[Creating_a_web_service_client]]

== Declare the web service function ==
Web service functions are declared into ''db/services.php'' of each component. In our case ''create_groups()'' is a core external function, so we will add the function to [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php].

<code php>
$functions = array(
'moodle_group_create_groups' => array( //web service function name
'classname' => 'moodle_group_external', //class containing the external function
'methodname' => 'create_groups', //external function name
'classpath' => 'group/externallib.php', //file containing the class/external function
'description' => 'Creates new groups.', //human readable description of the web service function
'type' => 'write', //database rights of the web service function (read, write)
),
);
</code>

Here you should ask yourself what is the difference between a web service function and an external function. Web service function could be considered as a redirection to the external function. The web service function name is the name that is served by web service server to the client. By this way the client doesn't know about the external functions.

The web service function name is arbitrary, but it must be globally unique so we highly recommend using the component name as prefix (and "moodle" for core functions).

== Define the web service description ==
Some web service protocol need a full description of the web service function parameters and return value. For example we need to generate a full WSDL for SOAP if we expect a JAVA/.Net client to call our function. This WSDL need a complete and fully detailed description of the parameters. 
Moreover we implemented a web service parameters validation process that also require a full description of these parameters. 
In conclusion, you must write these descriptions. These descriptions are not applied on the web service function but the external function, remember that the web service function is just a kind of redirection. 

Descriptions are usually located into a class into an externallib.php in the component folder. You probably remember that we referenced this ''moodle_group_external'' class into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]. Let's take a minuteto talk about the [http://cvs.moodle.org/moodle/group/ /group/externallib.php] that we need to create.

This file will contain all external functions. These functions are all related together. In our case the externallib.php file will contain all ''group'' related functions. For each external function, we will have:
* the external function, calling a core function.
* a function describing the parameters of the external function
* a function describing the return values of the external function

Now we'll have look to the description of the ''create_group($groups)'' parameters. All parameter descriptions are included into a function called ''externalfunctionname_parameters()''. In our case it will be ''create_groups_parameters()'' :

<code php>
require_once("$CFG->libdir/externallib.php");

class moodle_group_external extends external_api {

/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
)
);
}
</code>

Ok you are probably already here :) We have to understand this function very well. There is different approaches to understand this function, here is my personal one. 
The function ''xxx_parameters()'' always return an ''external_function_parameters'' object. This object is initialized with an associative array. This array contains the parameter descriptions. If the external function have three parameters, this array will have three elements, the keys being the paramters name.
Each of its element is a parameter description. A parameter can be a list (external_multiple_structure), an object (external_single_structure) or a primary type (external_value).

Our create_groups() function expect one parameter named ''groups'', so we will first write:

<code php>
/**
* Returns description of method parameters
* @return external_function_parameters
*/
public static function create_groups_parameters() {
return new external_function_parameters(
array(
'groups' => ...

)
);
}
</code>

This ''groups'' parameter is a list of group. So we will write :

<code php>
'groups' => new external_multiple_structure(
...
)
</code>

An external_multiple_structure object (list) can be initialized with external_single_structure (object), external_value (primary type) or another external_multiple_structure (list). You will respectively obtain a list of a unique object type, a list of a unique primary type, or a list of a list. These last case being probably bad design. 
In our case we want a list of group, a group being a object. Have a look on how would describe a group :

<code php>
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
</code>

From this code you can guess that a external_single_structure is initialize with an associative array. This array containing the object attibuts. 
Thus a list of group will be defined :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => ...,
'name' => ...,
'description' => ...,
'enrolmentkey' => ...,
)
)
)
</code>

Every of the group attribut are primary type. ''courseid'' is an integer. ''name'' is a string, ''description'' is a string, and ''enrolmentkey'' is also a string. To define a primary type we use the ''external_value'' object. This ''external_value'' is initialize with a non associative array containing three elements:
# the primary type
# a human readable description
# is the parameter optional (if the parameter is mandatory we don't need this third element)

For example for the ''courseid'' and ''name'' attribut we would define:

<code php>
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
</code>

So our list of object description will be :

<code php>
'groups' => new external_multiple_structure(
new external_single_structure(
array(
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
)
</code>

Now you should be able to understand this parameter description function. You also should be able to deduce how to write the return value description function.

Return value function always return one primary or complex value, so we don't need to return external_function_parameters object. In our case we want to return the exact same list of groups. The only change being that we want the group to have an id attribut.

<code php>
public static function create_groups_returns() {
return new external_multiple_structure(
new external_single_structure(
array(
'id' => new external_value(PARAM_INT, 'group record id'),
'courseid' => new external_value(PARAM_INT, 'id of course'),
'name' => new external_value(PARAM_TEXT, 'multilang compatible name, course unique'),
'description' => new external_value(PARAM_RAW, 'group description text'),
'enrolmentkey' => new external_value(PARAM_RAW, 'group enrol secret phrase'),
)
)
);
}
</code>

Here ends the most difficult part of the web service infrastructure implementation. Congratulation!

=== Mandatory, Optional or Default ===
An attribut can be defined MANDATORY, OPTIONAL, or DEFAULT.

<code php>
'yearofstudy' => new external_value(PARAM_INT, 'year of study',VALUE_DEFAULT, 1979),
</code>

The third parameter VALUE_DEFAULT indicates that 'yearofstudy' is set to a default value if the attribut is not supply. When you set VALUE_DEFAULT in the description, you must also write a default value as fourth parameter. Here the default value of 'yearofstudy' is 1979.

The three values can be understood like it:
* VALUE_REQUIRED - if the parameter is not supplied, then the server throw an exception
* VALUE_OPTIONAL - if the parameter is not supplied, then the param has no value
* VALUE_DEFAULT - if the parameter is not supplied, then the default value is used

== Define the external function ==
We declared our web service function and we defined the external function parameters and return values. We will now implement the external function. Let's have a full look to the external function.

<code php>
/**
* Create groups
* @param array $groups array of group description arrays (with keys groupname and courseid)
* @return array of newly created groups
*/
public static function create_groups($groups) { //Don't forget to set it as static
global $CFG, $DB;
require_once("$CFG->dirroot/group/lib.php");

$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));

$transaction = $DB->start_delegated_transaction();

$groups = array();

foreach ($params['groups'] as $group) {
$group = (object)$group;

if (trim($group->name) == '') {
throw new invalid_parameter_exception('Invalid group name');
}
if ($DB->get_record('groups', array('courseid'=>$group->courseid, 'name'=>$group->name))) {
throw new invalid_parameter_exception('Group with the same name already exists in the course');
}

// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);

// finally create the group
$group->id = groups_create_group($group, false);
$groups[] = (array)$group;
}

$transaction->allow_commit();

return $groups;
}
</code>

First let's have a look at
<code php>
$params = self::validate_parameters(self::create_groups_parameters(), array('groups'=>$groups));
</code>
This ''validate_parameters'' function validates the external function parameters against the description. It will return an exception if some required parameters are missing, if parameters are not well-formed, and check the parameters validity. The array('groups'=>$groups) take the expected parameters by the description function.

A very important point: the parameters of your external function (''$groups'' into ''public static function create_groups('''$groups''') {'') should be in the exact same order as the ones into your description function ('''return new external_function_parameters( array( ''''groups'''' => ... )'''). I agree that our example function doesn't give us a good example :)

Then we have the following line
<code php>
$transaction = $DB->start_delegated_transaction();
</code>
Our function has for goal to create multiple group. The function is calling multiple time the '''groups_create_group()''' core function. If ever one function fail and return an exception, we do not want to commit any change. It's why we declare this line that suspend any commit till we write the following line
<code php>
$transaction->allow_commit();
</code>

You will also notice that our function throws exception. It is automatically handle by Moodle web service servers.
<code php>
throw new invalid_parameter_exception('Group with the same name already exists in the course');
</code>

And yet again another important point: the context checking and capability checking happens into the external function. In a long term, in Moodle 2.0, all context/capability checks will disappear from the core functions and will be located into the external functions
<code php>
/// now security checks
$context = get_context_instance(CONTEXT_COURSE, $group->courseid);
self::validate_context($context);
require_capability('moodle/course:managegroups', $context);
</code>

Last point, the return values will be parsed by the Moodle web service servers. Only the required values describe into the function description will be returned. An error will be returned if the external function doesn't return some required values.

== Add the web service function into a service ==
In some specific case we could want to set a service by default, so the Moodle administrator doesn't need to set a new one. We can do that by writing the following code into [http://cvs.moodle.org/moodle/lib/db/ /lib/db/services.php]:

<code php>
$services = array(
'groupservice' => array( //the name of the web service
'functions' => array ('moodle_add_groups', 'moodle_create_groups'), //web service functions of this service
'requiredcapability' => 'some/capability:specified', //if set, the web service user need this capability to access
//any function of this service
'restrictedusers' = >1, //if enabled, the Moodle administrator must link some user to this service
//into the administration
'enabled'=>0, //if enabled, the service can be reachable on a default installation
)
);
</code>

In this code we created a service called ''groupservice''. This service contains two functions ''moodle_add_groups''/''moodle_create_groups''. 
This possibility to preset a service exists to facilitate the administrator life. He can easily enable a bunch of function. 
However it is not possible for an administrator to add/remove any function from preset service. For security purpose, it is not a recommended to preset a service.

==See also==
* [[Web services]]
* [[External services security]]
* [[External services description]]
* [[Creating_a_web_service_client]]
* [https://docs.moodle.org/en/Web_Services Web services]
* [http://moodle.org/mod/forum/discuss.php?d=169078 Using Webservices with Zend Rest Client]

[[Category:Web Services]]

Data manipulation API

2011-10-02T19:28:15Z

Fp2011: /* Getting a particular field value from one record */

{{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 2.0|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|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|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:
<code php>global $DB;</code>
* 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.
<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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting a single record===

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

===Getting an 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!)
<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_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.
</code>

The following methods return data as key/value pairs in an associative array.
<code php>
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.
</code>
====Examples====
=====moodle_database::get_records()=====
Get a number of records as an array of objects where all the given conditions met.
<code php>
///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 AND' , '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.
</code>

=====moodle_database::get_records_select()=====
Get a number of records as an array of objects which match a particular WHERE clause.
<code php>
///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);
</code>

=====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.
<code php>
///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' ));
</code>

=====moodle_database::get_records_list()=====
Get a number of records as an array of objects where one field match one list of values.
<code php>
///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');
</code>

=====moodle_database::get_records_menu()=====
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.
<code php>
///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
$fields = 'id, tacos'; ///list of fields to return

$result = $DB->get_records_menu($table,$conditions));

</code>
The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_select_menu()=====
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
<code php>
///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
$fields = 'id, tacos';//return these fields
$result = $DB->get_records_select_menu($table,$select,null,$fields);
</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_sql_menu()=====
Get the first two columns from a number of records as an associative array using a SQL statement.
<code php>
///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);

</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

===Getting a particular field value from one record===
<code php>
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.
</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===
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.
<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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
</code>

<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
</code>

===Updating Records===
<code php>
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.
</code>

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

<code php>
$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 matches one list of values.
</code>

===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:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> 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)====

<code php>
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);
}
</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 and have 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_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE.
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.
</code>

==See also==

* [[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 functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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

Data manipulation API

2011-09-30T19:20:13Z

Fp2011: /* Updating Records */

{{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 2.0|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|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|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:
<code php>global $DB;</code>
* 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.
<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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting a single record===

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

===Getting an 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!)
<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_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.
</code>

The following methods return data as key/value pairs in an associative array.
<code php>
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.
</code>
====Examples====
=====moodle_database::get_records()=====
Get a number of records as an array of objects where all the given conditions met.
<code php>
///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 AND' , '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.
</code>

=====moodle_database::get_records_select()=====
Get a number of records as an array of objects which match a particular WHERE clause.
<code php>
///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);
</code>

=====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.
<code php>
///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' ));
</code>

=====moodle_database::get_records_list()=====
Get a number of records as an array of objects where one field match one list of values.
<code php>
///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');
</code>

=====moodle_database::get_records_menu()=====
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.
<code php>
///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
$fields = 'id, tacos'; ///list of fields to return

$result = $DB->get_records_menu($table,$conditions));

</code>
The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_select_menu()=====
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
<code php>
///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
$fields = 'id, tacos';//return these fields
$result = $DB->get_records_select_menu($table,$select,null,$fields);
</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_sql_menu()=====
Get the first two columns from a number of records as an associative array using a SQL statement.
<code php>
///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);

</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</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===
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.
<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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
</code>

<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
</code>

===Updating Records===
<code php>
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.
</code>

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

<code php>
$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 matches one list of values.
</code>

===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:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> 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)====

<code php>
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);
}
</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 and have 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_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE.
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.
</code>

==See also==

* [[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 functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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

Data manipulation API

2011-09-28T12:10:08Z

Fp2011: /* moodle_database::get_records_list() */

{{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 2.0|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|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|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:
<code php>global $DB;</code>
* 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.
<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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting a single record===

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

===Getting an 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!)
<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_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.
</code>

The following methods return data as key/value pairs in an associative array.
<code php>
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.
</code>
====Examples====
=====moodle_database::get_records()=====
Get a number of records as an array of objects where all the given conditions met.
<code php>
///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 AND' , '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.
</code>

=====moodle_database::get_records_select()=====
Get a number of records as an array of objects which match a particular WHERE clause.
<code php>
///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);
</code>

=====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.
<code php>
///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' ));
</code>

=====moodle_database::get_records_list()=====
Get a number of records as an array of objects where one field match one list of values.
<code php>
///Get all records where the values('bar',1,'elephant','moodle') are found in the field 'foo'
$result = $DB->get_records_list($table, 'foo', array( 'bar', 1, 'elephant', 'moodle'));

///Get all records where the values('bar',1,'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', 1, 'elephant', 'moodle'), null, 'id,test,taco');
</code>

=====moodle_database::get_records_menu()=====
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.
<code php>
///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
$fields = 'id, tacos'; ///list of fields to return

$result = $DB->get_records_menu($table,$conditions));

</code>
The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_select_menu()=====
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
<code php>
///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
$fields = 'id, tacos';//return these fields
$result = $DB->get_records_select_menu($table,$select,null,$fields);
</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_sql_menu()=====
Get the first two columns from a number of records as an associative array using a SQL statement.
<code php>
///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);

</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</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===
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.
<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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
</code>

<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
</code>

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

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

<code php>
$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 matches one list of values.
</code>

===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:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> 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)====

<code php>
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);
}
</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 and have 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_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE.
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.
</code>

==See also==

* [[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 functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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

Data manipulation API

2011-09-28T12:09:19Z

Fp2011: /* moodle_database::get_records_sql() */

{{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 2.0|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|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|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:
<code php>global $DB;</code>
* 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.
<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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting a single record===

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

===Getting an 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!)
<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_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.
</code>

The following methods return data as key/value pairs in an associative array.
<code php>
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.
</code>
====Examples====
=====moodle_database::get_records()=====
Get a number of records as an array of objects where all the given conditions met.
<code php>
///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 AND' , '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.
</code>

=====moodle_database::get_records_select()=====
Get a number of records as an array of objects which match a particular WHERE clause.
<code php>
///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);
</code>

=====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.
<code php>
///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' ));
</code>

=====moodle_database::get_records_list()=====
Get a number of records as an array of objects where one field match one list of values.
<code php>
///Get all records where the values('bar',1,'elephant','moodle') are found in the field 'foo'
$result = $DB->get_records($table, 'foo', array( 'bar', 1, 'elephant', 'moodle'));

///Get all records where the values('bar',1,'elephant','moodle') are found in the field 'foo'
///Only returning the fields 'id', 'test' and 'taco'
$result = $DB->get_records($table, 'foo', array( 'bar', 1, 'elephant', 'moodle')null, 'id,test,taco');
</code>

=====moodle_database::get_records_menu()=====
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.
<code php>
///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
$fields = 'id, tacos'; ///list of fields to return

$result = $DB->get_records_menu($table,$conditions));

</code>
The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_select_menu()=====
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
<code php>
///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
$fields = 'id, tacos';//return these fields
$result = $DB->get_records_select_menu($table,$select,null,$fields);
</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_sql_menu()=====
Get the first two columns from a number of records as an associative array using a SQL statement.
<code php>
///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);

</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</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===
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.
<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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
</code>

<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
</code>

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

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

<code php>
$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 matches one list of values.
</code>

===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:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> 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)====

<code php>
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);
}
</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 and have 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_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE.
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.
</code>

==See also==

* [[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 functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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

Data manipulation API

2011-09-28T12:08:29Z

Fp2011: /* moodle_database::get_records_sql() */

{{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 2.0|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|DML functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|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:
<code php>global $DB;</code>
* 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.
<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 the question mark and named placeholders can be used. Note that named params '''must be unique''' even if the value passed is the same.
<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>

===Getting a single record===

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

===Getting an 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!)
<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_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.
</code>

The following methods return data as key/value pairs in an associative array.
<code php>
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.
</code>
====Examples====
=====moodle_database::get_records()=====
Get a number of records as an array of objects where all the given conditions met.
<code php>
///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 AND' , '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.
</code>

=====moodle_database::get_records_select()=====
Get a number of records as an array of objects which match a particular WHERE clause.
<code php>
///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);
</code>

=====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.
<code php>
///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' );
</code>

=====moodle_database::get_records_list()=====
Get a number of records as an array of objects where one field match one list of values.
<code php>
///Get all records where the values('bar',1,'elephant','moodle') are found in the field 'foo'
$result = $DB->get_records($table, 'foo', array( 'bar', 1, 'elephant', 'moodle'));

///Get all records where the values('bar',1,'elephant','moodle') are found in the field 'foo'
///Only returning the fields 'id', 'test' and 'taco'
$result = $DB->get_records($table, 'foo', array( 'bar', 1, 'elephant', 'moodle')null, 'id,test,taco');
</code>

=====moodle_database::get_records_menu()=====
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.
<code php>
///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
$fields = 'id, tacos'; ///list of fields to return

$result = $DB->get_records_menu($table,$conditions));

</code>
The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_select_menu()=====
Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
<code php>
///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
$fields = 'id, tacos';//return these fields
$result = $DB->get_records_select_menu($table,$select,null,$fields);
</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</code>

=====moodle_database::get_records_sql_menu()=====
Get the first two columns from a number of records as an associative array using a SQL statement.
<code php>
///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);

</code>

The result of this last example will look something like:
<code php>
/// The value of the id field is 909 and the value of the 'tacos' column is 6
array(1) { [909]=6 }
</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===
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.
<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>
====Example(s)====
<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$DB->insert_record('quiz_report', $record, false);
</code>

<code php>
$record = new stdClass();
$record->name = 'overview';
$record->displayorder = '10000';
$lastinsertid = $DB->insert_record('quiz_report', $record);
</code>

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

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

<code php>
$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 matches one list of values.
</code>

===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:
<code php>
$transaction = $DB->start_delegated_transaction();
</code>

and finished by:
<code php>
$transaction->allow_commit();
</code>

Usually a transaction is rolled back when an exception is thrown. <code>$transaction->rollback($ex);</code> 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)====

<code php>
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);
}
</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 and have 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_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = ' \\ ')
/// Returns the proper SQL to do LIKE.
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.
</code>

==See also==

* [[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 functions - pre 2.0|DML functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions|DDL functions]]: Where all the functions used to handle DB objects ([[wikipedia:Data_Definition_Language|DDL]]) are defined.
* [[DB layer 2.0 examples|DB layer 2.0 examples]]: To see some code examples using various DML functions.
* [[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.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in SQL databases

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

Talk:Subplugins

2011-09-24T14:19:08Z

Fp2011: more details please !

"A sub-plugin has the same structure as a regular plugin. It will have a version.php, a lang directory, and can have a db directory with an install.xml and all the other hooks you'd expect to see used in any other type of plugin, such as an activity, block, or admin report."

This part is unclear. None of the actual subplugins of the Moodle distribution have this structure. What hooks precisely can be used in a subplugin ? What file names, what function names, what classes are needed to get it all working ?

Thanks,

[[User:F P|F P]] 22:19, 24 September 2011 (WST)